OraQA

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 follow 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
  2  SELECT '1aAbB23CcD4d5eE6fF7Gg8hHiI90' AS in_str FROM DUAL
  3  UNION ALL
  4  SELECT '2589WghHGXYZzxyw0' AS in_str FROM DUAL
  5  /

Table created.

SQL> SELECT * FROM t_ch
  2  /

IN_STR
----------------------------
1aAbB23CcD4d5eE6fF7Gg8hHiI90
2589WghHGXYZzxyw0

SQL> SELECT IN_STR old_str ,
  2         MAX(REPLACE(SYS_CONNECT_BY_PATH (ch , ','), ',' )) new_str
  3   FROM
  4   (
  5   WITH INPUT AS (
  6      SELECT
  7       IN_STR , SUBSTR(IN_STR, LEVEL , 1) ch,
  8       row_number( ) OVER (PARTITION BY IN_STR
  9       ORDER BY
 10       CASE WHEN ascii(SUBSTR(IN_STR, LEVEL , 1))
 11                 BETWEEN ascii('A') AND ascii('Z')
 12            THEN ascii(SUBSTR(IN_STR, LEVEL , 1)) +
 13            ABS(ascii('a') - ascii('A')) +0.1
 14       WHEN ascii(SUBSTR(IN_STR, LEVEL , 1))
 15                 BETWEEN ascii('0') AND ascii('9')
 16       THEN ascii(SUBSTR(IN_STR, LEVEL , 1)) +
 17            ABS(GREATEST(ascii('z'), ascii('Z')) - ascii('0'))  +0.1
 18       ELSE ascii(SUBSTR(IN_STR, LEVEL , 1))
 19       END  ) rn
 20      FROM t_ch
 21      CONNECT BY PRIOR IN_STR = IN_STR
 22      AND LEVEL <= LENGTH(IN_STR)
 23      AND PRIOR dbms_random.string ('a', 10) IS NOT NULL)
 24   SELECT IN_STR, ch, rn FROM INPUT
 25   )
 26   START WITH rn = 1
 27   CONNECT BY PRIOR IN_STR = IN_STR AND PRIOR rn = rn -1
 28   GROUP BY IN_STR
 29   /

OLD_STR                         NEW_STR
------------------------------  ------------------------------
1aAbB23CcD4d5eE6fF7Gg8hHiI90    aAbBcCdDeEfFgGhHiI0123456789
2589WghHGXYZzxyw0               gGhHwWxXyYzZ02589

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.