How to devide a string in to sub strings [message #409596] |
Tue, 23 June 2009 04:25  |
adusur
Messages: 36 Registered: June 2009 Location: Bhubaneswar
|
Member |
|
|
Hi Everybody,
How can i devide a string in to substirng using the delimeters.
For example i have a string like this: abcde,fghijk,lmn,opqr
Now i need to devide this string in to substrings based on the coma(,) delimeter.
sub strings must be like this: abcde fghijk lmn opqr.
How can i do this in oracle.
Thanks in advance,
Ravindra.
|
|
|
|
|
Re: How to devide a string in to sub strings [message #409605 is a reply to message #409596] |
Tue, 23 June 2009 05:18  |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
There are several threads on this subject already. But hey, I'll demonstrate it once more. Since you didn't lay out the ground rules (you didn't specify what you exactly wanted), I'll do it for you.
The input: a single string with a delimiter, identifying all the string elements
The output: a record for each element.
You're on 10g, so we could really have fun with regular expressions:
WITH yourtable AS
(SELECT 'abcde;fghijk;lmn;opqr' thestring
FROM DUAL
)
SELECT DISTINCT
REGEXP_SUBSTR (thestring, '[^;]+', 1, LEVEL) empno
FROM (SELECT thestring
FROM yourtable)
CONNECT BY REGEXP_SUBSTR (thestring, '[^;]+', 1, LEVEL) IS NOT NULL I believe that is was OraFAQ member ebrian that demonstrated this.
And we could have even more fun, if we added a model clause (thanks to Rob van Wijk for demonstrating this on his blog!)
with yourtable
as
( SELECT 'abcde;fghijk;lmn;opqr' thestring
FROM DUAL
)
select thestring
from yourtable
MODEL return updated rows
dimension by (0 i)
measures(thestring)
rules (thestring[for i from 1 to ( length(thestring[0])
- length(replace(thestring[0],';'))
) + 1 increment 1] =
regexp_substr(thestring[0],'[^;]+',1,cv(i))
) Next time, search the board or you risk to have a 'read the guidelines, follow the rules' post. 
MHE
|
|
|