MySQL에서 특정 숫자가 포함된 값을 검색하고 싶다면 SUBSTRING_INDEX() 함수를 활용할 수 있습니다. 이 함수는 LIKE 연산자와 함께 사용하면 원하는 숫자의 위치를 기준으로 결과를 정렬하는 것도 가능합니다.
실제 동작 과정을 예제를 통해 단계별로 살펴보겠습니다.
1. 테이블 생성
먼저 숫자 데이터를 저장할 테이블을 생성합니다.
mysql> create table DemoTable (Number varchar(100));
Query OK, 0 rows affected (0.60 sec)
2. 샘플 데이터 삽입
INSERT 명령을 사용하여 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable values('235678');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('1634990');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('678590');
Query OK, 1 row affected (0.46 sec)
mysql> insert into DemoTable values('908765432');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('222343388773');
Query OK, 1 row affected (0.35 sec)
mysql> insert into DemoTable values('09028215');
Query OK, 1 row affected (0.51 sec)3. 전체 레코드 확인
SELECT 문으로 테이블에 저장된 모든 레코드를 조회합니다.
mysql> select *from DemoTable;
출력 결과
+--------------+
| Number |
+--------------+
| 235678 |
| 1634990 |
| 678590 |
| 908765432 |
| 222343388773 |
| 09028215 |
+--------------+
6 rows in set (0.00 sec)
4. 특정 숫자가 포함된 값 검색
이제 '5'라는 숫자가 포함된 값만 조회하고, 해당 숫자가 나타나기 전까지의 길이를 기준으로 오름차순 정렬하는 쿼리입니다.
mysql> select Number from DemoTable where Number LIKE '%5%' ORDER BY LENGTH(SUBSTRING_INDEX(Number, '5', 1));
출력 결과
+-----------+
| Number |
+-----------+
| 235678 |
| 678590 |
| 908765432 |
| 09028215 |
+-----------+
4 rows in set (0.00 sec)
쿼리 동작 원리
이 쿼리의 핵심은 SUBSTRING_INDEX(Number, '5', 1) 부분입니다. 이 함수는 문자열에서 구분자('5')가 처음 나타나기 전까지의 부분 문자열을 반환합니다. 그리고 LENGTH()로 그 길이를 계산하면, 숫자 '5'가 문자열 내에서 얼마나 앞쪽에 위치하는지 알 수 있습니다.
예를 들어 '235678'의 경우 '5' 앞부분인 '23'의 길이가 2이므로 가장 먼저 정렬되고, '09028215'처럼 '5'가 뒤쪽에 있는 값일수록 뒤로 밀리게 됩니다. 이러한 방식은 증분 검색(incremental search) 기능을 구현할 때 사용자가 입력한 숫자와 일치하는 항목을 자연스럽게 우선 노출하는 데 유용하게 활용됩니다.