5 Replies Latest reply: Jul 27, 2013 10:06 AM by Solomon Yakobson RSS

    Split a string and Insert in Table

    nkswapnil

      I have a string '#A,B,C##D,E,F#'

       

      I want to split like this

       

      CREATE TABLE T1

      (COL1 VARCHAR2(10),

      COL2 VARCHAR2(10),

      COL3 VARCHAR2(10)

      );

       

      After split this string i want my following o/p

       

      Desired O/P

      ---------------

      COL1     COL2            COL3

      -=====================

         A                B                  C

        D                 E                   F

       

      Thanks in Advance...

        • 1. Re: Split a string and Insert in Table
          Solomon Yakobson

          There are many ways:

           

          with sample_table as (

                                select '#A,B,C##D,E,F#' str from dual

                               )

          select  substr(column_value,1,instr(column_value,',') - 1) col1,

                  substr(column_value,instr(column_value,',') + 1,instr(column_value,',',1,2) - instr(column_value,',') - 1) col2,

                  substr(column_value,instr(column_value,',',1,2) + 1) col3

            from  sample_table,

                  table(

                        cast(

                             multiset(

                                      select  substr(

                                                     str,

                                                     instr(str,'#',1,(level - 1) * 2 + 1) + 1,

                                                     instr(str,'#',1,level * 2) - instr(str,'#',1,(level - 1) * 2 + 1) - 1

                                                    )

                                        from  dual

                                        connect by level <= (length(str) - length(replace(str,'#'))) / 2

                                     )

                             as sys.OdciVarchar2List

                            )

                       )

          /


          COL1 COL2 COL3
          ---- ---- ----
          A    B    C
          D    E    F

          SQL>

           

          Or:

           

          with sample_table as (

                                select '#A,B,C##D,E,F#' str from dual

                               )

          select  substr(s,1,instr(s,',') - 1) col1,

                  substr(s,instr(s,',') + 1,instr(s,',',1,2) - instr(s,',') - 1) col2,

                  substr(s,instr(s,',',1,2) + 1) col3

            from  sample_table,

                  xmltable(

                           '

                            for $c at $i in ora:tokenize($s,"##")

                            where $i != 1

                            return $c

                           '

                           passing '##' || trim('#' from str) as "s"

                           columns

                             s varchar2(20) path '.'

                          )

          /


          COL1 COL2 COL3
          ---- ---- ----
          A    B    C
          D    E    F

          SQL>

           

          SY.

          • 2. Re: Split a string and Insert in Table
            Frank Kulash

            Hi,

             

            Here's another way to use XMLTABLE:

             

            WITH got_rows AS

            (

                SELECT  x1.column_value

                FROM         table_x     t

                CROSS JOIN   XMLTABLE ( REPLACE ( REPLACE (str, '#', '"')

                                                , '""'

                                                , '","'

                                                )

                                      )  x1

            )

            SELECT   REGEXP_SUBSTR (column_value, '[^,]+', 1, 1) AS col1

            ,        REGEXP_SUBSTR (column_value, '[^,]+', 1, 2) AS col2

            ,        REGEXP_SUBSTR (column_value, '[^,]+', 1, 3) AS col3

            FROM     got_rows

            ;

            • 3. Re: Split a string and Insert in Table
              nkswapnil

              Thanks Guys...

              But Without Without using XMLTABLE is it Possible...

               

              Another One is sting i passed is dynamic..So dont know exact length...

               

              in that case how can i do it...

              • 4. Re: Split a string and Insert in Table
                Solomon Yakobson

                nkswapnil wrote:

                 

                Thanks Guys...

                But Without Without using XMLTABLE is it Possible...

                 

                Another One is sting i passed is dynamic..So dont know exact length...

                 

                in that case how can i do it...

                 

                I gave you solution with & without XMLTABLE. Neither Frank's nor mine solutions care about string length.

                 

                SY.

                • 5. Re: Split a string and Insert in Table
                  Solomon Yakobson

                  SolomonYakobson wrote:

                   

                   

                  Neither Frank's nor mine solutions care about string length.

                   

                   

                     Actually, Frank's solution can fail if string is long enough since it adds commas to it. Simple fix will take care of that. Just change:

                   

                      CROSS JOIN   XMLTABLE ( REPLACE ( REPLACE (str, '#', '"')

                   

                  to:

                   

                      CROSS JOIN   XMLTABLE ( REPLACE ( REPLACE (TO_CLOB(str), '#', '"')

                   

                  SY.