regex_substr: regular expression to extract the comma separated string literals

Question

The following regular expression separates the comma separated plain values

(SELECT regexp_substr(:pCsv,'[^,]+', 1, level) 
 FROM DUAL 
 CONNECT BY regexp_substr(:pCsv, '[^,]+', 1, level) IS NOT NULL);

Example:

300100033146068, 300100033146071 , 300100033146079

returns

300100033146068
300100033146071
300100033146079

This works fine if we use the regular expression with SQL IN operator

select * 
from mytable 
where t.mycolumn IN (SELECT regexp_substr(:pCsv,'[^,]+', 1, level) 
                     FROM DUAL 
                     CONNECT BY regexp_substr(:pCsv, '[^,]+', 1, level) IS NOT NULL);

But this query is not working if the comma separated value is a single quoted string literal

'one' , ' two' , 'three'

score 2 · Answer 1 · answered Jan 09 '15 at 17:07

As sln mentioned in the comments, your regular expression is matching any number of non-, characters. That means it will match 'one', not one as you expect.

To exclude the single quotes and spaces, you'll need to add them to the excluded characters list.

Your new inner query should look like:

SELECT regexp_substr(:pCsv,'[^,'' ]+', 1, level)
FROM DUAL
CONNECT BY regexp_substr(:pCsv, '[^,'' ]+', 1, level) IS NOT NULL

Note that we have to use '' to represent a single quote because it appears within another string.

regex_substr: regular expression to extract the comma separated string literals

1 Answers1