topic Re: Substitute characters in strings with new lines and tabs in Discussions

Substitute characters in strings with new lines and tabs

FN — Fri, 09 Jun 2023 22:03:34 GMT

I am writing a OLEDB query that needs the SQL command to be in a string without containing end of lines or tabs, only spaces.

The following works, but I cannot find how to do this without having to create the 'empty' strings.

User may modify the SQL query, using JMP installed in Windows machines.

SQL_query = "SELECT a, b, c
	FROM mytable
	WHERE a = 'alice'
		AND b BETWEEN '86' AND '91'";

// Cleansing strings (inputs need to be in one line)
newline = "
";
tab = "	";
space = " ";

SQL_query = substitute(SQL_query, tab, space);
SQL_query = substitute(SQL_query, newline, space);

Re: Substitute characters in strings with new lines and tabs

Jeff_Perkinson — Mon, 18 Jan 2021 19:56:14 GMT

You can use escape sequences for those special characters.

SQL_query =
"SELECT a, b, c
	FROM mytable
	WHERE a = 'alice'
		AND b BETWEEN '86' AND '91'";

SQL_query = Substitute( SQL_query,
	"\!t", " ",
	"\!r", " "
);

You might also find the Substitute Into() function useful. It will do the substitution in place and avoid the assignment back to the original string.

Substitute Into( SQL_query,
	"\!t", " ",
	"\!r", " "
);

Re: Substitute characters in strings with new lines and tabs

FN — Tue, 19 Jan 2021 18:14:23 GMT

For some reason, none of these substitutions seem to work when inside an add-in I am developing (?)

If I copy and paste the code, the substitution works.

Re: Substitute characters in strings with new lines and tabs

Jeff_Perkinson — Tue, 19 Jan 2021 19:13:55 GMT

That's odd. There's nothing special about an add-in that would affect this. Can you provide any more detail about what exactly isn't working?

The Hex() function may help you see exactly what (invisible) characters are in your input string.

tab = "\!t";

Show( Hex( tab ) );

return = "\!r";

Show( Hex( return ) );

SQL_query =
"\[SELECT a, b, c
	FROM mytable
	WHERE a = 'alice'
		AND b BETWEEN '86' AND '91']\";
		
Show( Hex( SQL_query ) );

Results:

Hex(tab) = "09";
Hex(return) = "0D";
Hex(SQL_query) = "53454C45435420612C20622C20630D0946524F4D206D797461626C650D0957484552452061203D2027616C696365270D0909414E442062204245545745454E202738362720414E442027393127";