개요
데이터베이스를 다루다 보면 'Chris Brown'과 'Chris.Brown'처럼 이름 사이의 마침표 유무만 다른 데이터를 동일한 조건으로 검색해야 하는 경우가 있습니다. 이럴 때는 검색 대상 컬럼에서 특정 문자를 제거한 뒤 비교하면 됩니다. 이 글에서는 MySQL의 REPLACE() 함수를 활용해 마침표를 무시하고 이름을 조회하는 방법을 단계별로 살펴보겠습니다.
1. 샘플 테이블 생성
먼저 실습에 사용할 테이블을 생성합니다.
mysql> create table DemoTable
(
Name varchar(40)
)
;
Query OK, 0 rows affected (0.63 sec)2. 테스트 데이터 삽입
INSERT 명령어로 마침표가 포함된 이름과 공백으로 구분된 이름을 함께 입력합니다.
mysql> insert into DemoTable values('John.Smith');
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable values('Chris Brown');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Chris.Brown');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('David Miller');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('Chris.Taylor');
Query OK, 1 row affected (0.18 sec)3. 전체 데이터 확인
SELECT 문으로 저장된 모든 레코드를 조회해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+--------------+ | Name | +--------------+ | John.Smith | | Chris Brown | | Chris.Brown | | David Miller | | Chris.Taylor | +--------------+ 5 rows in set (0.00 sec)
4. 특정 문자를 무시하는 검색 쿼리
핵심은 REPLACE() 함수입니다. Name 컬럼 값에서 마침표(.)를 빈 문자열로 치환한 후 비교하면, 마침표가 있든 없든 동일하게 매칭됩니다.
mysql> select *from DemoTable where replace(Name,'.','')='Chris Brown';
실행 결과는 다음과 같습니다.
+-------------+ | Name | +-------------+ | Chris Brown | +-------------+ 1 row in set (0.00 sec)
참고: REGEXP_REPLACE 활용하기
MySQL 8.0 이상을 사용한다면 정규식 기반의 REGEXP_REPLACE() 함수를 사용할 수도 있습니다. 여러 종류의 특수문자를 한 번에 제거해야 할 때 특히 유용합니다.
mysql> select *from DemoTable
where regexp_replace(Name, '[.]', '') = 'ChrisBrown';마무리
이처럼 REPLACE() 또는 REGEXP_REPLACE() 함수를 WHERE 절에서 활용하면 마침표, 하이픈 등 불필요한 구분 문자를 무시하고 원하는 데이터를 유연하게 검색할 수 있습니다. 다만 함수를 적용한 컬럼에는 인덱스가 사용되지 않으므로, 대용량 테이블에서는 생성 컬럼(generated column)에 인덱스를 추가하거나 데이터 입력 단계에서 형식을 통일하는 방안도 함께 고려하는 것이 좋습니다.