Home » SQL & PL/SQL » SQL & PL/SQL » Query to display string in vertical form
Query to display string in vertical form [message #169479] Thu, 27 April 2006 02:07 Go to next message
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 Go to previous messageGo to next message
Maaher
Messages: 7053
Registered: December 2001
Senior Member
Let's abuse dual once again Wink :
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 #169485 is a reply to message #169479] Thu, 27 April 2006 03:04 Go to previous messageGo to next message
PavanKumar
Messages: 3
Registered: April 2006
Junior Member
its not working
its getting stopped at he first character only
Re: Query to display string in vertical form [message #169492 is a reply to message #169485] Thu, 27 April 2006 03:54 Go to previous messageGo to next message
Maaher
Messages: 7053
Registered: December 2001
Senior Member
PavanKumar wrote on Thu, 27 April 2006 10:04

its not working
its getting stopped at he first character only

Yes it is. You are probably experiencing the famous SQL*Plus bug concerning connect by level on dual. SQL*Plus sees 'dual' and immediately stops scanning for a second row.

Try this instead:
SQL>  EXEC :thetext := 'Example'

PL/SQL procedure successfully completed.

SQL> SELECT SUBSTR(txt, l, 1) x
  2  FROM ( SELECT :thetext txt
  3              , LEVEL l
  4         FROM   dual
  5         CONNECT BY LEVEL <= LENGTH(:thetext)
  6       )
  7  /

X
-
E
x
a
m
p
l
e

7 rows selected.


MHE
Re: Query to display string in vertical form [message #169496 is a reply to message #169492] Thu, 27 April 2006 04:20 Go to previous messageGo to next message
PavanKumar
Messages: 3
Registered: April 2006
Junior Member
thanks it works fine

Very good sir and efficient but what's he problem with before query.
Re: Query to display string in vertical form [message #169499 is a reply to message #169496] Thu, 27 April 2006 04:35 Go to previous messageGo to next message
Maaher
Messages: 7053
Registered: December 2001
Senior Member
PavanKumar wrote on Thu, 27 April 2006 11:20

Very good sir and efficient but what's he problem with before query.
It is a SQL Plus bug. Some versions of SQL*Plus just return 1 row when you try to do this directly. By hiding dual in the inline view you work around this bug. The query itself was OK but the tool (SQL*Plus) is not.

MHE
Re: Query to display string in vertical form [message #622533 is a reply to message #169499] Wed, 27 August 2014 08:22 Go to previous messageGo to next message
aaditya321
Messages: 165
Registered: January 2014
Location: Delhi
Senior Member
Please let me know how to write these desire output.
X
-
E
x
a
m
p
l
e

then our output like:
  X
-----------
Example
Re: Query to display string in vertical form [message #622535 is a reply to message #622533] Wed, 27 August 2014 08:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2348
Registered: May 2013
Location: World Wide on the Web
Senior Member
What did you try so far?

And you are not new to this forum, you just posted in a 8 year old zombie thread. Moreover, your question is exactly opposite to the topic title.

[Updated on: Wed, 27 August 2014 08:53]

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2031
Registered: January 2010
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: Query to display string in vertical form [message #622537 is a reply to message #622535] Wed, 27 August 2014 08:52 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you've got individual letters in different rows of a table?
Which rows should be used to make the word?
Which order show the rows be got in?
Re: Query to display string in vertical form [message #622538 is a reply to message #622537] Wed, 27 August 2014 08:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2348
Registered: May 2013
Location: World Wide on the Web
Senior Member
@SY, the new question is just opposite to the topic.
Re: Query to display string in vertical form [message #622539 is a reply to message #622538] Wed, 27 August 2014 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
PDNFTT
how to convert vertical to horizontal in pl/sql function [message #622608 is a reply to message #169479] Thu, 28 August 2014 01:40 Go to previous messageGo to next message
aaditya321
Messages: 165
Registered: January 2014
Location: Delhi
Senior Member
suppose I have a column like:

   X
--------
  Hello

Now I want to convert it like:

X
--
H
e
l
l
o


Please help me how to write function for it.

[Updated on: Thu, 28 August 2014 01:41]

Report message to a moderator

Re: how to convert vertical to horizontal in pl/sql function [message #622609 is a reply to message #622608] Thu, 28 August 2014 01:42 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you've seen quite a few examples, how come you didn't manage to do that by yourself?
Re: how to convert vertical to horizontal in pl/sql function [message #622614 is a reply to message #622609] Thu, 28 August 2014 02:25 Go to previous messageGo to next message
aaditya321
Messages: 165
Registered: January 2014
Location: Delhi
Senior Member
Sorry for my wrong question I want like:
X
--
H
e
l
l
o

convert it like:
   X
--------
  Hello
Re: how to convert vertical to horizontal in pl/sql function [message #622617 is a reply to message #622614] Thu, 28 August 2014 02:32 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Replace new line character with an empty string, then.
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2348
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 #622622 is a reply to message #622618] Thu, 28 August 2014 02:57 Go to previous messageGo to next message
aaditya321
Messages: 165
Registered: January 2014
Location: Delhi
Senior Member
Thank you Lalit, Very happy now.
Re: how to convert vertical to horizontal in pl/sql function [message #622627 is a reply to message #622622] Thu, 28 August 2014 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's what I was talking about:
SQL> CREATE TABLE test (col VARCHAR2 (20));

Table created.

SQL> INSERT INTO test
  2       VALUES ('H
  3  e
  4  l
  5  l
  6  o');

1 row created.

SQL> SELECT * FROM test;

COL
--------------------
H
e
l
l
o


SQL> SELECT REPLACE (col, CHR (10), '') result FROM test;

RESULT
--------------------
Hello

SQL>
Re: how to convert vertical to horizontal in pl/sql function [message #622639 is a reply to message #622627] Thu, 28 August 2014 04:18 Go to previous messageGo to next message
aaditya321
Messages: 165
Registered: January 2014
Location: Delhi
Senior Member
awesome answers LF, thank you so much for supporting us.
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2348
Registered: May 2013
Location: World Wide on the Web
Senior Member
aaditya321 wrote on Thu, 28 August 2014 14:48
awesome 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?
Re: how to convert vertical to horizontal in pl/sql function [message #622650 is a reply to message #622643] Thu, 28 August 2014 04:53 Go to previous messageGo to next message
aaditya321
Messages: 165
Registered: January 2014
Location: Delhi
Senior Member
No annoying Lalit, I am enhancing my knowledge that one question can be solved in two types.
Re: how to convert vertical to horizontal in pl/sql function [message #622657 is a reply to message #622650] Thu, 28 August 2014 05:54 Go to previous message
Lalit Kumar B
Messages: 2348
Registered: May 2013
Location: World Wide on the Web
Senior Member
aaditya321 wrote on Thu, 28 August 2014 15:23
No annoying Lalit, I am enhancing my knowledge that one question can be solved in two types.


That is not one question. Those are two questions with two answers provided to you respectively.
Previous Topic: Find UPPER Chars in a name column
Next Topic: Analytical Query AVG for different days in same sql
Goto Forum:
  


Current Time: Sun Sep 21 16:03:42 CDT 2014

Total time taken to generate the page: 0.08228 seconds