Home » SQL & PL/SQL » SQL & PL/SQL » How to devide a string in to sub strings (oracle 10g)
How to devide a string in to sub strings [message #409596] Tue, 23 June 2009 04:25 Go to next message
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 #409603 is a reply to message #409596] Tue, 23 June 2009 04:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for varying in-list, stragg in this forum. You will find loads of example how to do that.

Regards

Raj
Re: How to devide a string in to sub strings [message #409604 is a reply to message #409596] Tue, 23 June 2009 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
INSRT and SUBSTR, or REGEXP_SUBSTR are the normal ways of doing it.
Re: How to devide a string in to sub strings [message #409605 is a reply to message #409596] Tue, 23 June 2009 05:18 Go to previous message
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. Wink

MHE
Previous Topic: Order by for a specific category
Next Topic: it is too urgent how to return the primarykey number as string
Goto Forum:
  


Current Time: Thu Feb 06 23:27:33 CST 2025