MySQL에서 substr_replace처럼 문자열 일부를 치환하는 방법

MySQL의 INSERT() 함수로 substr_replace 기능 구현하기

PHP의 substr_replace()와 같은 기능이 필요하다면, MySQL에서는 INSERT() 함수를 사용하면 됩니다. 이 함수는 지정한 위치부터 일정 길이만큼의 부분 문자열을 새로운 문자열로 교체해 줍니다.

INSERT() 함수의 문법과 동작 방식

기본 문법은 다음과 같습니다.

INSERT(str, pos, len, newstr)

각 인자의 의미와 동작은 아래와 같습니다.

• str: 원본 문자열
• pos: 치환을 시작할 위치
• len: 치환할 문자 수
• newstr: 삽입할 새로운 문자열

함수는 문자열 str에서 pos 위치부터 시작해 len 길이만큼의 부분 문자열을 newstr로 대체한 결과를 반환합니다. 만약 pos가 문자열 길이 범위를 벗어나면 원본 문자열을 그대로 반환합니다.

또한 len이 나머지 문자열의 길이를 초과하는 경우에는 pos 위치부터 문자열 끝까지 전부 newstr로 교체됩니다. 인자 중 하나라도 NULL이면 결과 역시 NULL을 반환합니다.

예제 테이블 생성하기

먼저 예제에 사용할 테이블을 생성해 보겠습니다.

mysql> create table DemoTable
(
   Password varchar(50)
);
Query OK, 0 rows affected (0.51 sec)

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

mysql> insert into DemoTable values('76367_____8793443');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('12345_____9899984322');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('99999_____4747747432');
Query OK, 1 row affected (0.22 sec)

select 문으로 테이블의 모든 레코드를 확인해 보겠습니다.

mysql> select *from DemoTable;

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

+----------------------+
| Password             |
+----------------------+
| 76367_____8793443    |
| 12345_____9899984322 |
| 99999_____4747747432 |
+----------------------+
3 rows in set (0.00 sec)

INSERT() 함수로 특정 위치의 문자열 치환하기

이제 INSERT() 함수를 사용해 5번째 위치부터 'PPPPPP'로 문자열을 치환하는 쿼리입니다.

mysql> select insert(Password,5,length('PPPPPP'),'PPPPPP') from DemoTable;

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

+----------------------------------------------+
| insert(Password,5,length('PPPPPP'),'PPPPPP') |
+----------------------------------------------+
| 7636PPPPPP8793443                            |
| 1234PPPPPP9899984322                         |
| 9999PPPPPP4747747432                         |
+----------------------------------------------+
3 rows in set (0.00 sec)

위 결과에서 볼 수 있듯이, 각 비밀번호 문자열의 5번째 위치부터 6글자가 'PPPPPP'로 성공적으로 교체되었습니다. 이처럼 MySQL의 INSERT() 함수를 활용하면 PHP의 substr_replace()와 동일한 방식으로 문자열의 특정 구간을 손쉽게 치환할 수 있습니다.