[MySQL] 테이블 필드의 공백, 개행문자, 캐리지리턴 등 제거(하이픈 제거 포함)

[MySQL] 테이블 필드의 공백, 개행문자, 캐리지리턴 제거

- 문제상황 -

csv파일을 MySQL 테이블에 밀어 넣고, select를 해본다.

shopname 필드로 조회를 하면 정상적으로 조회가 되지만,

tel필드로 조회를 해보면 조회가 되지 않는다.

이런 경우 문제는 대부분 특수문자 혹은 보이지 않는 공백이 삽입된 경우다.

그래서 아래와 같이 like를 활용하여 다시 한번 조회해보았다.

조회가 성공적으로 되었다.

문제가 명확해졌다.

뒤에 뭔가 있는 것이다.(확인하는 방법이 따로 있긴 하지만 귀찮다. 그냥 처리한다)

- 해결책 - 

그럼 이제 공백, 탭, 라인피드, 캐리지리턴 등을 제거하는 법을 알아보자.

필드값 변경 함수

replace(필드명, 변경할 문자, 변경될 문자)

- '필드명'에 해당하는 레코드에서 '변경할 문자'를 모두 '변경될 문자'로 변경한다.

1. 공백제거(앞/뒤의 공백을 제거한다)

- update table set field = replace(field, ' ', '');

* table은 테이블명, field는 필드명이다. 

2. 개행문자 제거

- update table set field = replace(field, '\r\n', '');

3. 탭( Tab - char(9) ) 제거

- update table set field = replace(field, char(9), '');

- field 값에 탭(char(9))이 있다면, 없애서('') 레코드 값을 수정하라.

4. 라인피드(Line Feed) 제거

- update table set field = replace(field, char(10), '');

- field 값에 라인피드(char(10))이 있다면, 없애서('') 레코드 값을 수정하라.

5. 캐리지리턴(Carriage Return) 제거

- update table set field = replace(field, char(13), '');

- field 값에 캐리지리턴(char(13))이 있다면, 없애서('') 레코드 값을 수정하라.

이쯤 되면 응용도 가능하다. 

6. 하이픈( - ) 제거

- update table set field = replace(field, '-', '');

직접 캐리지 리턴 제거를 수행해보았다.

정상적으로 조회가 된다! 

공백 제거의 경우 trim(), ltrim(), rtrim()함수를 사용할 수도 있다.

- trim()함수를 사용하는 법 : update table set field = trim(field);

이렇게 해도 안 된다면 

직접 생각해보고 구글링을 해보면서 찾아보시면 좋을 것 같습니다.

감사합니다.