Separating Text by Delimiter using regexp_subtr

Question

I am using plsql to separate parts of my text. The text: 'a^b^c^d^e'

declare
test varchar2(10);
begin
select 'a^b^c^d^e' into test from dual;
dbms_output.put_line('1. '|| regexp_substr(test, '[^^]+', 1, 1));
dbms_output.put_line('2. '|| regexp_substr(test, '[^^]+', 1, 2));
dbms_output.put_line('3. '|| regexp_substr(test, '[^^]+', 1, 3));
dbms_output.put_line('4. '|| regexp_substr(test, '[^^]+', 1, 4));
dbms_output.put_line('5. '|| regexp_substr(test, '[^^]+', 1, 5));
end;

Output:

1. a
2. b
3. c
4. d
5. e

This works as expected, until a null is found in the middle (i.e. 'a^b^^d^e'). I expect that output to be:

1. a
2. b
3. 
4. d
5. e

but the actual output is:

1. a
2. b
3. d
4. e
5.

I'm not real good at regex but I am most of the way there.
Any help would be appreciated.

Gary_W · Answer 1 · 2022-07-28T15:10:32.073

See my answer here for more detail. Don't use the format '[^^]+' for parsing strings! It returns unexpected results when there is a NULL element in the list and will get you in big trouble as it will return the wrong element. Instead use this form of REGEXP_SUBSTR() as it handles NULL list elements:

REGEXP_SUBSTR('a^b^^d^e', '(.*?)(\^|$)', 1, 4, NULL, 1)

Run it and you'll see you will get 'd' returned as expected.

Separating Text by Delimiter using regexp_subtr

1 Answers1