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
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment