Solved

how to separate the comma delimeitted fields

Posted on 2013-12-19
9
348 Views
Last Modified: 2013-12-19
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;
0
Comment
Question by:pardeshirahul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39729674
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
 
LVL 32

Expert Comment

by:awking00
ID: 39729739
Something like -
column1 - MY TEST-TE
column1 - 102 B
column1 - C3DRFA
column1 - FAFDAF2
column1 - FABB
column1 - NULL
column1 - NULL
column1 - FA
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39729759
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 39729784
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
 

Author Comment

by:pardeshirahul
ID: 39729899
i want a single row with number of columns populated
the string can have any number of columns ',' delimitted
0
 
LVL 32

Expert Comment

by:awking00
ID: 39729958
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39729999
>>>  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
 

Author Comment

by:pardeshirahul
ID: 39730015
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39730100
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question