밑줄(_)과 하이픈(-)으로 구분된 긴 문자열 안에서 숫자 부분만 추출해야 하는 경우가 있습니다. MySQL에서는 REPLACE() 함수로 불필요한 문자열을 제거한 뒤, CAST() 함수를 사용해 남은 값을 정수형으로 변환하면 간단하게 해결할 수 있습니다. 아래 예제를 통해 단계별로 살펴보겠습니다.
1. 테이블 생성
먼저 데모용 테이블을 생성합니다.
mysql> create table DemoTable1961
(
Title text
);
Query OK, 0 rows affected (0.00 sec)
2. 샘플 데이터 삽입
INSERT 명령을 사용하여 문자열과 숫자가 섞여 있는 샘플 데이터를 삽입합니다.
mysql> insert into DemoTable1961 values('You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts');
Query OK, 1 row affected (0.00 sec)3. 저장된 데이터 확인
SELECT 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select * from DemoTable1961;
실행 결과는 다음과 같습니다.
+------------------------------------------------------------------------------------+
| Title |
+------------------------------------------------------------------------------------+
| You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4. CAST()와 REPLACE()로 숫자 추출하기
숫자 앞뒤에 있는 불필요한 문자열을 REPLACE() 함수로 차례대로 제거하고, 남은 값 '10001'을 CAST() 함수로 UNSIGNED(부호 없는 정수) 타입으로 변환합니다.
mysql> select cast(replace(replace('You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts','You_can_remove_the_string_part_only-',''),
'-But_You_can_not_remove_the_numeric_parts','') as unsigned) as Output from DemoTable1961;실행 결과는 다음과 같습니다.
+--------+
| Output |
+--------+
| 10001 |
+--------+
1 row in set (0.00 sec)
동작 원리 정리
위 쿼리는 다음 순서로 동작합니다.
1. 내부의 REPLACE()가 접두사 'You_can_remove_the_string_part_only-'를 빈 문자열로 치환하여 제거합니다.
2. 외부의 REPLACE()가 접미사 '-But_You_can_not_remove_the_numeric_parts'를 제거하여 숫자 '10001'만 남깁니다.
3. CAST(... AS UNSIGNED)가 남은 문자열을 정수 타입으로 변환하여 최종 결과를 반환합니다.
이처럼 REPLACE()와 CAST()를 조합하면 별도의 프로그래밍 로직 없이 SQL 쿼리만으로 형식이 고정된 문자열에서 숫자를 손쉽게 추출할 수 있습니다.