topic Re: How to use the SQL LIKE ANY command? in Discussions

How to use the SQL LIKE ANY command?

rleeper — Tue, 11 Aug 2015 13:53:49 GMT

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.

Re: How to use the SQL LIKE ANY command?

pmroz — Tue, 11 Aug 2015 15:42:36 GMT

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

Re: How to use the SQL LIKE ANY command?

pmroz — Wed, 12 Aug 2015 00:47:44 GMT

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

Re: How to use the SQL LIKE ANY command?

rleeper — Wed, 12 Aug 2015 12:40:18 GMT

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.