Home » SQL & PL/SQL » SQL & PL/SQL » subquery problem
subquery problem [message #223779] Sun, 11 March 2007 06:02 Go to next message
mkr_oracle
Messages: 6
Registered: March 2007
Location: chennai
Junior Member
hi,
how can i print 'hello' word one by one.using sub string.

ex:
    h
    e
    l
    l
    o


regards,
mohan

[Updated on: Mon, 12 March 2007 03:41] by Moderator

Report message to a moderator

Re: subquery problem [message #223802 is a reply to message #223779] Sun, 11 March 2007 13:11 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
SELECT SUBSTR('Hello', LEVEL, 1)
FROM dual
CONNECT BY LEVEL <= LENGTH('Hello')
Re: subquery problem [message #223837 is a reply to message #223802] Mon, 12 March 2007 00:33 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

select substr( 'hello',rownum,1)
from user_objects
where rownum<=length('hello);
Re: subquery problem [message #223881 is a reply to message #223802] Mon, 12 March 2007 03:22 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i want

name
he
ll
o

is this possible
Re: subquery problem [message #223884 is a reply to message #223881] Mon, 12 March 2007 03:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
what did you try so far yourself?
Surely you played around with all the solutions given?
Re: subquery problem [message #223886 is a reply to message #223881] Mon, 12 March 2007 03:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pavuluri wrote on Mon, 12 March 2007 09:22
is this possible
Yes, it is.

Think about it. It is simple math: number of rows is the length of the string/2. But you'll need to round it to the next integer in case of an odd length. Then you'll need to substring for each row with a length of 2 characters: row 1 -> start at position 1, row 2 -> start at position 3, row 3 ...

SQL> col name format a4
SQL> var thetext varchar2(20)
SQL> exec :thetext := 'Hello';

PL/SQL procedure successfully completed.

SQL> select name
  2  from ( select substr(:thetext,1+(level-1)*2,2) name
  3         from   dual
  4         connect by level <= ceil(length(:thetext)/2)
  5       )
  6  /

NAME
----
He
ll
o


MHE
Re: subquery problem [message #223887 is a reply to message #223884] Mon, 12 March 2007 03:37 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
yes,
asume that

"srinivas"

i want

sr
in
iv
as


select substr('srinivas',level,2) from dual
connect by level <=length('srinivas')


its givinig
sr
ri
in
.
.
.
.
.

Thanks,
srinivas

[Updated on: Mon, 12 March 2007 03:42] by Moderator

Report message to a moderator

Re: subquery problem [message #223894 is a reply to message #223887] Mon, 12 March 2007 03:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, with your result, you notice your starting position is incorrect, so you adjust it to level * 2
(substr('srinivas' ,level * 2, 2)

Then you see your first row starts on position 2 instead of position 1 and that you have too many rows.
So, your start position must be 1 + level * 2
The number of iterations must be length/2 rounded up.

This gives you the query Maaher provided, but now you got there yourself and you learned something along the way.
If you try things yourself, you will remember and learn; if you are spoonfed queries, you will not.
Re: subquery problem [message #223895 is a reply to message #223894] Mon, 12 March 2007 03:53 Go to previous message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
thanks
srinivas
Previous Topic: Help me in writing trigger
Next Topic: attendance table
Goto Forum:
  


Current Time: Thu Dec 08 05:52:14 CST 2016

Total time taken to generate the page: 0.14831 seconds