MySQL에서 LIKE로 행을 조회하고 일치한 문자열을 담는 새 컬럼 만들기

MySQL에서 LIKE 조건으로 특정 문자열이 포함된 행을 조회하면서, 동시에 일치한 문자열 자체를 값으로 가지는 새로운 컬럼을 만들고 싶다면 SUBSTRING() 함수를 활용하면 됩니다. 아래에서 단계별로 살펴보겠습니다.

1. 테이블 생성

먼저 예제용 테이블을 생성합니다.

mysql> create table DemoTable1872
    (
    Name varchar(20)
    );
Query OK, 0 rows affected (0.00 sec)

2. 데이터 삽입

INSERT 명령으로 몇 개의 레코드를 추가합니다.

mysql> insert into DemoTable1872 values('John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1872 values('Adam Smith');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1872 values('Mitchell Johnson');
Query OK, 1 row affected (0.00 sec)

3. 전체 레코드 확인

SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.

mysql> select * from DemoTable1872;

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

+------------------+
| Name             |
+------------------+
| John Doe         |
| Adam Smith       |
| Mitchell Johnson |
+------------------+
3 rows in set (0.00 sec)

4. LIKE와 SUBSTRING()으로 일치 문자열 추출하기

핵심 쿼리입니다. WHERE 절의 LIKE '%John%'로 'John'이 포함된 행만 필터링하고, SELECT 절에서는 SUBSTRING()과 LOCATE(), LENGTH() 함수를 조합해 일치한 문자열을 새 컬럼(NewName)에 담습니다.

mysql> select Name,
    substring(Name, locate('John', Name), length('John')) as NewName
    from DemoTable1872
    where Name like '%John%';

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

+------------------+---------+
| Name             | NewName |
+------------------+---------+
| John Doe         | John    |
| Mitchell Johnson | John    |
+------------------+---------+
2 rows in set (0.00 sec)

쿼리 동작 원리

• LOCATE('John', Name): Name 컬럼 값 내에서 'John'이 시작되는 위치(인덱스)를 반환합니다.
• LENGTH('John'): 추출할 문자열의 길이를 지정합니다.
• SUBSTRING(Name, 위치, 길이): 해당 위치부터 지정한 길이만큼 문자열을 잘라냅니다.

이처럼 세 함수를 조합하면 LIKE 검색으로 걸러낸 행에서 실제 매칭된 부분 문자열을 별도 컬럼으로 손쉽게 추출할 수 있습니다.