JSL escape character pattern for comma(,)

[#1]

Hi Can any one tell me how to escape comma (,) from a string. I know it for double quate (") it is \! but not aware for comma(,)

[#2]

Hi @dilipkumar,

No escape character needed for a comma, you can simply use them inside your quoted string:

For reference, I've included the jsl escape sequences at the bottom of this post. But, are you encountering a situation where a comma in a string appears to cause some kind of error? I'm sure we can get to the bottom of what's happening if you can elaborate.

@julian  

[#3]

Actually i have defined a variable x= "abc,k,yz". where abc is a first string where x,yz is the second string.
now if i am passing these variable through my script . during execution my script is taking variable x as abc,k and yz separately.

Regards,
Dilip

[#4]

Hi @dilipkumar,

Without seeing more of your script I can't say why JMP is processing that variable in that manner. Actually, I would expect the entire string to always be returned unless you are doing extra work to take it apart based on the delimiter. Are you using a function such as Word() to peel away pieces of the string X? If you can share more I'm happy to help figure this out. Alternatively, if you wish to pass a single variable around, but want tighter control over what you reference in the variable, perhaps a list. From your description, it sounds like you want one element to be "abc" and another element "x,yz." The following jsl could help with that :

x = {"abc", "k,yz"};

print( x[1] ); //"abc"
print( x[2] ); //k,yz"

@julian 

[#5]

Actually I need to fetch data from data base, based on the variable defined below(MN). If I am passing the MN in my jsl script and getting data of 3C22, Y MERS,.AAA-.75YIN . as a output

//Create Database Connection
dbc = Create Database Connection(
"XXX",

)
MN = "3C22,Y MERS,.AAA-.75YIN";// where 3C22 is one input and 2nd input is Y MERS,.AAA-.75YIN"

dataTable = New Table( "RAW DATA");

ddt = Data Table( 1 );

//Execute SQL statements using this connection

sqlQuery = "execute [dbo].[ABC] @MN = '" || MN|| "'";

Regards,
Dilip

[#6]

Hi @dilipkumar,

I think I understand the problem you're facing now. It looks like the issue might be that the database is parsing the commas in your query string in a way you don't want, not that JMP is. SQL isn't my specialty, but I did a little searching and came across this post that discussed escaping characters, like commas, in SQL statements. The suggestion is to add double quotes to help identify the separate clauses since the comma alone isn't definitive if you're using commas elsewhere. In your example, that could look like the following when defining your MN variable, though I must say I don't have a way to test whether this will work so I offer it only as a possible suggestion:

MN = " \!"3C22\!",\!"Y MERS,.AAA-.75YIN\!" ";
//  which stores the string, including quotes:
//        "3C22" , "Y MERS,.AAA-.75YIN"

@julian 

[#7]

@julian 

Hi, Thanks for info.

Yes you have understood my problem correctly. But i don't need double quote to be there in output

MN = " \!"3C22\!",\!"Y MERS,.AAA-.75YIN\!" ";
//  which stores the string, including quotes:
//        "3C22" , "Y MERS,.AAA-.75YIN" i 

i need only 3C22, Y MERS,.AAA-.75YIN as an output.

please suggest.

Regards,

Dilip

[#8]

Hi @dilipkumar,

That appears to be what your initial jsl does. I'm not sure how an output as you stated, 3C22,  Y MERS,.AAA-.75YIN   , will allow the query to identify which part of the string is each variable. The addition of quotes was a suggestion elsewhere to aid in distinguishing the individual components since the comma alone can not, but again, not being well versed in SQL I do not know if that is the best or even viable method.

@julian 

[#9]

The way to debug this is to create a working command in native SQL Server ( I think that's the database you're using).  After that it's not hard to create the same string in JSL.  I have a suggestion to make the code a little easier to read.  If you use evalinsert and surround the MN variable with caret characters (^) that will accomplish the same thing:

sqlQuery = evalinsert("execute [dbo].[ABC] @MN = '^MN^'");
print(sqlquery);

The result is:

"execute [dbo].[ABC] @MN = '3C22,Y MERS,.AAA-.75YIN'"

I think the problem is that this is incorrect SQL Server syntax.  You are setting the variable MN equal to the entire string.  Can a SQL Server expert chime in here?

[#10]

Here are some uses of passing a variable x="abc,k,yz"; in JSL, none of which cause an error.  Can you be more specific on where you are seeing an issue?  I can see it being a problem if it is being added to another literal string without quoting arond it.

names default to here(1);
x= "abc,k,yz";

rr=function({y},show(y));

rr(x);

dt=new table(new column(x,formula(random uniform())));
dt<<add rows(10);

Distribution( Continuous Distribution( Column( as column(x) ) ) );