Split Sting [message #441036] |
Thu, 28 January 2010 06:45  |
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   |
pablolee
Messages: 2882 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   |
Frank
Messages: 7901 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   |
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 #441063 is a reply to message #441036] |
Thu, 28 January 2010 08:13   |
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);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|