How to do gliderecord "IN" query when values contain commas?

[Jon Ulrich]

I have a table with a column whos values contain commas:

My,value,1
My,value,2
My,value,3

I need to query this table looking for an array of matching values

var myArray = ['My,value,1','My,value,3'];
var gr = new GlideRecord('my_table');
gr.addQuery('my_column','IN',myArray);
gr.query();
while(gr.next()){
   gs.print(gr.my_column);
}

System is returning nothing. I have a feeling the system is querying my_column IN [My,value,1,My,value,3]

I have tried escaping and encoding the commas, but still didn't work.

Any ideas how to make this work?

[Elijah Aromola]

What are the actual values you're querying for? The "in" query is looking for a comma separate list of values. The below example demonstrates a query for a list of records by their sys_ids. Each sys_id in this example is separated by a comma

var myArray = ['123456','5879798','56213165'];
var gr = new GlideRecord('my_table');
gr.addEncodedQuery('my_columnIN' + myArray);
gr.query();
while(gr.next()){
   gs.print(gr.my_column);
}

[Jon Ulrich]

The actual values I am looking for are LDAP CN strings.

CN=first.last,OU=myou,DC=domain,DC=com

[Jon Ulrich · Accepted solution]

so far my only idea is to loop through the array and add OR conditions:

var myArray = ['My,value,1','My,value,3'];
var gr = new GlideRecord('my_table');
var or = gr.addQuery('my_column',myArray[0]);
for(i=1; i<myArray.length; i++){
   or.addOrCondition('my_column',myArray[i]);
}
gr.query();
while(gr.next()){
   gs.print(gr.my_column);
}

I got it to work this way, but worry about performance for large arrays

[Elijah Aromola]

Did you try this?

var myArray = [] //data here
var gr = new GlideRecord('my_table');
gr.addEncodedQuery('my_columnIN' + myArray);
gr.query();
while(gr.next()){
   gs.print(gr.my_column);
}