MSSQL 개행문자 조회 - MSSQL gaehaengmunja johoe

IT정보공유/DB

알지오™ 2017. 8. 7.

MSSQL Server Management Studio를 이용하여 데이터 조회를 한 후, 

결과를 csv 파일이나 엑셀로 저장을 한 후 

저장된 엑셀문서로 열었는데, 

웬걸, 필드값에 눈에는 보이지 않는 캐리지리턴(개행) 문자가 들어 있어

강제로 줄바꿈이 되더군요.

이럴 때 select문 자체에서 replace 함수를 이용해 개행문자를 제거해 줘야 합니다.

MSSQL 특수 문자 코드

Line Feed : char(10)

Carriage Return : char(13)

Tab : char(9)

특수문자를 공백으로 바꾸어 엑셀이나 csv저장시에 개행이 되지 않도록 하려면

replace 함수를 이용하여 Select Query에서 특수 문자 코드를 '' 공백으로 변경해 주면 됩니다.

replace(replace(필드명, char(13),''), char(10),'')

쉽지요?

태그

MSSQL, Replace, 엔터문자 제거, 캐리지리턴, 특수문자

볼 만한 글

댓글0

비밀글

mssql  줄바꿈 치환

 REPLACE(REPLACE(CONTENT, char(13) , '' ) ,  char(10), '' ) 

* 줄바꿈, 탭문자 제거 방법(text 타입도 포함)

- 변경 스트링 함수

replace([컬럼명], [변경할 문자], [변경될 문자])

- 엔터, 탭 문자 제거 함수 활용

Tab : char(9)

Line feed: char(10)

Carriage return: char(13)

>엔터 : replace(replace([컬럼명], char(13), ''), char(10), '')

>탭 : replace([컬럼명], char(9), '')

 텍스트 타입일 경우, [replace 함수의 인수 1에 대한 인수 데이터 형식 text이(가) 잘못되었습니다.] 에러를 확인할 수 있다.

=> replace(convert(varchar(max), [컬럼명]), [변경할 문자], [변경될 문자])

TEXT 타입 컬럼명 모두 치환

replace(replace(replace(convert(varchar(max),isnull([컬럼명], '-')), char(9), ' '),char(10), ' '), char(13), '') AS [컬럼명]

출처: https://ngio.co.kr/3253

문제: DB 데이터를 추출해 낼때 줄바꿈(엔터키)이 있을경우 데이터 이동시(ex: 엑셀) 줄바꾼채로 데이터가 행이 밀려버리는 현상이 발생.

해결: 이를 위해 데이터 추출해 낼때 줄바꿈을 제거하는 쿼리를 날리도록 함.

- 변경 스트링 함수

replace([컬럼명], [변경할 문자], [변경될 문자])

- 엔터, 탭 문자 제거 함수 활용

Tab: char(9)

Line feed: char(10)

Carriage return: char(13)

EX)

엔터: replace(replace([컬럼명], char(13), ''), char(10), '');

탭: replace([컬럼명], char(9), '')

텍스트 타입일 경우, [replace 함수의 인수 1에 대한 인수 데이터 형식 text이(가)잘못되었습니다.] 라는 에러가 확인되는데

아래와 같이 vachar등으로 컨버팅을 해야 한다. 

replace(convert(varchar(max), [컬럼명]), [변경할 문자], [변경될 문자])

출처: http://ggachi.ncity.net/TIP/7712

MSSQL에서 SMS발송업무등 부득히 문장을 줄바꿈해야하는 일이 발생한다.
이런 이슈에 아래와 같이 처리를 한다.

<해결방법: char(13),char(10)>

Select Concat('MSSQL띄어쓰기 테스트합니다.',char(13),char(10),' 안녕하세요. Tistory여러분!!') As '줄바꿈'

DB에서 정보를 검색해야 되는데 이상하게 검색이 안된다..

DB가 char(n) 타입이라 데이터 뒤에 공백이 붙는다.

예를들어 'aaaa' 라는 데이터를 넣으면 'aaaa           ' 라는 형식으로 입력이 된다.(char 니까 당연...)

하지만 검색할때는 'aaaa', 'aaaa ', 'aaaa     '... 다 되는게 정상이다...

그런데 이상하게 검색이 안되는 넘이 있었다...

문제의 발단은 txt 파일에서 정보를 읽어서 자동으로 인서트 시키는 기능이 있었는데 이 txt 파일에 aaaa 뒤에 탭 문자가 들어가 있었다.

실제로 메모장으로 열어서 커서를 이동해 보면 탭 문자 간격만큼 이동한다..

이거 때문에 삽질 하다가 수정한 결과를 적어 본다.

우선 MSSQL 에서 Tab 문자는  CHAR(9) 다.

줄바꿈 문자는 CHAR(10) 혹은 CHAR(10)+CHAR(13) ...

UPDATE [TABLE] SET value = REPLACE(value, CHAR(9), '')   하면 해결..

여기서CHAR(9) 는 문자열이 아니므로 'CHAR(9)' 로 적으면 안댄다.

어쨋든 이렇게 한건 해결...

MSSQL에서 데이터를 뽑아서 엑셀이나 CSV 등에 옮길 때 엔터나 탭으로 인해 파일 형식이 깨지는 경우가 많다.

왜냐하면 tab이나 enter가 구분자 역할을 하기 때문이다.

따라서 엑셀에 담기 전에 탭과 엔터를 REPLACE로 제거해주면 된다.

엔터: REPLACE(REPLACE(컬럼명, CHAR(13), ''), CHAR(10), '');

탭: REPLACE(컬럼명, CHAR(9), '')

\t: char(9)

\n: char(10)

\r: char(13)

\t는 탭이기 때문에 char(9)를 찾아서 REPLACE를 해주면 된다.

\n, \r은 엔터이기 때문에 char(10)과 char(13)을 찾아서 REPLACE를 해주면 된다.