2 Replies Latest reply on Oct 23, 2009 4:18 PM by InoL

    String to rows

    InoL
      I know this has been asked many times, and I got the simple solution for converting a (comma separated) string to rows like this:
      with t as (select 'aa;bb;cc;dd;ee;ff' as txt from dual)
        select regexp_substr (txt, '[^;]+', 1, level)
        from t
        connect by level <= length(regexp_replace(txt,'[^;]*'))+1;
      This works fine for one string. But my situation is that I have a table like this:
      col1    col2
      A       aa;bb;cc
      B       dd
      etc.
      I want to convert this to:
      A  aa
      A  bb
      A  cc
      B  dd
      etc.
      I can do this with a pipelined function or I can do this with a group by or unique in my sql, e.g.:
      with t as (select 'A' as what, 'aa;bb;cc' as txt from dual
                 union 
                 select 'B' as what, 'dd' as txt from dual)
        select unique what,REGEXP_SUBSTR (txt, '[^;]+', 1, level)
        from t
        connect by level <= length(regexp_replace(txt,'[^;]*'))+1;
      
      W REGEXP_S
      - --------
      A bb      
      A aa      
      B dd      
      A cc      
      
      4 rows selected.
      In this example, with only two records A and B, this is no problem. But in the actual table I am using in my WITH clause, this select is performing slowly due to the UNIQUE.
      with t as 
        (select col1, col2
        from mytable
        )
        select unique col1, regexp_substr (col2, '[^,]+', 1, level)
        from t
        connect by level <= length(regexp_replace(col2,'[^,]*'))+1;
      Anybody with a suggestion how to improve this statement?
        • 1. Re: String to rows
          Frank Kulash
          Hi,

          It's very confusing to use "CONNECT BY LEVEL <= x" when there's more than one row.
          Generate the highest level you'll ever need in a sub-query (called a counter table ) that is based on a one-row table.
          Then join to the counter table in your main query.
          For example:
          WITH     cntr     AS
          (
               SELECT     LEVEL     AS n
               FROM     dual
               CONNECT BY  LEVEL <= 1 + ( SELECT  MAX ( LENGTH ( REGEXP_REPLACE ( col2
                                                                                       , '[^;]'
                                           )     )                  )
                                 FROM    t
                               )
          )
          SELECT     t.col1
          ,     REGEXP_SUBSTR ( t.col2
                          , '[^;]+'          -- Use + here, not *
                          , 1
                          , cntr.n
                          ) AS col2_substr
          FROM     t
          JOIN     cntr     ON     cntr.n     <= LENGTH ( REGEXP_REPLACE (col2 || ';', '[^;]'))
          ORDER BY  t.col1
          ,            cntr.n
          ;
          Edited by: Frank Kulash on Oct 23, 2009 12:01 PM
          • 2. Re: String to rows
            InoL
            Thanks a lot Frank (espacially for the edited version, the first version returned an error as you probably noticed too).
            Works very fast now!