Home » SQL & PL/SQL » SQL & PL/SQL » sql regex_instr (oracle 11g)
sql regex_instr [message #684266] Thu, 29 April 2021 12:40 Go to next message
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?
Re: sql regex_instr [message #684267 is a reply to message #684266] Thu, 29 April 2021 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67885
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 Go to previous messageGo to next message
CindyC
Messages: 6
Registered: April 2021
Junior Member
Yes Michael. C and H.
Re: sql regex_instr [message #684269 is a reply to message #684268] Thu, 29 April 2021 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 67885
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.
Re: sql regex_instr [message #684270 is a reply to message #684269] Thu, 29 April 2021 13:09 Go to previous messageGo to next message
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'
Re: sql regex_instr [message #684271 is a reply to message #684270] Thu, 29 April 2021 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67885
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67885
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 Go to previous messageGo to next message
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.
Re: sql regex_instr [message #684279 is a reply to message #684276] Fri, 30 April 2021 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67885
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
Re: sql regex_instr [message #684280 is a reply to message #684266] Fri, 30 April 2021 06:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3061
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.
Re: sql regex_instr [message #684281 is a reply to message #684280] Fri, 30 April 2021 06:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3061
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.
Re: sql regex_instr [message #684289 is a reply to message #684281] Fri, 30 April 2021 16:38 Go to previous message
CindyC
Messages: 6
Registered: April 2021
Junior Member
Thanks Solomon
Previous Topic: substring compare and update
Next Topic: how to get the rows of different values of single column
Goto Forum:
  


Current Time: Sun Jun 20 18:53:44 CDT 2021