7 Replies Latest reply: Feb 1, 2013 3:39 PM by skv RSS

    Split one row into multiple columns

    skv
      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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Split one row into multiple columns
            Hoek
            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
              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
                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
                  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
                    deleted.

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