CODEWITHSUNDEEP
sqlregexdb2
Marcos
Marcos

Reputation: 450

DB2 SQL regular expression with "or" operator

Why on earth is this regular expression not working!?!?!?

create table SAMPLE (STR char(3));    
insert into SAMPLE values ('aaa');
insert into SAMPLE values ('bbb');
insert into SAMPLE values ('ccc');

select 
    STR
    , xmlquery('fn:matches($STR,"a")') as A
    , xmlquery('fn:matches($STR,"b")') as B
    , xmlquery('fn:matches($STR,"a|b")') as A_OR_B
from SAMPLE;

Results in:

STR;   A;     B;     A_OR_B
'aaa'; true;  false; false
'bbb'; false; true;  false
'ccc'; false; false; false

A_OR_B is always false, even when A or B is true.

PS: I'm using DB2 10

Upvotes: 2

Views: 250

Answers (2)

maraca
maraca

Reputation: 8743

It could be the character encoding. E.g. if the | is not really a |, but something that looks the same or similar, let's say ¦. In that case the regex tries to match a¦b literally instead of a or b.

Upvotes: 1

Marcos
Marcos

Reputation: 450

I have found a dodgy work around by making multiple calls to RegExp and combine them with the or operator.

select 
    xmlquery('fn:matches($COL_NAME,"a")') as A
    , xmlquery('fn:matches($COL_NAME,"b")') as B
    , xmlquery('fn:matches($COL_NAME,"a|b")') as A_OR_B_Error
    , xmlquery('fn:matches($COL_NAME,"a") or fn:matches($COL_NAME,"b")') as A_OR_B
from MYTABLE

Upvotes: 1

Related Questions