This discussion is archived
5 Replies Latest reply: Jul 27, 2013 8:06 AM by Solomon Yakobson RSS

Split a string and Insert in Table

nkswapnil Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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.

Legend

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