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

Frank Kulash

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

Thanks Frank. Will surely check it out.

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.

460798

Wow . SY you rock !
Thanks a bunch. :-)

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

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.

813659

Nice one Sir !