MySQL VARCHAR 필드에서 특정 문자열 등장 횟수 계산하는 방법

MySQL에서 VARCHAR 타입 컬럼에 저장된 문자열 안에 특정 문자열이 몇 번 등장하는지 확인해야 하는 경우가 있습니다. 이럴 때는 문자열 길이 차이를 활용한 뺄셈 로직을 사용하면 간단하게 해결할 수 있습니다. 핵심 원리는 다음과 같습니다.

① 전체 문자열의 길이에서, 찾고자 하는 문자열을 제거한 후의 길이를 뺍니다.
② 그 차이를 찾고자 하는 문자열 자체의 길이로 나누면 등장 횟수가 됩니다.

1. 테스트 테이블 생성

먼저 CREATE 명령어를 이용해 테스트용 테이블을 만들어 보겠습니다.

mysql> create table StringOccurrenceDemo
   -> (
   -> Cases varchar(100),
   -> StringValue varchar(500)
   -> );
Query OK, 0 rows affected (0.56 sec) 

2. 샘플 데이터 삽입

테이블 생성이 완료되면 아래 쿼리를 실행하여 레코드를 삽입합니다.

mysql> insert into StringOccurrenceDemo values('First','This is MySQL Demo and MySQL is an open source RDBMS');
Query OK, 1 row affected (0.07 sec)

mysql> insert into StringOccurrenceDemo values('Second','There is no');
Query OK, 1 row affected (0.20 sec)

mysql> insert into StringOccurrenceDemo values('Third','There is MySQL,Hi MySQL,Hello MySQL');
Query OK, 1 row affected (0.17 sec)

3. 전체 데이터 조회

SELECT 문으로 입력된 모든 레코드를 확인합니다.

mysql> select * From StringOccurrenceDemo;

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

+--------+------------------------------------------------------+
| Cases  | StringValue                                          |
+--------+------------------------------------------------------+
| First  | This is MySQL Demo and MySQL is an open source RDBMS |
| Second | There is no                                          |
| Third  | There is MySQL,Hi MySQL,Hello MySQL                  |
+--------+------------------------------------------------------+
3 rows in set (0.00 sec)

4. 문자열 'MySQL' 등장 횟수 계산하기

이제 LENGTH 함수와 REPLACE 함수를 조합하여 문자열 "MySQL"이 몇 번 등장하는지 계산하는 쿼리를 작성해 보겠습니다. 결과는 'NumberOfOccurrenceOfMySQL'이라는 별칭 컬럼에 표시됩니다.

mysql> SELECT Cases,StringValue,
   -> ROUND (
   -> (
   -> LENGTH(StringValue)- LENGTH( REPLACE (StringValue, "MySQL", "") )
   -> ) / LENGTH("MySQL")
   ->  ) AS NumberOfOccurrenceOfMySQL
   -> from StringOccurrenceDemo;

실행 결과는 아래와 같습니다.

+--------+------------------------------------------------------+---------------------------+
| Cases  | StringValue                                          |  NumberOfOccurrenceOfMySQL|
+--------+------------------------------------------------------+---------------------------+
| First  | This is MySQL Demo and MySQL is an open source RDBMS |                         2 |
| Second | There is                                             |                         0 |
| Third  | There is MySQL,Hi MySQL,Hello MySQL                  |                         3 |
+--------+------------------------------------------------------+---------------------------+
3 rows in set (0.05 sec)

결과 분석

위 출력 결과를 보면 각 행별로 'MySQL'이라는 문자열의 등장 횟수가 정확히 계산된 것을 확인할 수 있습니다.

• First: 'This is MySQL Demo and MySQL is an open source RDBMS' → 2회
• Second: 'There is no' → 0회 (해당 문자열 없음)
• Third: 'There is MySQL,Hi MySQL,Hello MySQL' → 3회

이처럼 LENGTH()와 REPLACE() 함수만 활용하면 별도의 복잡한 로직 없이도 VARCHAR 필드 내 특정 문자열의 등장 횟수를 손쉽게 구할 수 있습니다. 참고로 한글처럼 멀티바이트 문자를 다룰 때는 CHAR_LENGTH()와 LENGTH()의 차이를 고려하여 적절한 함수를 선택하는 것이 좋습니다.