MySQL에서 열 값의 특수 문자를 제거하는 방법

데이터베이스 작업 중에는 열 값에 포함된 특수 문자(예: #, @, - 등)를 제거해야 하는 경우가 자주 있습니다. 이 글에서는 MySQL의 REPLACE() 함수를 사용하여 특수 문자를 간단하게 삭제하는 방법을 예제와 함께 살펴보겠습니다.

1. 테이블 생성하기

먼저 실습에 사용할 테이블을 생성합니다.

mysql> create table DemoTable
-> (
-> StudentId varchar(100)
-> );
Query OK, 0 rows affected (0.53 sec)

2. 샘플 데이터 삽입하기

INSERT 명령을 사용하여 특수 문자가 포함된 학생 ID 데이터를 삽입합니다.

mysql> insert into DemoTable values('STU#123');
Query OK, 1 row affected (0.18 sec)

mysql> insert into DemoTable values('STU#567');
Query OK, 1 row affected (0.15 sec)

mysql> insert into DemoTable values('STU#98494');
Query OK, 1 row affected (0.19 sec)

3. 현재 데이터 확인하기

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

mysql> select *from DemoTable;

다음과 같은 출력 결과를 확인할 수 있습니다.

+-----------+
| StudentId |
+-----------+
| STU#123   |
| STU#567   |
| STU#98494 |
+-----------+
3 rows in set (0.00 sec)

모든 값에 특수 문자 #가 포함되어 있는 것을 볼 수 있습니다.

4. REPLACE() 함수로 특수 문자 제거하기

특수 문자를 제거하려면 REPLACE() 함수를 사용한 UPDATE 쿼리를 실행하면 됩니다. 아래 쿼리는 # 문자를 빈 문자열('')로 대체합니다.

mysql> update DemoTable
-> set StudentId=replace(StudentId,'#','');
Query OK, 3 rows affected (0.13 sec)
Rows matched: 3 Changed: 3 Warnings: 0

REPLACE() 함수 문법

REPLACE(컬럼명, '찾을_문자열', '대체할_문자열')

세 번째 인자에 빈 문자열을 넣으면 해당 문자를 삭제하는 것과 동일한 효과를 얻을 수 있습니다.

5. 변경 결과 확인하기

업데이트 후 테이블의 레코드를 다시 한번 조회해 보겠습니다.

mysql> select *from DemoTable;

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

+-----------+
| StudentId |
+-----------+
| STU123    |
| STU567    |
| STU98494  |
+-----------+
3 rows in set (0.00 sec)

모든 StudentId 값에서 # 문자가 성공적으로 제거된 것을 확인할 수 있습니다.

참고: 여러 종류의 특수 문자 제거하기

제거해야 할 특수 문자가 여러 개라면 REPLACE() 함수를 중첩해서 사용할 수 있습니다.

UPDATE DemoTable
SET StudentId = replace(replace(StudentId, '#', ''), '@', '');

또한 MySQL 5.5 이상 버전에서는 정규식 기반의 REGEXP_REPLACE() 함수를 사용하면 더 유연하게 처리할 수 있습니다.

UPDATE DemoTable
SET StudentId = regexp_replace(StudentId, '[^a-zA-Z0-9]', '');

위 쿼리는 영문자와 숫자를 제외한 모든 특수 문자를 한 번에 제거합니다.