Solved

how to separate the comma delimeitted fields

Posted on 2013-12-19
9
345 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

696 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