CODEWITHSUNDEEP
regexdb2sql-like
Stefano Balzarotti
Stefano Balzarotti

Reputation: 1904

DB2 10 for z/OS where LIKE compare columns with wildchar '_'

I need to do a select on DB2 like this:

SELECT * 
FROM Table1
WHERE col1 LIKE col2

The problem is that DB2 doesn't support LIKE between columns, because the second operator must be static string.

I need to compare strings like these: 'AABGYD' and 'AA_G_D' that should match. I can't use REGEXP_LIKE because not supported in this version. Can achieve something like with other operators?

Upvotes: 0

Views: 113

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

I don't have zDB2 at hand to test, but it seems that it supports the fn:matches function. Does the following work for you?

with t (col1, col2) as (
          select 'AABGYD', 'AA.G.D' from sysibm.sysdummy1
union all select 'AAGYD', 'AA.G.D' from sysibm.sysdummy1
)
select *
from t
where xmlcast(xmlquery('fn:matches($s, $p)' passing col1 as "s", col2 as "p") as int)=1;

Upvotes: 1

Related Questions