cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
rleeper
Level III

How to use the SQL LIKE ANY command?

Hi all.

I am trying to use the input from a text box to have a SQL query that uses the LIKE command, but with multiple entries.

Getting the list out of the text box is easy, the problem comes when I try a SQL query with LIKE and a list.

id = {"R8D-304%","R8D-404%","R8D-515%"};

Select WHERE Serial LIKE ANY (????)

This is where I have problems.  I've used the Concait Items when searching for a list of exact matches, but I need to find any close matches.

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: How to use the SQL LIKE ANY command?

Couldn't resist - you can do it with CONCAT ITEMS:

id = {"R8D-304%","R8D-404%","R8D-515%"};

sql_statement = "SELECT WHERE serial LIKE '" || concat items(id, "' OR serial LIKE '") || "'";

View solution in original post

3 REPLIES 3
pmroz
Super User

Re: How to use the SQL LIKE ANY command?

You can't combine an IN LIST with LIKE.  You could build a series of OR statement like this:

id = {"R8D-304%","R8D-404%","R8D-515%"};

for (i = 1, i <= nitems(id), i++,

     one_item = id[i];

     if (i == 1,

           sql_statement = evalinsert("SELECT WHERE serial LIKE '^one_item^'");

           ,

           sql_statement = evalinsert("^sql_statement^ OR serial LIKE '^one_item^' ");

     );

);

show(sql_statement);

sql_statement = "SELECT WHERE serial LIKE 'R8D-304%' OR serial LIKE 'R8D-404%'  OR serial LIKE 'R8D-515%' ";

pmroz
Super User

Re: How to use the SQL LIKE ANY command?

Couldn't resist - you can do it with CONCAT ITEMS:

id = {"R8D-304%","R8D-404%","R8D-515%"};

sql_statement = "SELECT WHERE serial LIKE '" || concat items(id, "' OR serial LIKE '") || "'";

rleeper
Level III

Re: How to use the SQL LIKE ANY command?

PMroz, the second answer works perfectly!  I had to adjust it to compensate for the column not always being named serial depending on the table but the query is exactly what I needed.

Thanks.