엑셀에서 복사해 붙인 데이터나 웹 폼 textarea 로 들어온 값을 DB에 넣고 나면, 눈에는 안 보이는 탭이나 줄바꿈 문자가 끼어 있는 경우가 꽤 많습니다. 화면에서는 멀쩡해 보이는데 WHERE code = 'A001' 로 찾으면 안 나오고, 길이를 찍어 보면 한두 글자가 더 길게 나오죠. 이럴 때 쓰는 게 MySQL의 REPLACE() 입니다.
REPLACE(문자열, 찾을 문자열, 바꿀 문자열)찾은 문자열을 전부 바꿉니다. 첫 번째 것만 바꾸는 게 아니라 문자열 안에 있는 것 모두가 대상이고, 대소문자를 구분합니다. 바꿀 문자열에 빈 문자열 '' 을 주면 그냥 삭제가 됩니다. 아래 예제에서 table 은 테이블명, field 는 컬럼명 자리입니다.
문자별 제거 쿼리
보이지 않는 문자는 따옴표 안에 직접 쓰기 어려우니 CHAR(코드) 로 지정합니다. 자주 쓰는 건 세 가지입니다.
| 문자 | 코드 | 쿼리에서 쓰는 형태 |
|---|---|---|
| 탭 (Tab) | 9 | CHAR(9) |
라인피드 (LF, \n) |
10 | CHAR(10) |
캐리지리턴 (CR, \r) |
13 | CHAR(13) |
공백 제거:
update table set field = replace(field, ' ', '');여기서 착각하기 쉬운 게 있는데, 이 쿼리는 앞뒤 공백만 지우는 게 아니라 문자열 중간의 공백까지 전부 없앱니다. "홍 길동" 이 "홍길동" 이 되는 거죠. 상품코드나 전화번호처럼 공백이 아예 없어야 하는 컬럼이면 괜찮지만, 이름이나 주소에 걸면 데이터가 망가집니다. 양 끝 공백만 정리하려면 TRIM() 을 쓰세요.
update table set field = trim(field);윈도우 줄바꿈(CR+LF) 제거:
update table set field = replace(field, '\r\n', '');MySQL 문자열 리터럴 안의 \r, \n 은 이스케이프로 해석되어 실제 CR, LF 문자가 됩니다. 단, 서버가 NO_BACKSLASH_ESCAPES 모드로 돌고 있으면 글자 그대로 백슬래시가 되니, 이때는 아래처럼 CHAR() 방식을 쓰는 게 확실합니다.
탭 제거:
update table set field = replace(field, char(9), '');라인피드 제거:
update table set field = replace(field, char(10), '');캐리지리턴 제거:
update table set field = replace(field, char(13), '');\r\n 을 한 번에 지우는 쿼리는 CR과 LF가 붙어 있을 때만 걸립니다. 맥이나 리눅스에서 만든 데이터처럼 LF만 들어 있는 경우도 있어서, 저는 보통 CR과 LF를 따로 지우는 쪽을 씁니다.
한 번에 여러 문자 지우기
REPLACE 는 결과가 다시 문자열이라 중첩해서 쓸 수 있습니다. 탭·CR·LF를 한 쿼리로 정리하고 마지막에 앞뒤 공백까지 다듬으면 이렇게 됩니다.
update table
set field = trim(replace(replace(replace(field, char(9), ''), char(13), ''), char(10), ''));같은 방식으로 다른 문자에도 응용할 수 있습니다. 예를 들어 전화번호나 사업자번호에서 하이픈을 빼고 숫자만 저장하고 싶을 때는 이렇게 씁니다.
update table set field = replace(field, '-', '');UPDATE 전에 꼭 확인할 것
WHERE 없는 UPDATE 는 테이블 전체를 바꿉니다. 저는 먼저 SELECT 로 어떤 행이 영향을 받는지, 바뀐 결과가 어떤지 확인하고 나서 UPDATE 를 돌립니다.
select field, replace(field, char(13), '') as after_value, length(field)
from table
where field like concat('%', char(13), '%');줄바꿈을 지우는 게 항상 정답은 아니라는 점도 기억해 두면 좋습니다. 게시글 본문이나 메모처럼 줄바꿈이 의미 있는 컬럼에서 CR/LF를 지우면 문단이 한 줄로 붙어 버립니다. 그런 컬럼은 지우는 대신 replace(field, '\r\n', '\n') 처럼 줄바꿈 형식만 통일하는 편이 낫습니다. 그리고 근본적으로는 입력 단계(PHP라면 trim(), VB.NET이라면 String.Trim())에서 정리해서 넣는 게 나중에 덜 고생합니다.
참고: 카레유 블로그