Substitute characters in strings with new lines and tabs

[#1]

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);

[#2 · Accepted solution]

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", " "
);

[#3]

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.

[#4]

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";