Home » SQL & PL/SQL » SQL & PL/SQL » query.....
query..... [message #278302] Fri, 02 November 2007 13:42 Go to next message
sunilcse1
Messages: 23
Registered: June 2007
Location: Bangalore
Junior Member
How could I write a query to arrange a string

my string is "ABCDE"
I need the output like
a
b
c
d
e
Re: query..... [message #278304 is a reply to message #278302] Fri, 02 November 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col v format a1
SQL> select 'ABCDEF' v from dual;
V
-
A
B
C
D
E
F

1 row selected.

Regards
Michel
Re: query..... [message #278309 is a reply to message #278304] Fri, 02 November 2007 14:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
nice one Smile
Re: query..... [message #278315 is a reply to message #278302] Fri, 02 November 2007 20:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
JFF
with t as (select 'abcd' x from dual)
select substr(x, 1,1) from t
union all
select substr(x, 2,1) from t
union all
select substr(x, 3,1) from t
union all
select substr(x, 4,1) from t
Re: query..... [message #278323 is a reply to message #278304] Fri, 02 November 2007 22:50 Go to previous messageGo to next message
sunilcse1
Messages: 23
Registered: June 2007
Location: Bangalore
Junior Member
thanks sir.....
Re: query..... [message #278612 is a reply to message #278323] Mon, 05 November 2007 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, for a more general solution:
select substr('abcde',pos,1)
from (select level pos
      from   dual
      connect by level <= length('abcde'))
Re: query..... [message #278616 is a reply to message #278612] Mon, 05 November 2007 02:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You're just showing off now. It's not big and it's not ... yeah, that doesn't work does it Smile
Re: query..... [message #278617 is a reply to message #278612] Mon, 05 November 2007 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gee! How can't I think about this? Twisted Evil

Regards
Michel
Re: query..... [message #278627 is a reply to message #278616] Mon, 05 November 2007 03:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pablolee wrote on Mon, 05 November 2007 09:59

You're just showing off now.
SELECT SUBSTR(:thetext, lvl , 1) x
FROM   dual
MODEL 
  DIMENSION BY ( 1 AS dm )
  MEASURES ( 1 AS lvl )
  RULES UPSERT ( lvl[ FOR dm FROM 1 TO LENGTH(:thetext) INCREMENT 1 ] = cv(dm) )
/

Very Happy

MHE
Re: query..... [message #278636 is a reply to message #278627] Mon, 05 November 2007 04:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Now THAT is proper showing off!
Re: query..... [message #278647 is a reply to message #278627] Mon, 05 November 2007 04:45 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Take a bow Maarten :applause: Laughing
Re: query..... [message #278683 is a reply to message #278304] Mon, 05 November 2007 06:58 Go to previous messageGo to next message
mitthu
Messages: 3
Registered: November 2007
Junior Member
Hi

Could you please explain what is this format a1 and how does it work?
Do we have any similar formats available too?
Where can i find more info on this?

Thanks
Mitthu
Re: query..... [message #278689 is a reply to message #278683] Mon, 05 November 2007 07:07 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
It's in the SQL*Plus Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm#sthref1160
Re: query..... [message #278690 is a reply to message #278302] Mon, 05 November 2007 07:09 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This Foramt is SQL*PLUS Specific . You cannot expect the same in other Party tools . You can browse the same for more information if intersrested .

JRowbottom's solution will give you the desired output in all the tools .

Thumbs Up
Rajuvan
Previous Topic: Dynamic SQL
Next Topic: NOT EXISTS help
Goto Forum:
  


Current Time: Tue Dec 06 10:13:26 CST 2016

Total time taken to generate the page: 0.16380 seconds