sql In clause with wildcards (DB2)

Question

Is there anyway to use wildcards in a clause similar to a "in", like this

select * from table where columnx xxxxxxx ('%a%','%b%')?

I know I can do:

select * from table where (columnx like '%a%' or columnx like '%b%')

But I'm looking for an alternative to make the querystring shorter.

BTW: I'm not able to register any custom functions, nor temp tables, it should be a native DB2 function.

I found this similar answer for oracle and SQLServer:

I am not familiar with db2, but in MySQL i can achieve this using regular expressions. Not sure if it's possible with your db — Uriil, Jun 08 '14 at 06:06
You could use the `CONTAINS() `text search function much like your linked example, if your version of DB2 supports it. You didn't tell us your DB2 platform nor version, so examples aren't easy to show. And as with your linked example, you'll need to create text search indexes. It's probably easier, faster and clearer to use multiple `LIKE` predicates unless you're working with text documents. — user2338816, Jun 09 '14 at 10:28

score 2 · Answer 1 · answered Jun 08 '14 at 14:10

There's no native regular expression support in "pureSQL" for DB2, you can either create your own as in:

Example:

where xmlcast(xmlquery('fn:matches(\$TEXT,''^[A-Za-z 0-9]*$'')') as integer) = 0

Yet another variant that may be shorter:

select t.* 
from table t
join ( values '%a%', '%b%' ) u (columnx)
   on t.columnx like u.columnx

Agreed, inline virtual/temp tables are awesome for this (and other reasons). — Clockwork-Muse, Jun 08 '14 at 22:33

1 Answers1