CODEWITHSUNDEEP
sqloracle-database
vikas gupta
vikas gupta

Reputation: 21

How to rearrange the letter in string in alphabetical order in SQL

How to rearrange the letter in string in alphabetical order in SQL

For example cbaz to abcz

Upvotes: 0

Views: 1107

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Just for fun, you could do this programmatically:

with function sort_letters
         ( p_str varchar2 )
         return varchar2
     as
         type charList is table of simple_integer index by varchar2(1);
         letters charList;
         letter varchar2(1);
         sorted_letters long;
     begin
         if p_str is not null then
             for i in 1..length(p_str) loop
                 letter := substr(p_str,i,1);
                 
                 letters(letter) :=
                     case
                         when letters.exists(letter) then letters(letter) +1
                         else 1
                     end;
             end loop;
        
             letter := letters.first;

             loop
                 sorted_letters := sorted_letters || rpad(letter, letters(letter), letter);
                 letter := letters.next(letter);
                 exit when letter is null;
             end loop;
         end if;
     
         return sorted_letters;
     end;
select sort_letters('abracadabra') 
from   dual
/

SORT_LETTERS('ABRACADABRA')
---------------------------
aaaaabbcdrr

Upvotes: 0

MT0
MT0

Reputation: 168041

You can split the string up into characters and then aggregate:

WITH characters ( rid, value, ch, i, l ) AS (
  SELECT ROWID,
         value,
         SUBSTR(value, 1, 1),
         1,
         LENGTH(value)
  FROM   table_name
UNION ALL
  SELECT rid,
         value,
         SUBSTR(value, i + 1, 1),
         i + 1,
         l
  FROM   characters
  WHERE  i < l
)
SELECT MAX( value ) AS original,
       LISTAGG(ch) WITHIN GROUP ( ORDER BY ch ) AS ordered
FROM   characters
GROUP BY rid

or:

SELECT value As original,
       ordered
FROM   table_name t
       CROSS APPLY (
         SELECT LISTAGG(SUBSTR(t.value, LEVEL, 1))
                  WITHIN GROUP (ORDER BY SUBSTR(t.value, LEVEL, 1)) AS ordered
         FROM   DUAL
         CONNECT BY LEVEL <= LENGTH(t.value)
       )

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'cbaz' FROM DUAL UNION ALL
SELECT 'zyx' FROM DUAL UNION ALL
SELECT 'zyx' FROM DUAL;

Outputs:

ORIGINAL ORDERED
cbaz abcz
zyx xyz
zyx xyz

db<>fiddle here

Upvotes: 1

Related Questions