Oracle, Regexp, string separated via semicolons

Question

I have this Query:

SELECT ROWNUM AS num_row,
       REGEXP_SUBSTR(REPLACE('param1;param2;param3;;param5;;param7;;;param10;param11;param12;param13;param14;param15;',
                             ';;',
                             '; ;'),
                     '[^;]+',
                     1,
                     level) AS par_value
FROM   dual
CONNECT BY REGEXP_SUBSTR(REPLACE('param1;param2;param3;;param5;;param7;;;param10;param11;param12;param13;param14;param15;',
                                 ';;',
                                 '; ;'),
                         '[^;]+',
                         1,
                         level) IS NOT NULL

with output:

1   param1
2   param2
3   param3
4    
5   param5
6    
7   param7
8    
9   param10
10  param11
11  param12
12  param13
13  param14
14  param15

As you already saw (or not), between parameter 7 and 10 there are supposed to be two NULL parameters, but this query returns only one.

If i put one more semi-colon before 'param10' or change ';;param10' to '; ;param10' -> param10 is in row 10, but this is something i can not do.

It seems that a longer sequence of ';' oracle treat always like there is amount of semicolons -1 .

Does anyone have an idea how to fix it?

fyi, thanks for your answer. Really helped :) I think Everyone will understand the problem — q4za4, Dec 20 '17 at 12:15

score 4 · Answer 1 · answered Dec 20 '17 at 12:18

If I were you, I'd simplify things by using regexp_count in the connect by clause (it seems your parameters always end with a semi-colon, so however many semi-colons there are, you want to loop through the string that many times).

Also, the [^<characters>] method of doesn't handle nulls, so you need to switch to pattern of (.*?)(;|$) - ie. zero or more characters followed by a semi-colon, and then select only the first experession (`.*?).

That means you can do:

WITH sample_data AS (SELECT 'param1;param2;param3;;param5;;param7;;;param10;param11;param12;param13;param14;param15;' str FROM dual)
SELECT LEVEL AS num_row,
       REGEXP_SUBSTR(REPLACE(str,
                             ';;',
                             '; ;'),
                     '[^;]+',
                     1,
                     level) AS par_value,
       REGEXP_SUBSTR(str, '(.*?)(;)', 1, LEVEL, NULL, 1) new_str
FROM   sample_data
CONNECT BY LEVEL <= regexp_count(str, ';');

   NUM_ROW PAR_VAL NEW_STR
---------- ------- -------
         1 param1  param1
         2 param2  param2
         3 param3  param3
         4         
         5 param5  param5
         6         
         7 param7  param7
         8         
         9 param10 
        10 param11 param10
        11 param12 param11
        12 param13 param12
        13 param14 param13
        14 param15 param14
        15         param15

Note, if the trailing semi-colon actually means there's a null parameter following it, then you'd need to switch to a pattern of (.*?)(;|$) (i.e. zero or more characters followed by a semi-colon or the end of the string), plus you'd need to add one to the regexp_count result:

WITH sample_data AS (SELECT 'param1;param2;param3;;param5;;param7;;;param10;param11;param12;param13;param14;param15;' str FROM dual)
SELECT LEVEL AS num_row,
       REGEXP_SUBSTR(REPLACE(str,
                             ';;',
                             '; ;'),
                     '[^;]+',
                     1,
                     level) AS par_value,
       REGEXP_SUBSTR(str, '(.*?)(;|$)', 1, LEVEL, NULL, 1) new_str
FROM   sample_data
CONNECT BY LEVEL <= regexp_count(str, ';') + 1;

   NUM_ROW PAR_VAL NEW_STR
---------- ------- -------
         1 param1  param1
         2 param2  param2
         3 param3  param3
         4         
         5 param5  param5
         6         
         7 param7  param7
         8         
         9 param10 
        10 param11 param10
        11 param12 param11
        12 param13 param12
        13 param14 param13
        14 param15 param14
        15         param15
        16

Oracle, Regexp, string separated via semicolons

1 Answers1