Home » SQL & PL/SQL » SQL & PL/SQL » sort given string alphabetic
sort given string alphabetic [message #269989] Tue, 25 September 2007 05:43 Go to next message
maurice.janmaat
Messages: 3
Registered: September 2007
Location: Netherlands
Junior Member
Hello,
can someone help me to sort a given string to sort this string in alphabetic order like:

AMERIKA

sort to

AAEIKMR

Thanx!!

Maurice
Re: sort given string alphabetic [message #269992 is a reply to message #269989] Tue, 25 September 2007 05:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
select substr(txt,lvl,1)
from (select txt
            ,level lvl 
      from (select 'AMERIKA' txt 
            from    dual) 
      connect by level <= length(txt))
order by 1;
Re: sort given string alphabetic [message #270039 is a reply to message #269992] Tue, 25 September 2007 08:46 Go to previous messageGo to next message
maurice.janmaat
Messages: 3
Registered: September 2007
Location: Netherlands
Junior Member
Thanx!!

select substr(txt,lvl,1)
from (select txt
,level lvl
from (select 'AMERIKA' txt
from dual)
connect by level <= length(txt))
order by 1;

S
-
A
A
E
I
K
M
R

7 rows selected.
Laughing
Re: sort given string alphabetic [message #270042 is a reply to message #270039] Tue, 25 September 2007 08:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(txt,'|')),'|') TEXT
FROM   (SELECT   SUBSTR (txt, lvl, 1) txt
               , ROW_NUMBER() OVER ( ORDER BY SUBSTR (txt, lvl, 1)) lvl
        FROM     (SELECT     txt
                           , LEVEL lvl
                  FROM       (SELECT 'AMERIKA' txt
                              FROM   DUAL)
                  CONNECT BY LEVEL <= LENGTH (txt))
        ORDER BY 1)
CONNECT BY lvl = PRIOR lvl + 1
START WITH lvl = 1
MHE
Re: sort given string alphabetic [message #270044 is a reply to message #270042] Tue, 25 September 2007 08:58 Go to previous messageGo to next message
maurice.janmaat
Messages: 3
Registered: September 2007
Location: Netherlands
Junior Member
This what i looking for!!

thanx Maaher!!
Re: sort given string alphabetic [message #270051 is a reply to message #270044] Tue, 25 September 2007 09:09 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Well, a nice old function is concat_all:
SQL> SELECT MAX (txt) txt
  2  FROM (  SELECT concat_all( concat_expr( SUBSTR (txt, level, 1), NULL))
  3                            OVER
  4                           ( ORDER BY SUBSTR (txt, level, 1) ) txt
  5          FROM   (SELECT 'AMERIKA' txt
  6                  FROM   DUAL)
  7          CONNECT BY LEVEL <= LENGTH (txt)
  8       )
  9  /

TXT
------------------------------
AAEIKMR
Concat_all can be found on the board. It's a function written by James Padfield.

MHE
Previous Topic: problem with sequences
Next Topic: needed oracle query
Goto Forum:
  


Current Time: Mon Dec 05 19:12:48 CST 2016

Total time taken to generate the page: 0.09262 seconds