Parsing strings regular expression for Oracle

Question

select rtrim(regexp_substr (str, '[^|]*(.|$)', 1, level), '|') ASPLIT
from 
(select 'str 1|str 2|str 3' as str from dual)
connect by level <= length (regexp_replace (str, '[^|]+')) + 1

str 1
str 2
str 3

How to alter the parser separator ', ' ?

'str 1, str 2, str 3'

Answer 1

You can just change the delimiter in the pattern:

select rtrim(regexp_substr (str, '[^,]*(.|$)', 1, level), ',') ASPLIT
from 
(select 'str 1, str 2, str 3' as str from dual)
connect by level <= length (regexp_replace (str, '[^,]+')) + 1;

Note that you do not want to change the one in the grouping, (.|$); in that context it's an OR operator not a literal character.

It's simpler to use the same pattern in the substring as you do in the replace (but note Gary_W's warning about this losing empty values with this pattern):

select trim(regexp_substr (str, '[^,]+', 1, level)) ASPLIT
from (select 'str 1, str 2, str 3' as str from dual)
connect by level <= length (regexp_replace (str, '[^,]+')) + 1;

ASPLIT            
-------------------
str 1              
 str 2             
 str 3             

But since you have spaces after the commas, you need to eliminate those; the simplest way is to get rid of leading and trailing spaces with trim. This also shows a variation on the connect by limit but either works (again, note the warning about this pattern):

select trim(regexp_substr (str, '[^,]+', 1, level)) ASPLIT
from (select 'str 1, str 2, str 3' as str from dual)
connect by regexp_substr (str, '[^,]+', 1, level) is not null;

ASPLIT            
-------------------
str 1              
str 2              
str 3              

Answer 2

I must point out that using the regex of the format '[^,]+' to parse a string will give invalid results if there is a NULL element in the list and the position of the element in the list is important. Consider this where the 2nd element is NULL. The results make it seem the 2nd element is 'str 3' where really the 2nd element is NULL.

SQL> select trim(regexp_substr (str, '[^,]+', 1, level)) ASPLIT
     from (select 'str 1,, str 3' as str from dual)
     connect by level <= length (regexp_replace (str, '[^,]+')) + 1;

ASPLIT
-------------
str 1
str 3

Here's another way that handles the NULL list element:

SQL> select trim(regexp_substr (str, '(.*?)(,|$)', 1, level, NULL, 1)) ASPLIT
     from (select 'str 1,, str 3' as str from dual)
     connect by level <= regexp_count(str, ',') + 1;

ASPLIT
-------------
str 1

str 3

SQL>

See this post for more info too: Split comma separated values to columns in Oracle