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

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)

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

--
Peter