MySQL/한글, 영문, 숫자, 특문 정렬방식

 

프로젝트에서 필요한 부분이 있어서, 샘플로 만들어 보았습니다.

 

WITH t_temp AS (
    SELECT  1 AS product_name
    UNION ALL
    SELECT  '&_@'
    UNION ALL
    SELECT  'B%#$'
    UNION ALL
    SELECT  'a%#$'
    UNION ALL
    SELECT  '^SA'
    UNION ALL
    SELECT  '남dsfa$%'
    UNION ALL
    SELECT  '강2f23bvc43'
    UNION ALL
    SELECT  '뷁2f23bvc43'
    UNION ALL
    SELECT  '뷁1f23bvc43'
    UNION ALL
    SELECT  '뷁11f23bvc43'
    UNION ALL
    SELECT  '뷁3f23bvc43'
)
SELECT  xxx.product_name,
            ASCII(SUBSTRING(xxx.product_name, 1)) AS ascii_product_name,
            (
                CASE
                    WHEN ASCII(SUBSTRING(xxx.product_name, 1)) BETWEEN 48 AND 57 THEN '숫자'
                    WHEN ASCII(SUBSTRING(xxx.product_name, 1)) > 127 THEN '한글'
                    WHEN (
                        (ASCII(SUBSTRING(xxx.product_name, 1)) BETWEEN 65 AND 90) OR
                        (ASCII(SUBSTRING(xxx.product_name, 1)) BETWEEN 97 AND 122)
                    ) THEN '영문'
                    WHEN ASCII(SUBSTRING(xxx.product_name, 1)) < 128 THEN '특문'
                    ELSE '기타'
                END
            ) AS ascii_sort,
            (
                CASE
                    WHEN SUBSTRING(xxx.product_name, 1) REGEXP '^[가-힣]+' THEN '한글'
                    WHEN SUBSTRING(xxx.product_name, 1) REGEXP '^[0-9]+' THEN '숫자'
                    WHEN SUBSTRING(xxx.product_name, 1) REGEXP '^[a-zA-Z]+' THEN '영문'
                    WHEN SUBSTRING(xxx.product_name, 1) REGEXP '^[`~!#$%^&*|\\\'\";:\/?]+' THEN '특문'
                    ELSE '기타'
                END
            ) AS regexp_sort
FROM     t_temp AS xxx
ORDER BY        ascii_sort,
                xxx.product_name
;