How to do a select statement query with comma separator?

Question

I need to do a simple query, Select Statement

I want to search in Table all record with value "ValueA, ValueB".

If I use this code, not work well:

String255 valueToFilter;
valueToFilter = 'ValueA, ValueB';

select count (RecId) from MyTable
                    where MyTable.Field like valueToFilter ;

But not working, I need to keep all record with value "ValueA" or "ValueB", if in the file there is value like : "ValueA, ValueC" I want to get too.

I don't know the number of values (valueToFilter).

Thanks!

Answer 1

From my point of view the easiest way to accomplish this is to split your filter string:

String255 valueToFilterA = 'ValueA';
String255 valueToFilterB = 'ValueB';
;

select count (RecId) from MyTable
    where MyTable.Field like valueToFilterA 
       || MyTable.Field like valueToFilterB;

If you don't know the number of values you should use query object to add ranges dynamically:

Query                   query = new Query();
QueryRun                queryRun;
QueryBuildDataSource    qbds;
QueryBuildRange         queryRange;
container               conValues;
;

qbds = query.addDataSource(tableNum(MyTable));

for (i = 1; i <= conlen(conValues); i++)
{
    queryRange = qbds.addRange(fieldNum(MyTable, Field));   
    queryRange.value(SysQuery::valueLike(conPeek(conValues, i)));
} 

queryRun = new QueryRun(query);    
info(strFmt("Records count %1", SysQuery::countTotal(queryRun)));