select statement to split string as per the content and show it [message #655479] |
Wed, 31 August 2016 14:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f46e4c8e7acca28da6041a92fbba1f16?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
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
|
|
|
|
|
|
|