[PostgreSQL] VARCHAR 칼럼 내 일부 단어만 변경

한 테이블의 VARCHAR 칼럼 내 일부 단어를 다른 단어로 변경해야 하는 상황이 발생했다.

 

방법은 생각 외로 간단했다. 그냥 단순하게 REGEXP_REPLACE 함수를 사용하면 된다.

 

https://www.postgresqltutorial.com/postgresql-string-functions/regexp_replace/

 

이제 테스트를 한 번 해보자.

 

단순한 단어 변경

테스트용 데이터를 아래와 같이 생성해보았다.

 

CREATE TABLE IF NOT EXISTS replace_test (
	-- SERIAL 사용 시 postgresql은 AUTO_INCREMENT가 자동 적용됨.
	serial_no SERIAL PRIMARY KEY,
	simple_str VARCHAR(100),
	detail_str VARCHAR(1000)
);


INSERT INTO replace_test (simple_str, detail_str) 
VALUES 
('this', 'this is a cat'),
('that', 'that is a dog'),
('those', 'those are animals');

 

이렇게 적용하면 아래와 같이 데이터가 구성될 것이다.

 

 

이제 this 라는 단어를 that으로 변경해보자.

 

쿼리는 아래와 같다.

 

UPDATE replace_test
SET
	simple_str = REGEXP_REPLACE(simple_str, 'this', 'that'),
	detail_str = REGEXP_REPLACE(detail_str, 'this', 'that');

 

적용 시 아래와 같이 simple_str, detail_str의 this가 that으로 변경되어 있음을 확인할 수 있다.

 

 

 

그런데 만약 "this"가 하나가 아니라 여러 개라면 위 쿼리가 정상적으로 동작할까?

정답은 아니다. 아래의 데이터를 더 넣고 위 쿼리를 다시 실행시켜보자.

 

-- this가 두개라면?

INSERT INTO replace_test (simple_str, detail_str)
VALUES ('this this', 'this is a horse and this is a cat');

UPDATE replace_test
SET
	simple_str = REGEXP_REPLACE(simple_str, 'this', 'that'),
	detail_str = REGEXP_REPLACE(detail_str, 'this', 'that');

 

 

 

네 번째 줄을 확인하면 알 수 있듯, 맨 앞의 this만 변경되었고, 뒤에 나오는 this는 변경되지 않았다.

 

그렇다면 문장 내 모든 this를 변경하기 위해서는 어떻게 해야할까?

해결법은 간단하다. REGEXP_REPLACE의 마지막 파라미터에 'g'를 넣는 것이다.

 

다음 쿼리를 한 번 돌려보자.

-- 삭제 및 아까 데이터 재입력
DELETE FROM replace_test WHERE simple_str = 'that this';

INSERT INTO replace_test (simple_str, detail_str)
VALUES ('this this', 'this is a horse and this is a cat');

UPDATE replace_test
SET
	-- 마지막 파라미터에 g 추가
	simple_str = REGEXP_REPLACE(simple_str, 'this', 'that', 'g'),
	detail_str = REGEXP_REPLACE(detail_str, 'this', 'that', 'g');

 

 

앞의 this 뿐 아니라 뒤에 나오는 모든 this까지 정상 수정됨을 확인할 수 있다.

 

 

특정 글자로 시작하거나 끝나는 단어 수정

일반적인 정규 표현식도 가능한 것으로 보인다.

다만 굳이 이걸 나열할 필요는 없을 것 같고, 쿼리 내에서 곧바로 적용 가능한 특수한 메타문자를 몇 개만 적어놓고 테스트도 해보고자 한다.

메타문자	용도	예시	설명
\m	시작점 지정	\mwo	단어의 시작으로 사용되는 wo 식별
\M	종료점 지정	rd\M	단어의 끝으로 사용되는 rd 식별
\y	단어의 경계 식별	\yword\y	word라는 하나의 완전한 단어 식별

 

위 경우는 아래와 같이 사용될 수 있다.

• this cat is wildcat. those cats are good. 
  • 맨 앞의 cat만 변경되어야 하는 경우
    • \mcat 혹은 \ycat\y
  • 중간의 wildcat만 변경되어야 하는 경우
    • \ywildcat\y
  • 맨 뒤의 cats만 변경되어야 하는 경우
    • \mcats 혹은 \ycats\y 혹은 cats\M

 

이제 각 경우에 대해 테스트를 아래와 같이 해보자. 설명은 위에 간단하게 기재했으므로 생략하고, 쿼리와 결과 캡쳐만 담아보았다.

 

 

\m 실험

-- 데이터 초기화
DELETE FROM replace_test;

-- 타겟 데이터 입력
INSERT INTO replace_test (simple_str, detail_str)
VALUES
	('wo awo bwo woc', 'wo awo bwo woc');

UPDATE replace_test
SET
	simple_str = REGEXP_REPLACE(simple_str, '\mwo', 'WO', 'g'),
	detail_str = REGEXP_REPLACE(detail_str, '\mwo', 'WO', 'g');

 

모든 wo가 아니라 단어의 시작인 wo만 수정됨

 

\M 실험

-- 데이터 초기화
DELETE FROM replace_test;

-- 타겟 데이터 입력
INSERT INTO replace_test (simple_str, detail_str)
VALUES
	('wo awo bwo woc', 'wo awo bwo woc');

UPDATE replace_test
SET
	simple_str = REGEXP_REPLACE(simple_str, 'wo\M', 'WO', 'g'),
	detail_str = REGEXP_REPLACE(detail_str, 'wo\M', 'WO', 'g');

 

단어의 끝이 wo인 것만 변경됨.

 

\y 실험

-- 데이터 초기화
DELETE FROM replace_test;

-- 타겟 데이터 입력
INSERT INTO replace_test (simple_str, detail_str)
VALUES
	('wo awo bwo woc', 'wo awo bwo woc');

UPDATE replace_test
SET
	simple_str = REGEXP_REPLACE(simple_str, '\ywo\y', 'WO', 'g'),
	detail_str = REGEXP_REPLACE(detail_str, '\ywo\y', 'WO', 'g');

wo라는 완전한 하나의 단어만 변경됨