마리아 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() 을 사용 가능하므로 쉽게 만들 수 있다.
참고
//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 를 한번 더 감싸야함.
참고
//stackoverflow.com/questions/978147/how-do-you-extract-a-numerical-value-from-a-string-in-a-mysql-query
//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;
반응형
공유하기
게시글 관리
구독하기전디버거의 코딩이야기저작자표시