The Oracle (tm) Users' Co-Operative FAQ

How do I re-order characters in a column based on a new “Order By” rule in a SQL Statement?


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 28th July 2005

Oracle version(s): 9.2

How do I re-order characters in a column based on a new “Order By” rules in a SQL Statement?

Back to index of questions


The following SQL query pattern can be used to re-order characters in a column based on a new “Order By” rule in the SQL Query

Here is the  new “Order By” Rule for characters in a column:

1)      The upper case letter should following the same letter in lower case.

2)      The numbers should be after all the letters.

For example :

aAbBcCdDeEfFgGhH……………...0123456789

 

SQL> Create table t_ch as

    SELECT '1aAbB23CcD4d5eE6fF7Gg8hHiI90' AS IN_STR FROM DUAL

    UNION ALL

    SELECT '2589WghHGXYZzxyw0' AS IN_STR FROM DUAL  ;

 

SQL> select * from t_ch;

IN_STR                                                                         

-------------------------------------------                                                   

1aAbB23CcD4d5eE6fF7Gg8hHiI90                                                    

2589WghHGXYZzxyw0                 

 

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 t_ch

 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;

 

OLD_STR                        NEW_STR                                         

------------------------------ ------------------------------                  

1aAbB23CcD4d5eE6fF7Gg8hHiI90   aAbBcCdDeEfFgGhHiI0123456789                    

2589WghHGXYZzxyw0              gGhHwWxXyYzZ02589  

 

SQL> spool off

 


 

Back to top

Back to index of questions