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

From: Peter Nilsson <airia_at_acay.com.au>
Date: Fri, 29 Feb 2008 21:37:03 -0800 (PST)

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?

Here are some (not necessarily pretty) ways, assuming n > 0...

select
x,
n,
substr(

```      '|' || x || '|',
instr('|' || x || '|', '|', 1, n) + 1,
instr('|' || x || '|', '|', 1, n + 1)
- instr('|' || x || '|', '|', 1, n) - 1),
regexp_replace(
'|' || x || '|',
'^([^\|]*\|){' || n || ',' || n || '}([^\|]*)|.*',
'\2' ),
regexp_substr(
regexp_substr(x || '|', '[^\|]*\|', 1, n),
'[^\|]*'),
regexp_substr(
'|' || x,
'[^\|]*',
instr('|' || x, '|', 1, n) + 1)
```

from

(select 'one|two||four|five' x from dual),     (select level n from dual connect by level <= 7)

```--
Peter
```
Received on Fri Feb 29 2008 - 23:37:03 CST

Original text of this message