Comma Separated values in Oracle

Question

I have a column with comma separated values like 6,7,99.3334.

I need write a PL SQL procedure that will give me these values separately. The Length of the column is 40.

Can anyone help me with this?

Answer 1

Something like this maybe?

with my as (
  select '6,7,99.3334' str
    from dual
)
select 
  regexp_substr(my.str,'[^,]+',1,level) part
from my
connect by level <= length(regexp_replace(my.str,'[^,]+')) + 1
;

Answer 2

For a non regex answer...

SELECT rn
     , field
     , SUBSTR( ','||field||','
             , INSTR( ','||field||',', ',', 1, rn ) + 1
             ,   INSTR( ','||field||',', ',', 1, rn+1 )
               - INSTR( ','||field||',', ',', 1, rn )
               - 1
             ) separated_field
  FROM ( SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 40 ) -- Length of column
     , ( SELECT '6,7,99.3334' field FROM dual ) -- Source column
 WHERE rn <= (   LENGTH( field ) 
               - LENGTH( REPLACE( field, ',', NULL ) ) 
             ) + 1 -- Number of Commas plus one

Answer 3

Here's another approach. This parses your string into a PL/SQL table.

First create a custom type which is an array of numbers:

CREATE OR REPLACE TYPE number_tab AS TABLE OF NUMBER;

Then create a function which converts a comma-separated list of values to an instance your array type:

CREATE OR REPLACE FUNCTION csv_to_number_tab(p_string IN VARCHAR2)
    RETURN number_tab AS
    l_string       LONG DEFAULT p_string || ',';
    l_data         number_tab := number_tab();
    n              NUMBER;
BEGIN
    LOOP
        EXIT WHEN l_string IS NULL;
        n := INSTR(l_string, ',');
        l_data.EXTEND;
        l_data(l_data.COUNT) := TO_NUMBER(LTRIM(RTRIM(SUBSTR(l_string, 1, n - 1))));
        l_string := SUBSTR(l_string, n + 1);
    END LOOP;

    RETURN l_data;
END;

And here's an anonymous block which demonstrates usage:

DECLARE
    nt   number_tab := number_tab();
    i    NUMBER := 0;
BEGIN
    nt  := csv_to_number_tab('1,2.3, 456.7, 89.1234,567890.12345');

    FOR i IN 1 .. nt.COUNT LOOP
        DBMS_OUTPUT.put_line(i || ' : ' || nt(i));
    END LOOP;
END;

Note that there are spaces between some of the values but not others; the function handles it either way.

Answer 4

You haven't said if you want the items in columns or rows. The row solution is quite easy using xml: http://pbarut.blogspot.com/2006/10/binding-list-variable.html

Basically you convert the string into an xml document then you pull the values out.