MySQL LOCATE() 함수로 부분 문자열 위치 기반 검색 결과 정렬하기

MySQL에서 부분 문자열 위치를 기준으로 검색 결과 정렬하기

검색 결과를 특정 부분 문자열이 나타나는 위치를 기준으로 정렬하고 싶다면 ORDER BY LOCATE()를 사용하면 됩니다. LOCATE() 함수는 문자열 안에서 해당 부분 문자열이 처음 등장하는 위치(인덱스)를 반환하므로, 이 값을 기준으로 정렬하면 검색어가 앞쪽에 있는 행부터 순서대로 조회됩니다.

간단한 예제를 통해 살펴보겠습니다. 먼저 테이블을 생성합니다.

mysql> create table DemoTable1838
    (
    Subject varchar(100)
    );
Query OK, 0 rows affected (0.00 sec)

테이블에 데이터 삽입

INSERT 명령어를 사용해 몇 개의 레코드를 추가합니다.

mysql> insert into DemoTable1838 values('MongoDB MySQL');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1838 values('MySQL Java');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1838 values('JavaWithMySQL');
Query OK, 1 row affected (0.00 sec)

저장된 데이터 확인

SELECT 문으로 테이블의 전체 레코드를 조회합니다.

mysql> select * from DemoTable1838;

실행 결과는 다음과 같습니다.

+---------------+
| Subject       |
+---------------+
| MongoDB MySQL |
| MySQL Java    |
| JavaWithMySQL |
+---------------+
3 rows in set (0.00 sec)

LOCATE()로 정렬하는 쿼리 작성

'MySQL'이라는 단어가 포함된 행만 조회하면서, 'MySQL'이 문자열 내에서 등장하는 위치를 기준으로 정렬하는 쿼리는 다음과 같습니다.

mysql> select * from DemoTable1838
    where Subject LIKE '%MySQL%'
    ORDER BY LOCATE('MySQL', Subject);

실행 결과는 다음과 같습니다.

+---------------+
| Subject       |
+---------------+
| MySQL Java    |
| MongoDB MySQL |
| JavaWithMySQL |
+---------------+
3 rows in set (0.00 sec)

결과를 보면 'MySQL Java'는 'MySQL'이 1번째 위치에 있기 때문에 가장 먼저 표시되었습니다. 나머지 두 행은 모두 9번째 위치에 'MySQL'을 포함하고 있어 동일한 순위를 가지며, 이 경우 두 행 사이의 순서는 보장되지 않습니다. 이처럼 WHERE 절의 LIKE 조건과 ORDER BY LOCATE()를 함께 사용하면 검색어가 앞쪽에 등장하는 데이터를 우선적으로 노출할 수 있어, 검색 결과의 관련도를 높이는 데 유용하게 활용됩니다.