This discussion is archived
7 Replies Latest reply: Feb 1, 2013 1:39 PM by skv RSS

Split one row into multiple columns

skv Newbie
Currently Being Moderated
Hi,

Data in one CLOB column in a table storing with delimiter, ##~~##. Ex. ##~~##abc##~~##defgh##~~##ijklm##~~##nopqr (data starts with delimiter). Please help me to split the data into multiple rows like below and it should be in the same order.

abc
defgh
ijklm
nopqr


I am using Oracle 11g.

Thanks.
  • 1. Re: Split one row into multiple columns
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Split one row into multiple columns
    Hoek Guru
    Currently Being Moderated
    Have a look here:
    http://nuijten.blogspot.nl/2009/07/splitting-comma-delimited-string-regexp.html
    http://nuijten.blogspot.nl/2011/08/splitting-comma-delimited-string-regexp.html
    (obviously you'll need to adjust the delimiter to the one you're dealing with)
  • 3. Re: Split one row into multiple columns
    skv Newbie
    Currently Being Moderated
    Thanks Hoek for your response. Before posting my question in the forum, I tried similar query. It is working with one character as delimiter.

    with test as (select 'ABC,DEF,GHI,JKL,MNO' str from dual )
    select regexp_substr (str, '[^,]+', 1, rownum) split
    from test
    connect by level <= length (regexp_replace (str, '[^,]+')) + 1;

    Above query is giving correct result by fetching 5 rows. I have modified the query like below...

    with test as (select 'ABC,,,DEF,,,GHI,,,JKL,,,MNO' str from dual )
    select regexp_substr (str, '[^,,,]+', 1, rownum) split
    from test
    connect by level <= length (regexp_replace (str, '[^,,,]+')) + 1;

    Above query resulting 13 rows and last 8 rows are nulls. Number of null rows are increasing, if I increase number of characters in delimiter. Could you please tell me how to avoid those null rows.

    Thanks.
  • 4. Re: Split one row into multiple columns
    onedbguru Pro
    Currently Being Moderated
    What have you tried? You have not provided us with any research or testing that show us what you are looking for. We are not here to do your work for you. We can if you were to provide us a PO number. :) So, show us what you have tested and why it didn't work.
  • 5. Re: Split one row into multiple columns
    Hoek Guru
    Currently Being Moderated
    You could add an outer query, restricting the result to 'WHERE SPLIT IS NOT NULL'.
    Or take into account the length (3 characters) of your delimiter:
    SQL> with test as (select 'ABC,,,DEF,,,GHI,,,JKL,,,MNO' str from dual )
      2  select regexp_substr (str, '[^,,,]+', 1, rownum) split
      3  from test
      4  connect by level*3 <= length (regexp_replace (str, '[^,,,]+'))+3;
    
    SPLIT
    --------------------------------------------------------------------------------
    ABC
    DEF
    GHI
    JKL
    MNO
    
    5 rows selected.
  • 6. Re: Split one row into multiple columns
    stefan nebesnak Journeyer
    Currently Being Moderated
    deleted.

    Edited by: stefan nebesnak on Feb 1, 2013 1:01 PM
  • 7. Re: Split one row into multiple columns
    skv Newbie
    Currently Being Moderated
    Thank you so much Hoek.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points