Home » SQL & PL/SQL » SQL & PL/SQL » Split Sting
Split Sting [message #441036] Thu, 28 January 2010 06:45 Go to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
Hi all,

i want to split a string in oracle
My desired output is
declare
str varchar2(10) := 'Good Evening';
begin 

for i in 1..10 loop

dbms_output.put_line(str(i));
end loop;

it is possible to hv output like 

str(1) = G
str(2) = o
str(3) = o
str(4) = d


thanks in advance
Re: Split Sting [message #441049 is a reply to message #441036] Thu, 28 January 2010 07:15 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Yes, simply use concatenation to add the string that you want onto the 'dynamic' part of the string
Re: Split Sting [message #441050 is a reply to message #441036] Thu, 28 January 2010 07:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use SUBSTR()
SQL> with data as
  2  (select 'Good Morning' the_string
  3   from   dual
  4  )
  5  select substr(the_string, i, 1)
  6  from   data
  7  ,      (select level i
  8          from   data
  9          connect by level <= length(data.the_string)
 10         )
 11  /

SUBS
----
G
o
o
d

M
o
r
n
i
n
g

[Edit: added example]

[Updated on: Thu, 28 January 2010 07:20]

Report message to a moderator

Re: Split Sting [message #441051 is a reply to message #441036] Thu, 28 January 2010 07:17 Go to previous messageGo to next message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
You can use this code
(note: I have changed the varchar2(10) to varchar2(12) to give enough room for the message):
declare
str varchar2(12) :='Good Evening';
l_len number := length(str);
begin
	for i in 1..l_len loop
	dbms_output.put_line(substr(str, i, 1));
	end loop;	
End;
Re: Split Sting [message #441053 is a reply to message #441051] Thu, 28 January 2010 07:23 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Nice one. Now they won't have to do their homework for themselves Wink
Re: Split Sting [message #441063 is a reply to message #441036] Thu, 28 January 2010 08:13 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Try this

with stringed as
(select 'Goodmorning' str from dual)
select level,substr(str,level,1) from stringed
connect by level<length(str);
Re: Split Sting [message #441064 is a reply to message #441063] Thu, 28 January 2010 08:23 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
In what/which way this is different from FRANK`s solutions?
And more over if you use connect by level<length(str);

you get up to 10('g') missing.
SQL> with stringed as
  2  (select 'Goodmorning' str from dual)
  3  select level,substr(str,level,1) from stringed
  4  connect by level<length(str);

     LEVEL S
---------- -
         1 G
         2 o
         3 o
         4 d
         5 m
         6 o
         7 r
         8 n
         9 i
        10 n

10 rows selected.



sriram Smile
Re: Split Sting [message #441066 is a reply to message #441064] Thu, 28 January 2010 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where "g" is gone?

Regards
Michel
Re: Split Sting [message #441087 is a reply to message #441066] Thu, 28 January 2010 10:37 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here:
SQL> l4
  4*      connect by level < length(str)
SQL> c/</<=
  4*      connect by level <= length(str)
SQL> /
Re: Split Sting [message #441106 is a reply to message #441066] Thu, 28 January 2010 20:56 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
./fa/3390/0/

sriram Smile
Re: Split Sting [message #441108 is a reply to message #441066] Thu, 28 January 2010 21:12 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Michel Cadot wrote on Thu, 28 January 2010 08:31
Where "g" is gone?

Regards
Michel


Good Caught Michel Cool



Re: Split Sting [message #441109 is a reply to message #441108] Thu, 28 January 2010 21:16 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
in india we write "Goodmornin"g" not like "Goodmornin".

You should re read MY post. i don`t know about much more about your place/country.
And already little foot answered Michel where exactly the "g" missing.
Please change your spectacles. Wink(as your smiley wearing a black ones(blind))

sriram Wink

[Updated on: Thu, 28 January 2010 21:17]

Report message to a moderator

Re: Split Sting [message #441114 is a reply to message #441109] Thu, 28 January 2010 21:35 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ramoradba wrote on Thu, 28 January 2010 21:16
in india we write "Goodmornin"g" not like "Goodmornin".

You should re read MY post. i don`t know about much more about your place/country.
And already little foot answered Michel where exactly the "g" missing.
Please change your spectacles. Wink(as your smiley wearing a black ones(blind))

sriram Wink


does not matter where you belongs to, if you live in India or on the Dark side of moon Bug is Bug.

ramoradba missing 'G' is Bug in your code since the complete string is 'Goodmorning', in my view 'g' should have been displayed.

@Blind that is the 'COOL' smiley , Since Michel has very keen Eye to catch even missing 'G'.

It is nice to see some one is reading your post so carefully and investing his Valuable time for you post.

ramoradba busted ./fa/1604/0/

[Updated on: Thu, 28 January 2010 21:37]

Report message to a moderator

Re: Split Sting [message #441116 is a reply to message #441114] Thu, 28 January 2010 21:44 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
./fa/449/0/


Quote:
In what/which way this is different from FRANK`s solutions?
And more over if you use connect by level<length(str);

you get up to 10('g') missing.



Thats why Michel posted where?

Littlefoot answered

Quote:
SQL> l4
4* connect by level < length(str)
SQL> c/</<=
4* connect by level <= length(str)
SQL> /



Before posting please read the complete thread...
Let me know If I am wrong...

sriram Smile
Re: Split Sting [message #441122 is a reply to message #441036] Thu, 28 January 2010 22:05 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
you still did not get it do you? Only point I wanted to make is 'g' is missing. Michel spotted it and I just watned to appriciate.

still In my view OP's requirment is to process complete string.

Re: Split Sting [message #441123 is a reply to message #441122] Thu, 28 January 2010 22:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Mr.Rahul

If you look at
http://www.orafaq.com/forum/mv/msg/154481/441063/136607/#msg_441063
the user provided the solution not me and i pointed it is same as FRank`s solution except (<=).
And if the OP really use that solution he may miss the "g".That is My point. NO more argument. i dont wanna argue in this more.

Sriram Smile ./fa/7375/0/
  • Attachment: cupid.gif
    (Size: 11.19KB, Downloaded 535 times)

[Updated on: Thu, 28 January 2010 22:14]

Report message to a moderator

Re: Split Sting [message #441224 is a reply to message #441036] Fri, 29 January 2010 09:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
your original post asked this:

it is possible to hv output like 

What is hv?

Kevin
Re: Split Sting [message #441227 is a reply to message #441036] Fri, 29 January 2010 09:54 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
srry, lst mssg ws nt clr. n yr lst pst y skd

it is possible to hv output like 

m qstn s wht s hv?

Kvn
Re: Split Sting [message #441233 is a reply to message #441227] Fri, 29 January 2010 10:48 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Previous Topic: To select specific columns from tables
Next Topic: Query
Goto Forum:
  


Current Time: Sun Dec 11 02:17:52 CST 2016

Total time taken to generate the page: 0.07067 seconds