한글 - 알파벳 - 숫자 순으로 정렬

** ASCII 코드의 값 범위

45217 ~ 51454         -- 한글

48 ~ 57             -- 숫자

65 ~ 90             -- 대문자

97 ~ 122        -- 소문자

 

ASCII함수로 정렬하기

WITH TEST AS(
  SELECT '777' AS NAME FROM DUAL UNION ALL
  SELECT '567' AS NAME FROM DUAL UNION ALL
  SELECT '0' AS NAME FROM DUAL UNION ALL
  SELECT '가나다라마바사' AS NAME FROM DUAL UNION ALL
  SELECT 'A' AS NAME FROM DUAL UNION ALL
  SELECT 'B' AS NAME FROM DUAL UNION ALL
  SELECT 'a' AS NAME FROM DUAL UNION ALL
  SELECT 'C' AS NAME FROM DUAL UNION ALL
  SELECT '  C' AS NAME FROM DUAL UNION ALL
  SELECT 'd' AS NAME FROM DUAL UNION ALL
  SELECT '나' AS NAME FROM DUAL

)

SELECT NAME,
CASE WHEN ASCII(UPPER(NAME)) > 45217 THEN '1' -- 한글
WHEN ASCII(UPPER(NAME)) BETWEEN '65'  AND '90' THEN '2' -- 알파벳
WHEN ASCII(UPPER(NAME)) BETWEEN '48'  AND '57'  THEN '3' -- 숫자
ELSE '4' -- 기타
END AS SORT_CODE
FROM TEST
ORDER BY SORT_CODE ASC, NAME ASC