Friday, September 11, 2009

SEPARATED BY COMMA IN QUERY

create table test_018 (emp_id number, skill varchar2(100));

insert into test_018 values (131, 'oracle,vb,java');
insert into test_018 values (123, 'c,c++,vb,java,db2');
insert into test_018 values (111, 'oracle');

commit

select * from test_018

select emp_id,substr(skill, INSTR(skill, ',', 1, LEVEL ) + 1,
INSTR(skill, ',', 1, LEVEL+1) -
INSTR(skill, ',', 1, LEVEL) -1 ) skill
FROM (
SELECT ','||skill||',' AS skill ,emp_id
FROM test_018
)
CONNECT BY PRIOR emp_id = emp_id
AND INSTR (skill, ',', 1, LEVEL+1) > 0
AND PRIOR dbms_random.string ('p', 10) IS NOT NULL



select emp_id
,substr(skill,instr(skill,',',1,lvl)+1
,instr(skill,',',1,lvl+1) - instr(skill,',',1,lvl)-1) sub_skill
from (select emp_id, ','||skill||',' skill from test_018)
,(select level lvl from dual connect by level <= (select max(length(skill) - length(replace(skill,',','')))-1 from (select ','||skill||',' skill from test_018)))
where lvl <= length(skill) - length(replace(skill,',',''))-1
order by emp_id,lvl

No comments: