Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to separate the comma delimeitted fields

Posted on 2013-12-19
9
Medium Priority
?
353 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

609 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