0

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.

Seema
  • 1
  • 1
  • What's your Db2 platform and version? – Mark Barinstein Jan 18 '23 at 10:05
  • @MarkBarinstein version 12.01 – Seema Jan 19 '23 at 07:59
  • What's the platform? Db2 for Z/OS? – Mark Barinstein Jan 19 '23 at 08:52
  • @MarkBarinstein : yes, DB2 for Z/OS – Seema Jan 19 '23 at 12:46
  • [LIKE predicate in zDb2](https://www.ibm.com/docs/en/db2-for-zos/12?topic=predicates-like-predicate) doesn't support column names in the `LIKE pattern-expression` like [LIKE predicate in Db2 for LUW](https://www.ibm.com/docs/en/db2/11.5?topic=predicates-like-predicate) does. Try [REGEXP_LIKE](https://www.ibm.com/docs/en/db2-for-zos/12?topic=functions-regexp-like) like [here](https://dbfiddle.uk/ZYqHcQVt). I don't have zDb2 to test, but it should work... – Mark Barinstein Jan 19 '23 at 18:56

1 Answers1

0

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

Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32