how to separate the comma delimeitted fields

this is not working for me
i wanted it to be break on the ',' where  there isno data between the delimetter i want null

'MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'; 11:19 AM
SELECT SUBSTR(l_string, 1 ,INSTR(l_string, ',', 1, 1)-1),
SUBSTR(l_string, INSTR(l_string,',', 1,1),INSTR(l_string,',',1,3)-INSTR(l_string, ',', 1, 1)+0),
SUBSTR(l_string, INSTR(l_string,',', 1,2),INSTR(l_string,',',1,2)-INSTR(l_string, ',', 1, 1)+0),
SUBSTR(l_string, INSTR(l_string,',', 1,3),INSTR(l_string,',',1,2)-INSTR(l_string, ',', 1, 1)+0),
NVL(SUBSTR(l_string, INSTR(l_string,',', 1,4),INSTR(l_string,',',1,2)-INSTR(l_string, ',', 1, 1)+0),NULL),
SUBSTR(l_string, INSTR(l_string,',', 1,5),INSTR(l_string,',',1,2)-INSTR(l_string, ',', 1, 1)+0)
into l_name,l_name1,l_name2,l_name3,l_name4,l_name5
FROM dual;
pardeshirahulAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
then just continue that pattern and name each field as you pull them off


SELECT RTRIM(
           REGEXP_SUBSTR(
               l_string,
               '[^,]*,|[^,]+$',
               1,
               1
           ),
           ','
       )
           supplier_code,
       RTRIM(
           REGEXP_SUBSTR(
               l_string,
               '[^,]*,|[^,]+$',
               1,
               2
           ),
           ','
       )
           customer_pull_signal_reference,
       RTRIM(
           REGEXP_SUBSTR(
               l_string,
               '[^,]*,|[^,]+$',
               1,
               3
           ),
           ','
       )
           qty_per_container,
       RTRIM(
           REGEXP_SUBSTR(
               l_string,
               '[^,]*,|[^,]+$',
               1,
               4
           ),
           ','
       )
           customer_production_line,
       RTRIM(
           REGEXP_SUBSTR(
               l_string,
               '[^,]*,|[^,]+$',
               1,
               5
           ),
           ','
       )
           receiving_dock,
       RTRIM(
           REGEXP_SUBSTR(
               l_string,
               '[^,]*,|[^,]+$',
               1,
               6
           ),
           ','
       )
           start_date_time,
       RTRIM(
           REGEXP_SUBSTR(
               l_string,
               '[^,]*,|[^,]+$',
               1,
               7
           ),
           ','
       )
           eng_chg_level
  FROM (SELECT 'MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA' l_string FROM DUAL);
0
 
awking00Commented:
What are you expecting for  l_name,l_name1,l_name2,l_name3,l_name4,and l_name5 in your example? You are only selecting 6 values, but with 7 commas, I would have suspected 8 values
0
 
awking00Commented:
Something like -
column1 - MY TEST-TE
column1 - 102 B
column1 - C3DRFA
column1 - FAFDAF2
column1 - FABB
column1 - NULL
column1 - NULL
column1 - FA
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
sdstuberCommented:
SELECT * FROM TABLE(str2tbl('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA', ','));


str2tbl definition...

CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(4000)

CREATE OR REPLACE FUNCTION str2tbl(
    p_string      IN VARCHAR2,
    p_delimiter   IN VARCHAR2:= ',',
    p_enclosed    IN VARCHAR2:= NULL
)
    RETURN vcarray
    PIPELINED
AS
    v_length         NUMBER := LENGTH(p_string);
    v_start          NUMBER := 1;
    v_index          NUMBER;
    v_enclosed_start NUMBER := 1;
    v_enclosed_end   NUMBER;
BEGIN
    v_enclosed_start := INSTR(p_string, p_enclosed, 1);
    v_enclosed_end := INSTR(p_string, p_enclosed, v_enclosed_start + 1);

    WHILE (v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_enclosed_end != 0 AND v_index > v_enclosed_start AND v_index < v_enclosed_end
        THEN
            v_index := INSTR(p_string, p_delimiter, v_enclosed_end);
            v_enclosed_start := INSTR(p_string, p_enclosed, v_enclosed_end + 1);

            IF v_enclosed_start != 0
            THEN
                v_enclosed_end := INSTR(p_string, p_enclosed, v_enclosed_start + 1);
            END IF;
        END IF;

        IF v_index = 0
        THEN
            PIPE ROW (SUBSTR(p_string, v_start));
            v_start := v_length + LENGTH(p_delimiter);
        ELSE
            PIPE ROW (SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + LENGTH(p_delimiter);
        END IF;
    END LOOP;

    RETURN;
END str2tbl;
/

Open in new window

0
 
sdstuberCommented:
or, using only built-in functions...


SELECT RTRIM(
           REGEXP_SUBSTR(
               l_string,
               '[^,]*,|[^,]+$',
               1,
               COLUMN_VALUE
           ),
           ','
       )
           x
  FROM (SELECT 'MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA' l_string FROM DUAL),
       TABLE(
               SELECT COLLECT(LEVEL)
                 FROM DUAL
           CONNECT BY LEVEL <= REGEXP_COUNT(l_string, '[^,]*,|[^,]+$')
       )
0
 
pardeshirahulAuthor Commented:
i want a single row with number of columns populated
the string can have any number of columns ',' delimitted
0
 
awking00Commented:
Still looking for a clarification on why you selected 6 values out of a possible 8. Also, will the string always contain the same number of commas or can it vary?
0
 
sdstuberCommented:
>>>  i want a single row with number of columns populated

not possible.

a sql result set must know how many columns are going to be returned at the time the statement is parsed.

You could create a large fixed number of columns and simply let the extras be null
but you can't have the number of columns change based on the contents of the string
0
 
pardeshirahulAuthor Commented:
i was using
substr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA',instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,8)+1 ,instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,9)-instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,8)-1) supplier_code,
substr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA', instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,9) + 1 ,  instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,10)- instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,9) - 1  )    CUSTOMER_PULL_SIGNAL_REFERENCE,
substr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA', instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,10) + 1 ,  instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,11)- instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,10) - 1  )    qty_per_container,
substr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA', instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,11) + 1 ,  instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,12)- instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,11) - 1  )    customer_production_line,
substr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA', instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,12) + 1 ,  instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,13)- instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,12) - 1  )    receiving_dock,
substr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA', instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,13) + 1 ,  instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,14)- instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,13) - 1  )    start_date_time,
substr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA', instr('MY TEST-TE,102 B,C3DRFA,FAFDAF2,FABB,,,FA'||',',',',1,14) +1 )  eng_chg_level
from dual
but what if the string is the datbase columns with more delimitted fields
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.