select statement to split string as per the content and show it [message #655479] |
Wed, 31 August 2016 14:19 |
|
chinmay89
Messages: 11 Registered: August 2016
|
Junior Member |
|
|
Hi All,
Hope you are doing well.
Please find the requirement.
Create table statement
create table ABC_MASTER (Part_number varchar(40),Filename varchar(80));
Insert table statement
insert into ABC_MASTER values (123, abc*cvbsmdm*cbdjdk(9)*asdre-01);
insert into ABC_MASTER values (124, qwe);
Now the task is the select statement from ABC_MASTER which shows like this, split the string as per (*)
part_number................filename
123............................abc
123............................cvbsmdm
123............................cbdjdk(9)
123............................asdre-01
124............................qwe
Thanks
Chinmay
|
|
|
Re: select statement to split string as per the content and show it [message #655483 is a reply to message #655479] |
Wed, 31 August 2016 15:04 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> select part_number, regexp_substr(filename,'[^*]+',1,column_value) filename
2 from ABC_MASTER,
3 table(cast(multiset(select level from dual
4 connect by level <= regexp_count(filename,'\*')+1)
5 as sys.odciNumberList))
6 order by 1, column_value
7 /
PART_NUMBER FILENAME
---------------------------------------- ---------------
123 abc
123 cvbsmdm
123 cbdjdk(9)
123 asdre-01
124 qwe
|
|
|
|
|
|
|