7 Replies Latest reply on Nov 12, 2010 9:39 PM by 813659

    Converting delimited string into table columns

    460798
      The source system will send delimited string to my procedure. I need to convert them into table columns to process.
      For example
      the data for sply_reord_no and order_qty will come as '002R456,100|003Y678,200|093T88,10'

      I need to convert this into
      sply_reord_no          ord_qty      
      -------------------    ------------    
      002R456                 100
      003Y678                 200
      093T88U                 10
      Dear Experts
      Please help.
      Thanks in advance

      Edited by: Saaya on Nov 12, 2010 10:47 AM
        • 1. Re: Converting delimited string into table columns
          Frank Kulash
          Hi,

          See this thread:
          i don't won't to tokenize in plsql
          for how to split a delimited string into parts, using REGEXP_SUBSTR.

          In your case, you'll have to apply this technique twice
          (1) to split the full string delimited by '|' into a varaible number of parts
          (2) to split each of those sub-qtrings into 2 parts, delimited by ',' (Or you might find it just as easy to to this step using INSTR and SUBSTR.)
          • 2. Re: Converting delimited string into table columns
            460798
            Thanks Frank. Will surely check it out.
            • 3. Re: Converting delimited string into table columns
              Solomon Yakobson
              drop table tbl
              /
              create table tbl(
                               sply_reord_no varchar2(20),
                               ord_qty       number
                              )
              /
              declare
                  v_str varchar2(100) := '002R456,100|003Y678,200|093T88,10';
              begin
                  for v_i in 1..length(regexp_replace(v_str || '|','[^|]')) loop
                     insert
                      into tbl
                      values(
                             regexp_substr(v_str,'[^|,]+',1,2 * v_i - 1),
                             regexp_substr(v_str,'[^|,]+',1,2 * v_i)
                            );    end loop;
              end;
              /
              select  *
                from  tbl
              /
              
              SPLY_REORD_NO           ORD_QTY
              -------------------- ----------
              002R456                     100
              003Y678                     200
              093T88                       10
              
              SQL> 
              SY.
              • 4. Re: Converting delimited string into table columns
                460798
                Wow . SY you rock !
                Thanks a bunch. :-)
                • 5. Re: Converting delimited string into table columns
                  pollywog
                  WITH t
                          AS (    SELECT REGEXP_SUBSTR ('002R456,100|003Y678,200|093T88,10',
                                                        '[^|]+',
                                                        1,
                                                        LEVEL)
                                            txt
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                            LENGTH ('002R456,100|003Y678,200|093T88,10')
                                            - LENGTH (
                                                 REPLACE ('002R456,100|003Y678,200|093T88,10',
                                                          '|'))
                                            + 1)
                  SELECT REGEXP_SUBSTR (txt,
                                        '[^\,]+',
                                        1,
                                        1)
                            sply_reord_no,
                         REGEXP_SUBSTR (txt,
                                        '[^\,]+',
                                        1,
                                        2)
                            ord_qty
                    FROM t
                  SPLY_REORD_NO     ORD_QTY
                  002R456     100
                  003Y678     200
                  093T88     10
                  • 6. Re: Converting delimited string into table columns
                    MichaelS
                    In 11g:
                    SQL> with t as (select '002R456,100|003Y678,200|093T88,10' str from dual)
                    --
                    --
                    select regexp_substr (str, '(.*),', 1, 1, null, 1) ply_reord_no, 
                           regexp_substr (str, ',(.*)$', 1, 1, null, 1) ord_qty
                      from (select regexp_substr (str, '(.*?)($|\|)', 1, level, null, 1) str from t
                            connect by level <= regexp_count (str, '\|') + 1)
                    /
                    PLY_REORD_NO                                      ORD_QTY             
                    ------------------------------------------------- --------------------
                    002R456                                           100                 
                    003Y678                                           200                 
                    093T88                                            10                  
                    
                    3 rows selected.