topic Re: Stop JMP from trimming whitespace in Discussions

Stop JMP from trimming whitespace

JPKO — Sat, 10 Jun 2023 23:34:59 GMT

If I put a text string with trailing whitespace in a character column of a data table it appears that JMP automatically trims that whitespace away. Is there any way of preventing that from happening?

Re: Stop JMP from trimming whitespace

ian_jmp — Mon, 09 Aug 2021 14:17:51 GMT

I'm not sure an option to force this behaviour exists, but that may be my fault.

But if you are 'putting' values into cells with some other JSL you might be able to use an 'Expression column" to persist values:

NamesDefaultToHere(1);

dt =
New Table( "Trailing White Space",
	Add Rows( 2 ),
	New Column( "String",
		Character,
		"Nominal",
		Set Values( {"", ""} ),
		Set Display Width( 96 )
	),
	New Column( "Expression String",
		Expression,
		"None",
		Set Values( {Empty(), Empty()} )
	)
);

Wait(3);
txt1 = "Text";
txt2 = "Text    ";
Column(dt, 1)[1] = txt1;
Column(dt, 2)[1] = txt1;
Column(dt, 1)[2] = txt2;
Column(dt, 2)[2] = txt2;

for(r=1, r<=NRow(dt), r++, Print(Length(Column(dt, 1)[r]), Length(Column(dt, 2)[r])));

Having stored values this way, the usefulness of this approach will also depend on what happens next.

Re: Stop JMP from trimming whitespace

ih — Tue, 10 Aug 2021 12:11:06 GMT

Something very similar to this behavior as bugged me for a while, I just submitted this wish list item which you might find useful:

Re: Stop JMP from trimming whitespace

JPKO — Tue, 10 Aug 2021 13:26:16 GMT

Thanks Ian,

I realize now that it would probably have been helpful with a bit more context: The character data I am filling into the table are user inputs I retrieve from a database with a SQL query. I am using the retrieved values to dynamically create a new query, but because the whitespaces are trimmed, the value in my table no longer matches the values in the database. I suspect that this behavior is also the reason why I cannot use the filter prompt in the Query Builder platform to filter for rows with trailing whitespace - in that case the query result is simply empty. Note that I have no problem hard coding a WHERE clause that matches the rows in question, and that I can also use the below to match all rows with trailing whitespace. However, as soon as the data is in a JMP table, the whitespace is gone.

"SELECT * 
FROM schema.table
WHERE RIGHT(column, 1) = ' ';"