Subscribe Bookmark RSS Feed

How to use the SQL LIKE ANY command?

rleeper

Community Trekker

Joined:

Jun 3, 2014

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
Solution

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

3 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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

Solution

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

Community Trekker

Joined:

Jun 3, 2014

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.