Home » Other » Training & Certification » Splitting a variable length delimited string into multiple strings (sqlplus, oracle 9i, unix)
Splitting a variable length delimited string into multiple strings [message #293966] Tue, 15 January 2008 15:33 Go to next message
dews
Messages: 7
Registered: November 2007
Junior Member
Hi,

I tried a lot to write a sqlplus statement for the following. Please see my tables and the output i want to generate as follows
1. Table Name - Apparals
Apparal_Name Brand_List
------------ ----------
Trousers PE,VH,SC
Shirts WL,VH

2. Table Name - Brand_Desc
Code Description
---- -----------
PE Peter England
VH VanHusean
SC Scullers
WL Wills

I am trying to get the following output in sqlplus

Trousers Peter England,VanHusean,Scullers
Shirts Wills,VanHusean

I could write a sql which will split the Brand_List and replace them with their descriptions, but only one in a row as follows

Trousers Peter England
Trousers VanHusean
Trousers Scullers
Shirts Wills ...etc

And the sql i wrote is

select Trousers,seq,Brand_Names from
(select Trousers,seq,substr(','||Brand_List||',',instr(','||Brand_List||',',',',1,seq)+1,
(instr(','||Brand_List||',',',',1,seq+1)-instr(','||Brand_List||',',',',1,seq))-1) Brand_List from
(select 1 seq from dual union all select 2 seq from dual union all select 3 seq from dual union all select 4 seq from dual
)a,Apparals b) c,Brand_Desc d
where c.Brand_List is not null
and c.Brand_List=d.Brand_List
order by Trousers,seq

Can any one suggest how can i get the descriptions together for every apparal_name? I know how to do this in pl/sql. But i am trying to get this working with sqlplus.




Re: Splitting a variable length delimited string into multiple strings [message #294003 is a reply to message #293966] Wed, 16 January 2008 00:17 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

did you search this forum.you could also find examples in asktom.

check this link

spliting the values:
select regexp_substr(col,'[^,]+',1,level)
from (
select 'A,B,C,D' col from dual)
connect by level <= ( length(col) - length(replace(col,','))+1
)



regards,
Re: Splitting a variable length delimited string into multiple strings [message #294011 is a reply to message #293966] Wed, 16 January 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

@dhananjay, regexp does not exists in 9i.

dews
Can any one suggest how can i get the descriptions together for every apparal_name?

This is called "pivot" and you'll find answers searching for this word.

Regards
Michel
Re: Splitting a variable length delimited string into multiple strings [message #294014 is a reply to message #294011] Wed, 16 January 2008 00:51 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

oops!!! didn't see the OP's oracle version.thanks Michel.


regards,
Previous Topic: New newbie
Next Topic: How to find out my age in years, months, days upto till date
Goto Forum:
  


Current Time: Thu Mar 28 10:39:20 CDT 2024