데이터베이스 작업 중 'Smith_John'처럼 접두사와 밑줄(_)이 붙은 문자열에서, 밑줄 뒤의 실제 이름 부분만 남기고 싶은 경우가 있습니다. 이럴 때 MySQL의 SUBSTRING() 함수와 LIKE 조건절을 조합하면 간단하게 처리할 수 있습니다. 지금부터 단계별 예제를 통해 살펴보겠습니다.
1. 테이블 생성하기
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table DemoTable
(
UserName varchar(100)
);
Query OK, 0 rows affected (0.69 sec)2. 샘플 데이터 삽입하기
INSERT 명령어를 사용해 몇 개의 레코드를 추가합니다.
mysql> insert into DemoTable values('Smith_John');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('Smith_Adam');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('Smith_David');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Smith_Mike');
Query OK, 1 row affected (0.12 sec)3. 저장된 데이터 확인하기
SELECT 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+-------------+ | UserName | +-------------+ | Smith_John | | Smith_Adam | | Smith_David | | Smith_Mike | +-------------+ 4 rows in set (0.00 sec)
4. 밑줄 뒤 문자열만 남기는 UPDATE 쿼리
다음은 'Smith_'로 시작하는 값에서 접두사를 제거하고, 밑줄 뒤의 문자열만 남기는 쿼리입니다.
mysql> update DemoTable set UserName=substring(UserName,7) where UserName LIKE 'Smith_%'; Query OK, 4 rows affected (0.12 sec) Rows matched: 4 Changed: 4 Warnings: 0
여기서 핵심은 SUBSTRING(UserName, 7)입니다. 이 함수는 문자열의 7번째 위치부터 끝까지를 반환합니다. 'Smith_'가 정확히 6글자이므로, 7번째 문자부터 읽으면 밑줄 뒤에 있는 이름 부분만 남게 됩니다. 또한 WHERE 절에 LIKE 'Smith_%' 조건을 사용해 해당 패턴에 맞는 행만 선택적으로 업데이트했습니다.
참고로 접두사 길이가 다른 데이터가 섞여 있다면, LOCATE() 함수를 활용해 SUBSTRING(UserName, LOCATE('_', UserName) + 1)처럼 밑줄 위치를 동적으로 찾는 방법도 유용합니다.
5. 변경 결과 확인하기
테이블을 다시 조회해 업데이트가 정상적으로 적용되었는지 확인합니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+----------+ | UserName | +----------+ | John | | Adam | | David | | Mike | +----------+ 4 rows in set (0.00 sec)
'Smith_' 접두사가 모두 제거되고 이름 부분만 깔끔하게 남은 것을 확인할 수 있습니다. 이처럼 SUBSTRING() 함수와 LIKE 조건을 활용하면 특정 패턴의 문자열을 손쉽게 가공할 수 있습니다.