OraFAQ Forum: Training & Certification » Splitting a variable length delimited string into multiple strings

Home » Other » Training & Certification » Splitting a variable length delimited string into multiple strings (sqlplus, oracle 9i, unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Splitting a variable length delimited string into multiple strings [message #293966]

Tue, 15 January 2008 15:33

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.

Report message to a moderator

Re: Splitting a variable length delimited string into multiple strings [message #294003 is a reply to message #293966]

Wed, 16 January 2008 00:17

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,

Report message to a moderator

Re: Splitting a variable length delimited string into multiple strings [message #294011 is a reply to message #293966]

Wed, 16 January 2008 00:46

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Splitting a variable length delimited string into multiple strings [message #294014 is a reply to message #294011]

Wed, 16 January 2008 00:51

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

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

regards,

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 28 13:52:35 CST 2024