Question came across my desk today that I never had really considered... can you do a LIKE condition against an IN list of items ?

e.g. SELECT column1,column2
FROM tablename
WHERE 1 = 1
AND column3 like ('THIS%','THAT%','AND%','THEM%');

So I am thinking... I don't think so... but then I got to thinking...maybe, just maybe.

1) Insert the values into a global temp table without the wildcard first.... could be real table, but I often like temp tables.

2) Write the following SQL. Notice we concatenate the wildcard to the SQL

SELECT x.column1,x.column2
FROM tablename x
WHERE 1 = 1
AND EXISTS (SELECT 'x'
FROM t_global_table gtt
WHERE 1 = 1
AND x.column3 like gtt.columnA||'%')

3) Run the SQL... and it works like a charm... not necessarily the best performer, but it works.

Needless to say I was surprised. Hope you all find some use for it and maybe someone else has some other ways of doing the same thing... drop me a line.