350702_en-US

liliconcom

Substitute characters in strings with new lines and tabs

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

 

ScriptingRe: Substitute characters in strings with new lines and tabs

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

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