Regular expressions: splitting with REGEXP_SUBSTR and "null"

Hi,
I have a problem, I found on internet a way to split strings separated by pipe | (for example) with regexp, something like that:

SELECT REGEXP_SUBSTR('One|Two|Three|Four','[^|]+', 1, 3) FROM dual;

which gives the result:
Three

the problem comes when I have one of the words separated by the pipe is actually a Null, so the string becomes 'One|Two||Four|'

the query

SELECT REGEXP_SUBSTR('One|Two||Four','[^|]+', 1, 3) FROM dual;

result in:
Four

I needed a Null (or empty string).
How is this possible?

Thank you in advance! Received on Thu Feb 28 2008 - 10:02:53 CST