| Query to display string in vertical form [message #169479] |
Thu, 27 April 2006 02:07  |
PavanKumar
Messages: 3
Registered: April 2006
|
Junior Member |
|
|
How to Print the Word in vetical form at Sql Prompt
For example if we had given the string to @substitution variable
as 'Example' String.
We need to extract each character in the Word.
If can be done with substrings but i need to display the word
in vertical form
E
x
a
m
p
l
e
but using query only
[MERGED by LF]
[Updated on: Thu, 28 August 2014 01:43] by Moderator Report message to a moderator |
|
|
|
| Re: Query to display string in vertical form [message #169482 is a reply to message #169479] |
Thu, 27 April 2006 02:15  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Let's abuse dual once again  :
SQL> var thetext varchar2(50)
SQL> EXEC :thetext := 'Example'
PL/SQL procedure successfully completed.
SQL> SELECT SUBSTR(:thetext, LEVEL, 1) x
2 FROM dual
3 CONNECT BY LEVEL <= LENGTH(:thetext)
4 /
X
-
E
x
a
m
p
l
e
7 rows selected.
SQL> EXEC :thetext := 'Another text'
PL/SQL procedure successfully completed.
SQL> SELECT SUBSTR(:thetext, LEVEL, 1) x
2 FROM dual
3 CONNECT BY LEVEL <= LENGTH(:thetext)
4 /
X
-
A
n
o
t
h
e
r
t
e
x
X
-
t
12 rows selected.
SQL>
The key is that you generate as many rows as there are characters in your string. For that I used a row generator technique like there are several. The LEVEL pseudo column increases for each row and can be used in the SUBSTR.
MHE
[Updated on: Thu, 27 April 2006 02:15] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query to display string in vertical form [message #622536 is a reply to message #169479] |
Wed, 27 August 2014 08:52 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
First you need to explain what do you mean by "print word in vertical format". Solutions given so far split string into characters and return one character per row. If you only want to display "vertically" you could:
select rownum,
regexp_replace(dname,'(.)','\1' || chr(10)) dname
from dept
/
ROWNUM DNAME
---------- ----------
1 A
C
C
O
U
N
T
I
N
G
ROWNUM DNAME
---------- ----------
2 R
E
S
E
A
R
C
H
3 S
A
ROWNUM DNAME
---------- ----------
L
E
S
4 O
P
E
R
A
T
I
ROWNUM DNAME
---------- ----------
O
N
S
SQL>
Or, if you are using SQL*Plus:
SQL> column dname format a1
SQL> select rownum,
2 dname
3 from dept
4 /
ROWNUM D
---------- -
1 A
C
C
O
U
N
T
I
N
G
ROWNUM D
---------- -
2 R
E
S
E
A
R
C
H
3 S
A
ROWNUM D
---------- -
L
E
S
4 O
P
E
R
A
T
I
ROWNUM D
---------- -
O
N
S
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to convert vertical to horizontal in pl/sql function [message #622618 is a reply to message #622614] |
Thu, 28 August 2014 02:38 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
SQL> column str format a5;
SQL> WITH DATA AS(
2 SELECT 'H' b FROM DUAL UNION ALL
3 select 'e' from dual union all
4 SELECT 'l' FROM DUAL UNION ALL
5 SELECT 'l' FROM DUAL UNION ALL
6 SELECT 'o' FROM DUAL),
7 T AS(
8 SELECT 1 a, b FROM DATA)
9 SELECT Listagg(b, '')
10 within GROUP (ORDER BY a) STR
11 FROM t
12 GROUP BY a
13 /
STR
-----
Hello
Does it make you happy now?
|
|
|
|
|
|
|
|
|
|
| Re: how to convert vertical to horizontal in pl/sql function [message #622643 is a reply to message #622639] |
Thu, 28 August 2014 04:30 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
aaditya321 wrote on Thu, 28 August 2014 14:48awesome answers
Now it's a bit annoying since you are OK with two different solutions for two different scenarios in a single question. My test case was based on different rows, and LF's test case has just a single row. You did not reply to CM's question, whether they are different rows or not. So what do you want?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
