| REGEXP_LIKE to match string in a list [message #681404] |
Wed, 15 July 2020 08:15  |
wtolentino
Messages: 373
Registered: March 2005
|
Senior Member |
|
|
i had this snippet of code that i would like to use the regular expression like function to return rows for a matching list of string. however, it returns other rows that should have not. please help thank you.
SQL> select vt.*
2 from (select 'xxxxxxxACxx' str from dual union all
3 select 'xxxDCxxxxxx' str from dual union all
4 select 'DDxxxxxxxxx' str from dual union all
5 select 'xxxxxBCxxxx' str from dual) vt
6 where REGEXP_LIKE (vt.str, '([AC|BC|DC])');
STR
-----------
xxxxxxxACxx
xxxDCxxxxxx
DDxxxxxxxxx
xxxxxBCxxxx
SQL>
expected output
STR
-----------
xxxxxxxACxx
xxxDCxxxxxx
xxxxxBCxxxx
|
|
|
|
| Re: REGEXP_LIKE to match string in a list [message #681405 is a reply to message #681404] |
Wed, 15 July 2020 08:37  |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Square brackets in regular expression indicate class. So '[AC|BC|DC]' means class of characters A,C,| (pipe character),B,C,D,C. As a result your select returns all rows since each row has at least one of class characters. What you nee is 'AC|BC|DC' which is AC or BC or DC:
select vt.*
from (select 'xxxxxxxACxx' str from dual union all
select 'xxxDCxxxxxx' str from dual union all
select 'DDxxxxxxxxx' str from dual union all
select 'xxxxxBCxxxx' str from dual) vt
where REGEXP_LIKE (vt.str, 'AC|BC|DC')
/
STR
-----------
xxxxxxxACxx
xxxDCxxxxxx
xxxxxBCxxxx
SQL>
[Updated on: Wed, 15 July 2020 08:38] Report message to a moderator |
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
