CODEWITHSUNDEEP
sqlregexoracle-database
user1732386
user1732386

Reputation: 13

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

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.

Upvotes: 1

Views: 6912

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27261

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

Upvotes: 1

Related Questions