Possible Duplicate:
Comma Separated values in Oracle
Folks, I know its an exteremly bad idea, and this table needs to be normalized. But unfortunately I cannot change the schema.
We have a table in Oracle DB , as
id|value | other_columns
----------------------------
1|a,b,c |some values
Can we create a view with something like
id|value
-----------
1|a
1|b
1|c
Thanks in advance for help.
I don't think this is an exact duplicate of the question referenced in the close votes. Similar yes, but not the same.
Not exactly beautiful, but:
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. The CONNECT BY LEVEL < 20 is arbitrary, adjust for 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>
I did something similiar to this in the past. You need to create a function that accepts an input string and a separator and returns a dataset. If separator is ommited, then comma is assumed.
First create a new type that represents a table of strings:
create or replace type varcharTableType as table of varchar2(255);
/
Then create this function:
create or replace function splitString(
allValues in varchar2,
delim in varchar2 default ','
)
return varcharTableType
as
str varchar2(255) := allValues || delim;
pos number;
dataset varcharTableType := varcharTableType();
begin
loop
pos := instr(str, delim);
exit when (nvl(pos, 0) = 0);
dataset.extend;
dataset(dataset.count) := ltrim(rtrim(substr(str, 1, pos - 1)));
str := substr(str, pos + length(delim));
end loop;
return dataset;
end;
/
Finally, call as:
select *
from table(cast(splitString('a,b,c') as varcharTableType));
COLUMN_VALUE
---------------
a
b
c
3 rows selected
To answer your specific case, you simply need to create a view that joins your table with this function table, as:
create or replace view splitView as
select yourTable.id, s.column_value as value
from yourTable,
table(cast(splitString(yourTable.value) as varcharTableType)) s;
select * from splitView;
id value
---- ---------------
1 a
1 b
1 c
3 rows selected
I am not sure if this last query will work, as I don't have an Oracle machine right now, but hopefully should help you.
