MySQL 문자열로부터 숫자 추출, VARCHAR 형인 숫자 정렬

 

*개요 
컬럼 중 '관리번호' 가 있는데 숫자 뿐만 아니라 A01, A02, ... 형태의 번호도 있어 VARCHAR 로 선언됨.
그대로 ORDER 하게 되면 

1,11,111,2,22,3,.... A1,A11,A111,A2,..... 순으로 정렬됨. 

제대로 정렬해보도록 하자.


* 한글-영어-숫자 순으로 정렬
조건 비교시 REGEXP 이용해서 다음과 같이 사용 가능

SELECT   A.MNG_NUM,
CASE WHEN A.MNG_NUM REGEXP '[가-힣].*' THEN 1 ELSE 0 END AS isHangeul,
CASE WHEN A.MNG_NUM REGEXP '[A-Za-z].*' THEN 1 ELSE 0 END AS isAlphabet,
CASE WHEN CAST(A.MNG_NUM AS UNSIGNED)=0 THEN 0 ELSE 1 END AS isNum
FROM MYTABLE A
ORDER BY isHangeul DESC, isAlphabet Desc, CAST(A.MNG_NUM AS UNSIGNED)  DESC
;


* 문자열을 숫자로 바꿔 정렬하기 (CHAR인 숫자 정렬)
CASE() 이용하여 문자열을 숫자로 바꿔서 정렬하면 된다.

SELECT CAST(A.MNG_NUM AS UNSIGNED) CASTMNGNUM FROM MYTABLE A
ORDER BY CASTMNGNUM, A.MNG_NUM DESC

- 이 방법은 약간 문제가 있는데, 숫자는 온전히 숫자로 바뀌지만 문자로 시작하는 경우 0으로 바뀐다는 점.
SELECT CAST('ABC123' AS UNSIGNED) ; -- 결과는 0
SELECT CAST('123ABC' AS UNSIGNED) ; -- 결과는 123



* 문자열로부터 숫자만 추출하기
'A30' 이 있을때 30 을 추출할 수 있을까?

MySQL 8.0 이상이라면 REGEXP_REPLACE() 을 사용 가능하므로 쉽게 만들 수 있다.
참고 
https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql

하지만 안된다면
CAST(A.MNG_NUM AS UNSIGNED) 를 할 수 있는데 문제는 위에 언급했듯 문자로 시작한다면 0이 된다는것.

하지만 문자열을 REVERSE() 로 뒤집어서 추출후 다시 뒤집는 꼼수로 가능하다.


SELECT SUBSTR(A, 1, LENGTH(A)-1), LENGTH(A) FROM (SELECT REVERSE(CAST(REVERSE(concat('abc1234','1')) AS UNSIGNED)) AS A) AS TBLA
;

여기서 CONCAT 이 있는 이유는

A30 
=REVERSE=> 03A 
=CAST=> 3
=REVERSE=> 3
이 되어버리기 때문에 0이 아닌 숫자를 하나 더 붙인후 SUBSTR() 로 잘라내는것


A30
=CONCAT=> A301
=REVERSE=> 103A
=CAST=> 103
=REVERSE=> 301
=SUBSTR => 30

위처럼 SELECT 를 한번 더 감싸야함.

참고
https://stackoverflow.com/questions/978147/how-do-you-extract-a-numerical-value-from-a-string-in-a-mysql-query
https://stackoverflow.com/questions/37268248/how-to-get-only-digits-from-string-in-mysql



* 문자열로부터 문자(한글 영어) 추출하기
REGEXP_REPLACE() 를 사용하거나, 위의 '숫자 추출' 후 REPLACE 하면 됨.