Home » SQL & PL/SQL » SQL & PL/SQL » Splitting the Give string into multiple sub strings (Oracle 11g)
Splitting the Give string into multiple sub strings [message #577526] Mon, 18 February 2013 03:40 Go to next message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member

Hi All ,


I need to split the given string into muliple sub strings based on one special character

Ex : Speace is Special character
with  data as (
select 'ab cd ef gh ' from dual )  
select  *  from  data 

Required Output  : 
ab
cd
ef
gh 



Could any body please help me
Thanks
SaiPRadyumn
Re: Splitting the Give string into multiple sub strings [message #577527 is a reply to message #577526] Mon, 18 February 2013 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ if you did search you'd find this

Regards
Michel
Re: Splitting the Give string into multiple sub strings [message #577569 is a reply to message #577527] Mon, 18 February 2013 08:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2031
Registered: January 2010
Senior Member
And XML solution (11g):

with data as (
              select 'ab cd ef gh' str from dual
             )  
select  rownum,
        x.*
  from  data,
        xmltable(
                 '
                  for $c at $i in ora:tokenize(.," ")
                  where $i != 1
                  return $c
                 '
                 passing ' ' || str
                 columns sub_str varchar2(4000)
                 path '.'
                ) x
/

    ROWNUM SUB_STR
---------- ----------
         1 ab
         2 cd
         3 ef
         4 gh

SQL> 


SY.
Re: Splitting the Give string into multiple sub strings [message #577864 is a reply to message #577569] Thu, 21 February 2013 05:48 Go to previous message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks
SaiPradyumn
Previous Topic: Problem moving files after using UTL_FILE package
Next Topic: Merge with returning Clause
Goto Forum:
  


Current Time: Sat Sep 20 21:34:53 CDT 2014

Total time taken to generate the page: 0.08440 seconds