DB2 like clause with multiple values

Question

How to use like clause with multiple values in DB2 version 12.01.

SELECT t1.* FROM table1 t1 , ( select (pattern_col) as term from help_table ) t2 WHERE t1.col1 like t2.term

Pattern_col contains n number of values like(%abc%, %xyz%, %nnn%, ...)

Thanks in advance for your time and help.

I tried this solution mentioned in How to use LIKE with IN in DB2?. It works when I use sysdummy table (Oracle equivalent is DUAL)

But when i try to replace the sysdummy1 with actual table values, i get below error. SQLCODE = -132, ERROR: AN OPERAND OF LIKE IS NOT VALID

I am not able to figure out why it works with sysdummy1 and why not with actual table.

Answer 1

There is nothing wrong with your approach (I guess), but the platform/version you are on may be a problem. Example for Db2 11.5 on LUW:

create table patterns (pid int not null primary key, pattern varchar(100) not null);
insert into patterns (pid, pattern) values (1, '%abc% %xyz% %nnn%');

create table t (val varchar(100) not null primary key);
insert into t (val) values ('xyzabc xyz nnn'),('xyzabc xyz nn');

select t.*
from t
join patterns p
    on t.val like p.pattern
where p.pid = 1;

VAL                                                                                                 
----------------------------------------------------------------------------------------------------
xyzabc xyz nnn

You can insert multiple patterns like:

delete from patterns;
insert into patterns (pid, pattern) 
values (1, '%abc%'), (2, '%xyz%'), (3, '%nnn%');

select t.*
from t
join patterns p
    on t.val like p.pattern
where p.pid = 1;

VAL                                                                                                 
----------------------------------------------------------------------------------------------------
xyzabc xyz nn                                                                                       
xyzabc xyz nnn

From your comment it appears as if you are using zos. Both LUW and ZOS have regexp abilities that you may want to explore:

REGEXP_LIKE