regex - regexp_substr skips over empty positions

Question

With this code to return the nth value in a pipe delimited string...

regexp_substr(int_record.interfaceline, '[^|]+', 1, i)

it works fine when all values are present

Mike|Male|Yes|20000|Yes so the 3rd value is Yes (correct)

but if the string is

Mike|Male||20000|Yes, the 3rd value is 20000 (not what I want)

How can I tell the expression to not skip over the empty values?

TIA

Mike

Answer 1

The regexp_substr works this way:

If occurrence is greater than 1, then the database searches for the second occurrence beginning with the first character following the first occurrence of pattern, and so forth. This behavior is different from the SUBSTR function, which begins its search for the second occurrence at the second character of the first occurrence.

So the pattern [^|] will look for NON pipes, meaning it will skip consecutive pipes ("||") looking for a non-pipe char.

You might try:

select trim(regexp_substr(replace('A|test||string', '|', '| '), '[^|]+', 1, 4)) from dual;

This will replace a "|" with a "| " and allow you to match based on the pattern [^|]