PHP 등 다른 언어에 익숙한 개발자라면 문자열 치환 함수인 str_replace가 익숙할 것입니다. 하지만 MySQL에는 str_replace라는 이름의 함수가 없으며, 대신 동일한 기능을 수행하는 REPLACE() 함수를 제공합니다. 이번 글에서는 REPLACE() 함수의 사용법을 실제 예제와 함께 단계별로 살펴보겠습니다.
1. 테이블 생성하기
REPLACE() 함수의 동작을 확인하기 위해 먼저 간단한 테이블을 생성하겠습니다.
mysql> create table StringReplaceDemo -> ( -> Id int, -> URL varchar(200) -> ); Query OK, 0 rows affected (0.38 sec)
위 쿼리는 정수형 Id 컬럼과 최대 200자까지 저장 가능한 URL 컬럼으로 구성된 테이블을 만듭니다.
2. 샘플 데이터 삽입하기
INSERT 명령을 사용해 테이블에 몇 개의 레코드를 추가해 보겠습니다.
mysql> insert into StringReplaceDemo values(1001,'https://www.google.co.in'); Query OK, 1 row affected (0.09 sec) mysql> insert into StringReplaceDemo values(1002,'https://www.facebook.com'); Query OK, 1 row affected (0.11 sec) mysql> insert into StringReplaceDemo values(1003,'https://mail.google.com'); Query OK, 1 row affected (0.12 sec)
세 개의 URL 데이터가 정상적으로 입력되었습니다.
3. 전체 레코드 조회하기
입력된 데이터를 확인하기 위해 아래 쿼리로 테이블의 모든 레코드를 조회합니다.
mysql> select * from StringReplaceDemo;
실행 결과는 다음과 같습니다.
+------+--------------------------+ | Id | URL | +------+--------------------------+ | 1001 | https://www.google.co.in | | 1002 | https://www.facebook.com | | 1003 | https://mail.google.com | +------+--------------------------+ 3 rows in set (0.00 sec)
4. REPLACE() 함수로 문자열 치환하기
출력 결과를 보면 세 번째 레코드(Id: 1003)의 URL에 'mail'이라는 단어가 포함되어 있습니다. 이제 이 단어를 'www'로 변경해 보겠습니다.
mysql> update StringReplaceDemo set URL = replace(URL,'mail','www') where URL like '%mail%'; Query OK, 1 row affected (0.15 sec) Rows matched: 1 Changed: 1 Warnings: 0
REPLACE() 함수는 세 가지 인자를 받습니다.
- 첫 번째 인자: 치환 작업을 수행할 대상 문자열 또는 컬럼
- 두 번째 인자: 찾고자 하는 문자열
- 세 번째 인자: 교체할 새로운 문자열
또한 WHERE 절에 LIKE '%mail%' 조건을 추가하여 'mail'이 포함된 레코드만 업데이트하도록 제한했습니다. WHERE 절 없이 실행하면 테이블 내 모든 행에 대해 치환이 수행되므로 주의가 필요합니다.
5. 결과 확인하기
'mail'이라는 단어가 실제로 'www'로 변경되었는지 다시 한번 조회해 보겠습니다.
mysql> select * from StringReplaceDemo;
아래 출력 결과에서 치환이 성공적으로 완료된 것을 확인할 수 있습니다.
+------+--------------------------+ | Id | URL | +------+--------------------------+ | 1001 | https://www.google.co.in | | 1002 | https://www.facebook.com | | 1003 | https://www.google.com | +------+--------------------------+ 3 rows in set (0.00 sec)
Id 1003 레코드의 URL이 https://mail.google.com에서 https://www.google.com으로 변경된 것을 볼 수 있습니다.
마무리 및 참고 사항
MySQL에서는 str_replace 대신 REPLACE() 함수를 사용하면 UPDATE문과 SELECT문 어디서든 손쉽게 문자열 치환이 가능합니다. 몇 가지 유의할 점은 다음과 같습니다.
- 대소문자 구분: REPLACE() 함수는 기본 collation 설정에 따라 대소문자를 구분할 수 있습니다.
- SELECT에서도 사용 가능: 데이터를 변경하지 않고 화면에 출력되는 값만 치환하고 싶다면
SELECT REPLACE(컬럼명, '찾을값', '바꿀값')형태로 사용할 수 있습니다. - 백업 권장: UPDATE문으로 대량의 데이터를 치환하기 전에는 반드시 백업을 진행하는 것이 안전합니다.