Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_LIKE to match string in a list (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
REGEXP_LIKE to match string in a list [message #681404] Wed, 15 July 2020 08:15 Go to next message
wtolentino
Messages: 320
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2987
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>
SY.

[Updated on: Wed, 15 July 2020 08:38]

Report message to a moderator

Re: REGEXP_LIKE to match string in a list [message #681406 is a reply to message #681405] Wed, 15 July 2020 08:45 Go to previous message
wtolentino
Messages: 320
Registered: March 2005
Senior Member
thanks so much
Previous Topic: issue in inserting pdf file in a table
Next Topic: How to Inherit data default from %TYPE attribute for a variable
Goto Forum:
  


Current Time: Wed Sep 30 17:23:26 CDT 2020