Forum Stats

  • 3,757,137 Users
  • 2,251,200 Discussions
  • 7,869,740 Comments

Discussions

Converting delimited string into table columns

460798
460798 Member Posts: 130
edited Nov 12, 2010 4:39PM in SQL & PL/SQL
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
User13042085-Oracle

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,027 Red Diamond
    edited Nov 12, 2010 1:52PM
    Hi,

    See this thread:
    947432
    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.)
  • 460798
    460798 Member Posts: 130
    Thanks Frank. Will surely check it out.
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,719 Black Diamond
    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.
  • 460798
    460798 Member Posts: 130
    Wow . SY you rock !
    Thanks a bunch. :-)
  • pollywog
    pollywog Member Posts: 1,006
    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
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    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.
    User13042085-Oracle
  • 813659
    813659 Member Posts: 4
    Nice one Sir !
This discussion has been closed.