How to split string using a matching string in Oracle using regexp?

Question

Currently I've this, but it is returning null.

select  regexp_substr(  'abcx1bcdx2mno',  '[^x[[:digit:]]]+', 1,1 ) from dual;
select  regexp_substr(  'abcx1bcdx2mno',  '[^x[[:digit:]]]+', 1,1 ) from dual;

I want to have it such a way, that the first query returns abc, and second one returns bcd.

Cerbrus · Accepted Answer · 2015-08-27 06:03:34Z

1

Unfortunately we cannot negate a group of characters(search string). As a workaround we could replace our search string with a single character, space for example, and then negate that one character:

Here is a simple example:

with t1(col) aS(
  select 'abcx1bcdx2mno' from dual
)
select regexp_substr( regexp_replace(col, 'x[[:digit:]]', ' '), '[^ ]+'
                     , 1, level) as res
  from t1
connect by level <= regexp_count( regexp_replace(col, 'x[[:digit:]]', ' ')
                                 , '[^ ]+')

Note: regexp_count() regular expression function introduced in oracle 11g version.

Result:

RES         
-------------
abc           
bcd           
mno

edited Aug 27, 2015 at 6:03

Cerbrus

73.3k19 gold badges138 silver badges151 bronze badges

answered Oct 18, 2013 at 18:32

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nick Krasnov Over a year ago

@user1732386 Previous approach might produce wrong result due to inability negate a group of characters. Answer has been updated.

user1732386 Over a year ago

Thanks. If I've space in the string, then it is not splitting correctly. For example, if I've 'abcx1bcdx2 s1 33 mno', then I would like to see splits as abc, bcd, s1 33 mno. But it is splitting with space too. Also, what if there are newline characters.

Nick Krasnov Over a year ago

@user1732386 If space is part of a string you could introduce a different character that is not part of a string you are trying to split, period or comma for instance. You could use n flag to allow .(period) to match newline or you could do multi-line search using m flag.

Michael-O Over a year ago

This one does not work if you have more than one line in t1.

Michael-O Over a year ago

@NicholasKrasnov, here is a better answer.

Collectives™ on Stack Overflow

How to split string using a matching string in Oracle using regexp?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related