Solved

how to separate the comma delimeitted fields

Posted on 2013-12-19
9
343 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
  • 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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

832 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