Home » SQL & PL/SQL » SQL & PL/SQL » please help ASAP
please help ASAP [message #195049] Tue, 26 September 2006 14:18 Go to next message
wannabbest
Messages: 4
Registered: September 2006
Location: Hyderabad
Junior Member

Could any please help me in generating a string "ABC" using SQL code in the following format..
AA
AB
AC
BA
BB
BC
CA
CB
CC


Thanks
Amar
Re: please help ASAP [message #195052 is a reply to message #195049] Tue, 26 September 2006 14:49 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SELECT 'ABC' FROM DUAL;
Re: please help ASAP [message #195080 is a reply to message #195049] Tue, 26 September 2006 21:51 Go to previous messageGo to next message
wannabbest
Messages: 4
Registered: September 2006
Location: Hyderabad
Junior Member

Hey Thanks so much for replying back But my out put should be generated as follows:-
AA
AB
AC
BA
BB
BC
CA
CB
CC
Re: please help ASAP [message #195083 is a reply to message #195080] Tue, 26 September 2006 23:22 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look here.

Ross Leishman
Re: please help ASAP [message #195310 is a reply to message #195083] Wed, 27 September 2006 20:22 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Similar to some of the other leads provided...
SQL> CREATE TABLE CHAR_TST
  2  ( COL1     VARCHAR2(10));

Table created.

SQL> insert into char_tst values ('AA');

1 row created.

SQL> insert into char_tst values ('AB');

1 row created.

SQL> insert into char_tst values ('AC');

1 row created.

SQL> insert into char_tst values ('BA');

1 row created.

SQL> insert into char_tst values ('BB');

1 row created.

SQL> insert into char_tst values ('BC');

1 row created.

SQL> insert into char_tst values ('CA');

1 row created.

SQL> insert into char_tst values ('CB');

1 row created.

SQL> insert into char_tst values ('CC');

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE OR REPLACE type arrayType as table of char(1);
  2  /

Type created.

SQL> CREATE OR REPLACE function all_chars( p_cursor in sys_refcursor ) return arrayType PIPELINED
  2  as
  3      type vcArray is table of varchar2(4000) index by binary_integer;
  4      l_data vcArray;
  5  begin
  6      loop
  7          fetch p_cursor bulk collect into l_data limit 500;
  8          for i in 1 .. l_data.count
  9          loop
 10              for j in 1 .. length(l_data(i))
 11              loop
 12                  pipe row( substr( l_data(i), j, 1 ) );
 13              end loop;
 14          end loop;
 15          exit when p_cursor%notfound;
 16      end loop;
 17      close p_cursor;
 18          return;
 19  end;
 20  /

Function created.

SQL> 
SQL> select replace(sys_connect_by_path(chr,'~'),'~', null) unique_chars
  2  from (select chr,
  3               row_number() over (partition by 1 order by chr) rn,
  4               count(*) over (partition by 1) cnt
  5         from (select distinct column_value chr
  6               from TABLE( all_chars(cursor(select col1 from char_tst)) )
  7               order by 1))
  8  where rn = cnt
  9  start with rn = 1
 10  connect by prior rn = rn-1;

UNIQUE_CHARS
--------------------------------------------------------------------------------
ABC

SQL> 
Re: please help ASAP [message #195313 is a reply to message #195310] Wed, 27 September 2006 20:44 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Or the "poor-mans" way (without the function) if you only have data is limited to width width 2...

SQL> -- test Euro sign
SQL> insert into char_tst values (unistr('\20AC'));
SQL> select replace(sys_connect_by_path(chr,'~'),'~', null) unique_chars
  2  from (select chr,
  3               row_number() over (partition by 1 order by chr) rn,
  4                       count(*) over (partition by 1) cnt
  5         from (select distinct chr from (
  6                 select substr(col1, 1, 1) chr from char_tst
  7                 union all
  8                 select substr(col1, 2, 1) chr from char_tst)
  9               order by 1))
 10  where rn = cnt
 11  start with rn = 1
 12  connect by prior rn = rn-1;

UNIQUE_CHARS
--------------------------------------------------------------------------------
ABC¤


Increase arrayType from char(1) to char(3) in first example above to handle UTF8 characters (like Euro sign).

[Updated on: Wed, 27 September 2006 20:45]

Report message to a moderator

Previous Topic: problem in oracle 10g release 2
Next Topic: masiv Query Blocks on Statement
Goto Forum:
  


Current Time: Tue Dec 06 06:42:27 CST 2016

Total time taken to generate the page: 0.14616 seconds