Computer >> 컴퓨터 >  >> 프로그래밍 >> MySQL

MySQL에서 구분자 사이의 텍스트 추출하기: LOCATE()와 SUBSTR() 활용법

MySQL에서 특정 구분자로 둘러싸인 텍스트를 추출하려면 LOCATE() 함수와 SUBSTR() 함수를 함께 사용해야 합니다. 아래에서 소개하는 문법은 구분자(예제에서는 콜론(:) 사용) 사이에 위치한 단어를 찾아 반환합니다. 물론 콜론 대신 다른 구분자를 사용할 수도 있으며, 이는 전적으로 사용자의 필요에 따라 자유롭게 선택하시면 됩니다.

기본 문법

다음은 구분자 뒤에 오는 단어를 찾는 기본 문법입니다.

SELECT
   SUBSTR(yourColumnName,
      LOCATE(''',yourColumnName)+1,
         (CHAR_LENGTH(yourColumnName) - LOCATE(''',REVERSE(yourColumnName)) - LOCATE(''',yourColumnName))) AS anyAliasName
FROM yourTableName;

예제 테이블 생성

위 문법을 실제로 이해하기 위해 먼저 테이블을 생성해 보겠습니다. 테이블 생성 쿼리는 다음과 같습니다.

mysql> create table SearchTextBetweenDelimitersDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Words longtext,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.67 sec)

샘플 데이터 삽입

이제 INSERT 명령을 사용하여 테이블에 몇 개의 레코드를 삽입합니다.

mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('Object:Oriented:Programming');
Query OK, 1 row affected (0.21 sec)
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('C++:Java:C#');
Query OK, 1 row affected (0.21 sec)
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('SQLServer:MySQL:Oracle');
Query OK, 1 row affected (0.17 sec)
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('SQL SQLServer:MySQL:Oracle');
Query OK, 1 row affected (0.18 sec)

저장된 데이터 확인

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

mysql> select *from SearchTextBetweenDelimitersDemo;

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

+----+-----------------------------+
| Id | Words                       |
+----+-----------------------------+
|  1 | Object:Oriented:Programming |
|  2 | C++:Java:C#                 |
|  3 | SQLServer:MySQL:Oracle      |
|  4 | SQL SQLServer:MySQL:Oracle  |
+----+-----------------------------+
4 rows in set (0.00 sec)

구분자 사이의 텍스트 검색 쿼리 실행

구분자 사이의 단어를 검색하여 화면에 출력하는 쿼리는 다음과 같습니다.

mysql> SELECT
    -> SUBSTR(Words,
    -> LOCATE(':',Words)+1,
    -> (CHAR_LENGTH(Words) - LOCATE(':',REVERSE(Words)) - LOCATE(':',Words))) AS Text
   -> FROM SearchTextBetweenDelimitersDemo;

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

+----------+
| Text     |
+----------+
| Oriented |
| Java     |
| MySQL    |
| MySQL    |
+----------+
4 rows in set (0.00 sec)

쿼리 동작 원리

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

  • LOCATE(':', Words)+1 : 첫 번째 콜론의 위치를 찾은 후, 그 다음 문자부터 추출을 시작합니다.
  • LOCATE(':', REVERSE(Words)) : 문자열을 뒤집은 상태에서 첫 번째 콜론의 위치를 찾아, 마지막 콜론까지의 상대적 거리를 계산합니다.
  • CHAR_LENGTH(Words) : 전체 문자열 길이에서 앞쪽 콜론 위치와 뒤쪽 콜론 위치를 빼면, 가운데 텍스트의 정확한 길이가 산출됩니다.

결과적으로 이 방식은 'A:B:C'처럼 세 부분으로 구성된 문자열에서 가운데 부분(B)만 깔끔하게 추출할 수 있는 강력하고 실용적인 기법입니다. 데이터 정규화가 되어 있지 않은 레거시 시스템이나 로그 데이터를 다룰 때 특히 유용하게 활용할 수 있습니다.