1

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.

ivpavici
  • 1,117
  • 2
  • 19
  • 30
user1732386
  • 13
  • 2
  • 4

1 Answers1

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
Cerbrus
  • 70,800
  • 18
  • 132
  • 147
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
  • @user1732386 Previous approach might produce wrong result due to inability negate a group of characters. Answer has been updated. – Nick Krasnov Oct 18 '13 at 20:44
  • 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. – user1732386 Oct 18 '13 at 21:40
  • @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. – Nick Krasnov Oct 18 '13 at 22:06
  • This one does not work if you have more than one line in `t1`. – Michael-O Jun 03 '14 at 09:56
  • @NicholasKrasnov, [here](http://stackoverflow.com/a/20399166/696632) is a better answer. – Michael-O Jun 03 '14 at 11:00