regex exclude results in oracle select

Question

I have the following strings from a oracle db table

AM-X1-X4-XX5
BI-TG-GF2
G7-FF

I am using regexp_substr to select 3 Columns from the table like this:

AM|X1|X4
BI|TG|GF2
G7|FF|(null)

the current statement looks like this

select 
  regexp_substr(c, '[^-]+',1, 1) as p1,
  regexp_substr(c, '[^-]+',1, 2) as p2,
  regexp_substr(c, '[^-]+',1, 3) as p3,
from table;

so far so good. Now i need to transform the regex or sql statement in a way that it excludes the strings 'AM' and 'BI'. My select statement should return the following table:

X1|X4
TG|GF2
G7|FF

So I tried to adapt the regex statement as below, so it does skip matches equal to 'AM' or 'BI'

(?!AM)(?!BI)(?!-)\w+

However, it does not work. Any help how to create the second table either by adapting the sql or regex-statement is appreciated

Try using a word boundary \b(?!AM)(?!BI)\w+

The fourth bird
– The fourth bird

2020-01-02 12:03:35 +00:00
Commented Jan 2, 2020 at 12:03 — The fourth bird
– The fourth bird, Commented Jan 2, 2020 at 12:03

ekochergin · Accepted Answer · 2020-01-03 06:39:03Z

Not sure how strict the task conditions are, but in the real life I'd do something like this. It returns 2 columns as only 2 are requested

select 
regexp_substr(regexp_replace(c, '^(AM|BI)-',''), '[^-]+',1, 1) as p1,
regexp_substr(regexp_replace(c, '^(AM|BI)-',''), '[^-]+',1, 2) as p2
from table;

However, when the task is to get first three "-" separated cols and then remove AM/BI, I'd use the following way

select
regexp_substr(regexp_replace(substr(c, 1, decode(instr(c, '-', 1, 3), 0, length(c), instr(c, '-', 1, 3)-1)), '(AM-|BI-)'), '[^-]+',1, 1) as p1,
regexp_substr(regexp_replace(substr(c, 1, decode(instr(c, '-', 1, 3), 0, length(c), instr(c, '-', 1, 3)-1)), '(AM-|BI-)'), '[^-]+',1, 2) as p2
from table

And the last (and most painful) case I see is when the task is to remove first three "-" separated columns and filter AM/BI out afterwards with regexp only

with t1 as (select regexp_replace(regexp_substr(c, '[^-]+',1, 1) || '-' || regexp_substr(c, '[^-]+',1, 2) || '-' || regexp_substr(c, '[^-]+',1, 3),'(AM-|BI-)','') c from qq)
select 
regexp_substr(c, '[^-]+',1,1) p1,
regexp_substr(c, '[^-]+',1,2) p2,
from t1;

Gordon Linoff · Accepted Answer · 2020-01-02 12:20:47Z

0

One solution is to remove those patterns and then use your current logic:

select regexp_substr(c, '[^-]+',1, 1) as p1,
       regexp_substr(c, '[^-]+',1, 2) as p2,
       regexp_substr(c, '[^-]+',1, 3) as p3
from (select trim(both '-' from regexp_replace('-' || c || '-', '-AM-|-BI-', '')) as c
      from t
     ) t

Here is a db<>fiddle.

answered Jan 2, 2020 at 12:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Barbaros Özhan · Accepted Answer · 2020-01-02 17:57:53Z

0

Just using classical replace() function might be enough :

with t as
(
 select replace(replace(c,'AM'),'BI') as c 
   from tab
)
select regexp_substr(c, '[^-]+',1, 1) as p1,
       regexp_substr(c, '[^-]+',1, 2) as p2,
       regexp_substr(c, '[^-]+',1, 3) as p3
  from t

Demo

answered Jan 2, 2020 at 17:57

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

Gary_W · Accepted Answer · 2020-01-02 22:08:45Z

You could do it all with REGEXP_REPLACE(). Group the various components of the string, replace with the parts you want. Group one is AM or BI. Group two is group one followed by a hyphen with the whole thing optional. Group three is the next set of characters before the next hyphen, then a hyphen that is not saved, then group 4 is the next set of characters that are not a hyphen, followed by the rest of the string. Replace all that with group 3, a pipe and group 4.

Assumes the strings you don't want, 'AM' or 'BI' are in the first position only.

with tbl(str) as (
  select 'AM-X1-X4-XX5' from dual union all
  select 'BI-TG-GF2' from dual union all
  select 'G7-FF' from dual
)
select regexp_replace(str, '((AM|BI)-)?([^-]+)-([^-]+).*$', '\3|\4') after
from tbl; 

AFTER                                                                           
---------------------
X1|X4                                                                           
TG|GF2                                                                          
G7|FF                                                                           

3 rows selected.

Collectives™ on Stack Overflow

regex exclude results in oracle select

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related