Re: Regular expressions: splitting with REGEXP_SUBSTR and "null"

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 29 Feb 2008 09:31:27 -0800 (PST)
Message-ID: <248f35ac-1e8c-424a-80cd-1da3edc66966@e6g2000prf.googlegroups.com>

On Feb 28, 11:02 am, citte <nicola.ame..._at_gmail.com> wrote:
> 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!

Will this help?

SQL> l
  1 select regexp_substr(replace('one|two||four','||','|NULL|'),   2* '[^|]+',1,3) from dual
SQL> / REGE

---

NULL Also to return an actual NULL value
SQL> select regexp_substr(replace('one|two||four','||',''),   2 '[^|]+',1,3) from dual;

R
-

HTH -- Mark D Powell -- Received on Fri Feb 29 2008 - 11:31:27 CST