Re: Char String printing Order

From: Billy <vslabs_at_onwe.co.za>
Date: 6 Jun 2005 06:05:05 -0700
Message-ID: <1118063105.019288.123470@g47g2000cwa.googlegroups.com>

Jignesh wrote:

> I faced a question in interview that if you have a string. e.g.
> "ORACLE" and want to print it as following using SQL stat.
> O
> R
> A
> C
> L
> E
>
> How can you do it ?

Well, I'm sure there are a couple of ways to do it.. this is one way.

SQL> create or replace type TCharTable as table of char(1);   2 /

Type created.

SQL> create or replace function StringToCharTable( cstring varchar2 ) return TCharTable is
  2 ctable TCharTable;
  3 begin

  4          ctable := NEW TCharTable();
  5          ctable.Extend( LENGTH(cstring) );
  6          for i in 1..LENGTH(cstring)
  7          loop
  8                  ctable(i) := SUBSTR(cstring,i,1);
  9          end loop;
 10
 11          return( ctable );

 12 end;
 13 /

Function created.

SQL>
SQL> select * from THE(select StringToCharTable('ORACLE') from dual);

C
-
O
R
A
C
L
E

6 rows selected.

My response in the interview would have been that I deal and work with requirements. Not what someone think the solution is. In other words, what is the requirement behind pivoting a string into a character per row? The solution to pivot may just not be the best solution to the requirement.

--
Billy

Received on Mon Jun 06 2005 - 08:05:05 CDT