OraFAQ Forum: SQL & PL/SQL » sql regex

Home » SQL & PL/SQL » SQL & PL/SQL » sql regex_instr (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql regex_instr [message #684266]

Thu, 29 April 2021 12:40

CindyC
Messages: 6
Registered: April 2021

Junior Member

Can someone help on this ;
I learnt if we run the below with input 'C|E'

select CASE    WHEN REGEXP_INSTR (',' || 'A,B,C,D,E' || ',', '(C|E)') > 0 THEN 'Y' ELSE 'N' END AS CINDY_FLAG   FROM DUAL;

output is CINDY_FLAG='Y'

But when i run this, see input parameter is 'C|H'

select CASE    WHEN REGEXP_INSTR (',' || 'A,B,C,D,E' || ',', '(C|H)') > 0 THEN 'Y' ELSE 'N' END AS CINDY_FLAG   FROM DUAL;

output is CINDY_FLAG='Y'

But I want N when there is a single mismatch. IS it possible?

Report message to a moderator

Re: sql regex_instr [message #684267 is a reply to message #684266]

Thu, 29 April 2021 12:56

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The output is expected "(C|H)" means "contains a C or a H".
What do you want exactly? "contains C and H"?
Are "C" and "H" items in the list? Or can they be a part of items? For example is item 'CE' valid for the "C" part of the test?

[Updated on: Thu, 29 April 2021 12:58]

Report message to a moderator

Re: sql regex_instr [message #684268 is a reply to message #684267]

Thu, 29 April 2021 12:57

CindyC
Messages: 6
Registered: April 2021

Junior Member

Yes Michael. C and H.

Report message to a moderator

Re: sql regex_instr [message #684269 is a reply to message #684268]

Thu, 29 April 2021 13:00

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Are "C" and "H" items in the list? Or can they be a part of items? For example is item 'CE' valid for the "C" part of the test?
In your first example, is the following correct:

SQL> select CASE    WHEN REGEXP_INSTR (',' || 'A,B,CE' || ',', '(C|E)') > 0 THEN 'Y' ELSE 'N' END AS CINDY_FLAG   FROM DUAL;
C
-
Y

1 row selected.

Report message to a moderator

Re: sql regex_instr [message #684270 is a reply to message #684269]

Thu, 29 April 2021 13:09

CindyC
Messages: 6
Registered: April 2021

Junior Member

Are "C" and "H" items in the list? H is not part of the list ('A,B,C,D,E') so it should return 'N'

Or can they be a part of items? No it cannot be part of the items. So to answer your question the example output is not expected.it should be 'N'

Report message to a moderator

Re: sql regex_instr [message #684271 is a reply to message #684270]

Thu, 29 April 2021 15:08

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

OK, in this case, regexp is not the best way.
Assuming items in the list are unique you can do something like:

SQL> set define off
SQL> with data as (select 'A,B,C,D,E' val from dual)
  2  select val, 'C&E' chk,
  3         decode(sign(instr(','||val||',', ',C,')) + sign(instr(','||val||',', ',E,')),
  4                2, 'Y',
  5                'N') flag
  6  from data
  7  union all
  8  select val, 'C&H' chk,
  9         decode(sign(instr(','||val||',', ',C,')) + sign(instr(','||val||',', ',H,')),
 10                2, 'Y',
 11                'N') flag
 12  from data
 13  /
VAL       CHK F
--------- --- -
A,B,C,D,E C&E Y
A,B,C,D,E C&H N

2 rows selected.

SQL> set define on

[Updated on: Thu, 29 April 2021 15:24]

Report message to a moderator

Re: sql regex_instr [message #684272 is a reply to message #684271]

Thu, 29 April 2021 15:22

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Which can be generalized like:

SQL> set define off
SQL> with
  2    data as (  -- Data to check
  3      select 'A,B,C,D,E' val from dual
  4      union all
  5      select 'E,F,G,H,I' from dual
  6    ),
  7    chk as (  -- item lists to check against
  8      select 'C&E' chk from dual
  9      union all
 10      select 'E&H' from dual
 11    ),
 12    chkbis as (  -- Break the check lists into items
 13      select chk, regexp_substr(chk, '[^&]+', 1, column_value) chkitem,
 14             regexp_count(chk,'&')+1 nbchk
 15      from chk,
 16           table(cast(multiset(select level from dual
 17                               connect by level <= regexp_count(chk,'&')+1)
 18                      as sys.odciNumberList))
 19    )
 20  select val, chk,
 21         decode(sum(sign(instr(','||val||',', ','||chkitem||','))), nbchk, 'Y', 'N') flag
 22  from data, chkbis
 23  group by val, chk, nbchk
 24  order by val, chk
 25  /
VAL       CHK F
--------- --- -
A,B,C,D,E C&E Y
A,B,C,D,E E&H N
E,F,G,H,I C&E N
E,F,G,H,I E&H Y

4 rows selected.

SQL> set define on

[Updated on: Thu, 29 April 2021 15:24]

Report message to a moderator

Re: sql regex_instr [message #684276 is a reply to message #684272]

Thu, 29 April 2021 16:01

CindyC
Messages: 6
Registered: April 2021

Junior Member

Thank you Michael. what is this sys.odciNumberList just for my knowledge.can we use something else.

Also i posted another thread "substring compare and update". Can you help answer that as i posted that so i can try something without plsql. Looks like with this process we dont need a plsql , can you help. I am also trying on my end.

thanks again and again.

Report message to a moderator

Re: sql regex_instr [message #684279 is a reply to message #684276]

Fri, 30 April 2021 00:09

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

what is this sys.odciNumberList just for my knowledge

It is a predefined array type:

SQL> desc sys.odciNumberList
 sys.odciNumberList VARRAY(32767) OF NUMBER

Report message to a moderator

Re: sql regex_instr [message #684280 is a reply to message #684266]

Fri, 30 April 2021 06:04

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

CindyC wrote on Thu, 29 April 2021 13:40

IS it possible?

I assume this is related to your other post. Then use REGEXP_REPLACE and flip arguments:

with sample as (
                select 'A,B,C,D,E' str1,'C,E' str2 from dual union all
                select 'A,B,C,D,E' str1,'C,H' str2 from dual
               )
select  str1,
        str2,
        case
          when regexp_replace(str2,replace(str1,',','|') || '|,') is null then 'Y'
          else 'N'
        end cindy_flag
  from  sample
/

STR1      STR2 C
--------- ---- -
A,B,C,D,E C,E  Y
A,B,C,D,E C,H  N

SQL>

SY.

Report message to a moderator

Re: sql regex_instr [message #684281 is a reply to message #684280]

Fri, 30 April 2021 06:37

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

Also, this will work only if elements in comma-separated list are single character. If elements can be more than one character long:

with sample as (
                select 'AB,BA,CD,DH,EZ' str1,'CD,EZ' str2 from dual union all
                select 'AB,BA,CD,DH,EZ' str1,'CD,H' str2 from dual
               )
select  str1,
        str2,
        case
          when regexp_replace(
                              ',' || replace(str2,',',',,') || ',',
                              ',' || replace(str1,',',',|,') || ','
                             ) is null then 'Y'
          else 'N'
        end cindy_flag
  from  sample
/

STR1           STR2       C
-------------- ---------- -
AB,BA,CD,DH,EZ CD,EZ      Y
AB,BA,CD,DH,EZ CD,H       N

SQL>

SY.

Report message to a moderator

Re: sql regex_instr [message #684289 is a reply to message #684281]

Fri, 30 April 2021 16:38

CindyC
Messages: 6
Registered: April 2021

Junior Member

Thanks Solomon

Report message to a moderator

Previous Topic:	substring compare and update
Next Topic:	how to get the rows of different values of single column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Mar 26 19:54:25 CDT 2023