[MariaDB] 대소문자 구분 없이 문자열 치환하기

정규식을 사용하여 컬럼에서 특정단어를 가지고 있는 데이터를 찾아 해당단어만 다른 단어로 변경하는 UPDATE 문

 

 

◆ 테이블( city )의 컬럼( city_name )에서 대문자 'S'가 들어가 있는 단어를 찾아 대문자 'A'로 변경하는 경우

---

UPDATE city
SET city_name = REPLACE(city_name, 'S', 'A')
WHERE city_name REGEXP 'S';

• REGEXP 'S'는 정규식 패턴으로 문자열 안에 대문자 'S'가 포함되어 있으면 매칭된다.

city_name
(변경전)	(변경후)
SEOUL	AEOUL
Seoul	Aeoul
seoul	seoul
Busan	Busan
jeju	jeju

 

 

◆ 테이블( city )의 컬럼( city_name )에서 대소문자 구분 없이 'S'가 들어가 있는 단어를 찾아 대문자 'A'로 변경하는 경우

---

UPDATE city
SET city_name = REPLACE(REPLACE(city_name, 'S', 'A'), 's', 'A')
WHERE city_name REGEXP 'S|s';

• REGEXP 'S|s' 또는 REGEXP '[S|s]'는 정규식 패턴으로 문자열 안에 대문자 'S' 또는 소문자 's'가 포함되어 있으면 매칭된다.
• REPLACE(REPLACE(city_name, 'S', 'A'), 's', 'A')는 하나의 데이터를 두 번 가공하는 구조라고 이해하면 된다.
  1차 가공 : REPLACE(city_name, 'S', 'A') → 원본 문자열에서 대문자 'S'를 'A'로 변경
  2차 가공 : REPLACE(1차 가공된 결과, 's', 'A') → 1차 가공된 결과에서 소문자 's'를 'A'로 변경

city_name
(변경전)	(변경후)
SEOUL	AEOUL
Seoul	Aeoul
seoul	Aeoul
Busan	BuAan
jeju	jeju

 

 

◆ REPLACE() 결과가 기대치와 다르게 대소문자가 제대로 적용되지 않은 경우

---

간혹 UPDATE시에 COLLATION로 인해 일부 데이터가 바뀌지 않는 경우가 있다.
COLLATION란 대소문자 구분 여부(Case-sensitive vs Case-insensitive)와 악센트(발음 기호) 구분 여부 (Accent-sensitive vs Accent-insensitive) 이 두 가지 요소를 기준으로 정의된 문자열을 비교하거나 정렬할 때 사용하는 규칙인데,

 

해당 컬럼의 비교 방식이 무엇인지 확인하기 위해 city 테이블의 Collation 항목값을 보았더니 역시나 "utf8mb3_general_ci"로 Case-insensitive(바꿀 필요 없음) 방식이었다.

 

즉,  REPLACE()는 대소문자를 구분해서 동작하지만 ('S'만 'A'로, 's'만 'A'로) city_name 컬럼의 COLLATION이 Case-insensitive이기 때문에, 'Seoul'과 'SEOUL'은 같은 문자열로 보고 MariaDB는 값이 변하지 않았다고 판단하고 UPDATE를 생략하는 것이다.

SHOW FULL COLUMNS FROM 테이블명;

 

해결방법 1
UPDATE시에만 임시적으로 COLLATE 방식 변경(대소문자 구분)

• SET절에서는 COLLATE utf8mb3_bin을 사용하여 REPLACE()가 대소문자를 구분하도록 처리한다.
• WHERE절에는 BINARY를 추가하여 MariaDB가 값이 실제로 바뀌었는지 정확히 판단할 수 있도록 한다.

UPDATE city
SET city_name = REPLACE(REPLACE(city_name COLLATE utf8mb3_bin, 'S', 'A') COLLATE utf8mb3_bin, 's', 'A')
WHERE BINARY city_name REGEXP 'S|s';

 

해결방법 2 

컬럼의 COLLATION을 아에 영구적으로 변경한 뒤 UPDATE 실행 (실제 운영 데이터에서는 신중히 써야 한다.)

-- city_name 컬럼을 대소문자 구분되도록 바꾸기
ALTER TABLE city
MODIFY city_name VARCHAR(100)
CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;

-- 그리고 UPDATE
UPDATE city
SET city_name = REPLACE(REPLACE(city_name, 'S', 'A'), 's', 'A')
WHERE city_name REGEXP 'S|s';

 

---

글 내용 중 잘못된 부분이 있거나, 첨부하실 내용이 있으시면 댓글로 남겨주세요. 공부하는데 많은 도움이 됩니다.
-- 기억의 유효기간은 생각보다 짧다. --