|
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? |
|
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