Re-order characters in a column
                                                 Last update (2008-03-06 12:22:45)
                                                                                                                   Date added (2008-03-06 12:10:24)

Summary
If you want to re-order characters based on two conditions:

1) The upper case letter should following the same letter in lower case.
2) The numbers should be after all the letters.

Example
Create table strings_table
CREATE TABLE strings_table (in_str VARCHAR(200));

INSERT INTO strings_table 
VALUES ('qwertyuiopasdfghjklmnbvcxzQWERTYUIOPASDFGHJKL23208635453ZXCVBN8348');
COMMIT;
Now use this query to re-order characters
SELECT IN_STR old_str, MAX(REPLACE(SYS_CONNECT_BY_PATH (ch , ','), ',' )) new_str
 FROM (WITH INPUT AS (
 SELECT IN_STR , SUBSTR(IN_STR, LEVEL , 1) ch,
 row_number( ) OVER (PARTITION BY IN_STR
 ORDER BY
 CASE WHEN ASCII(SUBSTR(IN_STR, LEVEL , 1))
           BETWEEN ASCII('A') AND ASCII('Z')
      THEN ASCII(SUBSTR(IN_STR, LEVEL , 1)) +
           ABS(ASCII('a') - ASCII('A')) +0.1
      WHEN ASCII(SUBSTR(IN_STR, LEVEL , 1))
           BETWEEN ASCII('0') AND ASCII('9')
      THEN ASCII(SUBSTR(IN_STR, LEVEL , 1)) +
           ABS(GREATEST(ASCII('z'), ASCII('Z')) - ASCII('0'))  +0.1
      ELSE ASCII(SUBSTR(IN_STR, LEVEL , 1))
 END) rn FROM strings_table
 CONNECT BY PRIOR IN_STR = IN_STR
 AND LEVEL <= LENGTH(IN_STR)
 AND PRIOR Dbms_Random.string ('a', 10) IS NOT NULL)
 SELECT IN_STR, ch, rn FROM INPUT
 ) START WITH rn = 1
 CONNECT BY PRIOR IN_STR = IN_STR AND PRIOR rn = rn -1
 GROUP BY IN_STR;
Output example
OLD_STR                                           NEW_STR
------------------------------------------------------------------------------------
qwertyuiopasdfghjklmnbvcxzQWERTYUIOPASDFGHJKL23208635453ZXCVBN8348      
aAbBcCdDeEfFgGhHiIjJkKlLmnNoOpPqQrRsStTuUvVwWxXyYz0Z22333344556888 
Reviews
Filters
Search