Mysql 한글만 추출 - Mysql hangeulman chuchul

마리아 db를 사용하다가 코드값인데 영문과 숫자가 섞여있고 숫자만 써야할 일이 생긴다.

예를들어 코드값이 AA123, BB1233,CDE2345 이렇게 영문,숫자가 섞여있을때 숫자가 1000 이하인것들만 추출할 경우이다.

select * from dept where regexp_replace(dept_id,'[a-z]','')<1000 and regexp_replace(dept_id,'[a-z]','')!=''

이런식으로 regexp_replace 함수를 쓰면 된다. 부서코드에 영문 [a-z] 이 있으면 '' 공백으로 바꾼다.

바꾸고 나서 값이 없는 경우도 있으니 공백만 있는경우 '' 는 뺀다.

참고로..

정규식을 통해 한글, 영문, 특문, 숫자를 제거 하는 방법입니다.

1. 한글제거

- SELECT REGEXP_REPLACE('ab12cd한글(테스트)','[가-힣]','') AS remove_digits;

2. 숫자제거 

- SELECT REGEXP_REPLACE('ab12cd한글(테스트)','[0-9]','') AS remove_digits;

3. 영문제거

- SELECT REGEXP_REPLACE('ab12cd한글(테스트)','[a-z]','') AS remove_digits;

4. 한글과 특수문자 ( ) 제거

- SELECT REGEXP_REPLACE('ab12cd한글(테스트)','[가-힣()]','') AS remove_digits;

일반적으로 비밀번호에 영어/숫자가 아닌 한글이 들어가는 경우는 프로그램에서 Filtering하지 못한 경우가 대부분이다.

이렇게 들어간 데이터를 검색하고자 할때 regexp를 사용해서 검사하면 모두 검색할수 있다.

SELECT * FROM MEMBER

WHERE USER_ID REGEXP '[가-힣]';

추가로 정규표현식을 공부하면 쉽게 알수 있는 거지만

한글로 시작하는 경우엔 '^[가-힣]'

모두가 한글로 구성된 경우만 검색할경우엔 '^[가-힣]+$'

*개요 
컬럼 중 '관리번호' 가 있는데 숫자 뿐만 아니라 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 하면 됨.

반응형

정규식을 통해 한글, 영문, 특문, 숫자를 제거 하는 방법입니다.

1. 한글제거

- SELECT REGEXP_REPLACE('ab12cd한글(테스트)','[가-힣]','') AS remove_digits;

2. 숫자제거 

- SELECT REGEXP_REPLACE('ab12cd한글(테스트)','[0-9]','') AS remove_digits;

3. 영문제거

- SELECT REGEXP_REPLACE('ab12cd한글(테스트)','[a-z]','') AS remove_digits;

4. 한글과 특수문자 ( ) 제거

- SELECT REGEXP_REPLACE('ab12cd한글(테스트)','[가-힣()]','') AS remove_digits;

반응형

공유하기

게시글 관리

저작자표시

'Database' 카테고리의 다른 글