MySQL에서 특정 문자열의 등장 횟수를 세는 방법

MySQL에서 특정 문자열이 문자열 값 안에 몇 번 등장하는지 확인하려면 LENGTH() 함수와 REPLACE(), CONCAT() 함수를 함께 활용하면 됩니다. 이 기법은 원본 문자열과 치환 후 문자열의 길이 차이를 계산하여 발생 횟수를 구하는 방식입니다.

먼저 예제에 사용할 테이블을 생성해 보겠습니다.

mysql> create table DemoTable
-> (
-> Value text
-> );
Query OK, 0 rows affected (0.74 sec)

다음으로 insert 명령을 사용해 테이블에 레코드를 삽입합니다.

mysql> insert into DemoTable values('10,20,10,30,10,40,50,40');
Query OK, 1 row affected (0.24 sec)

select 문을 사용해 테이블의 모든 레코드를 조회합니다.

mysql> select *from DemoTable;

출력 결과

위 쿼리는 다음과 같은 결과를 출력합니다.

+-------------------------+
| Value                   |
+-------------------------+
| 10,20,10,30,10,40,50,40 |
+-------------------------+
1 row in set (0.00 sec)

이제 MySQL에서 특정 문자열의 발생 횟수를 찾는 쿼리를 살펴보겠습니다. 여기서는 값 안에 '10'이 몇 번 나타나는지 확인합니다.

mysql> select LENGTH(Value) + 2 -LENGTH(REPLACE(CONCAT(',', Value, ','), ',10,', 'len'))from DemoTable;

출력 결과

위 쿼리는 다음과 같은 결과를 출력합니다.

+----------------------------------------------------------------------------+
| LENGTH(Value) + 2 -LENGTH(REPLACE(CONCAT(',', Value, ','), ',10,', 'len')) |
+----------------------------------------------------------------------------+
| 3                                                                          |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

쿼리 동작 원리

이 쿼리가 어떻게 작동하는지 단계별로 살펴보겠습니다.

1. 양 끝에 콤마 추가: CONCAT(',', Value, ',')는 값의 앞뒤에 콤마를 붙여 ',10,20,10,30,10,40,50,40,' 형태로 만듭니다. 이렇게 하면 문자열의 시작이나 끝에 있는 '10'도 정확하게 매칭할 수 있습니다.

2. 대상 문자열 치환: REPLACE(..., ',10,', 'len')은 ',10,'(4자)를 'len'(3자)으로 교체합니다. 치환 문자열이 원본보다 정확히 1자 짧기 때문에, '10'이 한 번 발견될 때마다 전체 길이가 1씩 줄어듭니다.

3. 길이 차이 계산: 원본 문자열의 길이(LENGTH(Value) + 2)에서 치환된 문자열의 길이를 빼면, 그 차이값이 곧 '10'의 등장 횟수가 됩니다.

위 예제에서는 '10'이 세 번 등장했으므로 결과값으로 3이 반환되었습니다. 이 방법은 별도의 저장 프로시저나 반복문 없이 순수 SQL만으로 문자열 발생 횟수를 손쉽게 계산할 수 있다는 장점이 있습니다.