select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level)
from dual
connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;
Asked
Active
Viewed 734 times
0
-
1The SQL is valid so the problem lies with Argos Reports. You need to take this up with Evisions – APC Mar 23 '17 at 11:57
-
Why not show the error? – Gary_W Mar 24 '17 at 17:18
1 Answers
0
You didn't include the error, so we don't know which character is invalid. Here's a different version that does the same thing but handles NULL list elements. Since it does not contain a circumflex let's see if that's the culprit:
select regexp_substr('SMITH,ALLEN,WARD,JONES','(.*?)(,|$)', 1, level, NULL, 1)
from dual
connect by level <= regexp_count('SMITH,ALLEN,WARD,JONES', ',')+1;
Please let us know the results.
P.S. Don't use the regex format '[^,]+' to parse lists as it will return incorrect values if there are NULL list elements. See this post for explanation: https://stackoverflow.com/a/31464699/2543416
