Comma Parsed Values in Oracle
For a non-regex response ...
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
a source to share
Here is a view that will split the CSV column across multiple rows:
CREATE OR REPLACE VIEW your_view AS
SELECT tt.ID, SUBSTR(value, sp, ep-sp) split, other_col1, other_col2...
FROM (SELECT id, value
, INSTR(','||value, ',', 1, L) sp -- 1st posn of substr at this level
, INSTR(value||',', ',', 1, L) ep -- posn of delimiter at this level
FROM tt JOIN (SELECT LEVEL L FROM dual CONNECT BY LEVEL < 20) q -- 20 is max #substrings
ON LENGTH(value)-LENGTH(REPLACE(value,','))+1 >= L
) qq JOIN tt on qq.id = tt.id;
where tt is your table.
Works for csv values longer than 1 or null. CONNECT BY LEVEL <20 arbitrary, adapt to your situation.
To illustrate:
SQL> CREATE TABLE tt (ID INTEGER, c VARCHAR2(20), othercol VARCHAR2(20));
Table created
SQL> INSERT INTO tt VALUES (1, 'a,b,c', 'val1');
1 row inserted
SQL> INSERT INTO tt VALUES (2, 'd,e,f,g', 'val2');
1 row inserted
SQL> INSERT INTO tt VALUES (3, 'a,f', 'val3');
1 row inserted
SQL> INSERT INTO tt VALUES (4,'aa,bbb,cccc', 'val4');
1 row inserted
SQL> CREATE OR REPLACE VIEW myview AS
2 SELECT tt.ID, SUBSTR(c, sp, ep-sp+1) splitval, othercol
3 FROM (SELECT ID
4 , INSTR(','||c,',',1,L) sp, INSTR(c||',',',',1,L)-1 ep
5 FROM tt JOIN (SELECT LEVEL L FROM dual CONNECT BY LEVEL < 20) q
6 ON LENGTH(c)-LENGTH(REPLACE(c,','))+1 >= L
7 ) q JOIN tt ON q.id =tt.id;
View created
SQL> select * from myview order by 1,2;
ID SPLITVAL OTHERCOL
--------------------------------------- -------------------- --------------------
1 a val1
1 b val1
1 c val1
2 d val2
2 e val2
2 f val2
2 g val2
3 a val3
3 f val3
4 aa val4
4 bbb val4
4 cccc val4
12 rows selected
SQL>
a source to share
Here's another approach. This parses your row into a PL / SQL table.
First, create a custom type that is an array of numbers:
CREATE OR REPLACE TYPE number_tab AS TABLE OF NUMBER;
Then, create a function that converts a comma-separated list of values to an instance of 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 that demonstrates the use:
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 values, but not others; the function handles it anyway.
a source to share
You didn't say if you want the items in columns or rows. The line solution is quite simple with xml: http://pbarut.blogspot.com/2006/10/binding-list-variable.html
Basically you convert the string to xml document, then you extract the values.
a source to share