[MSSQL] Excel로 데이터 추출 전 문자열 REPLACE 처리하기 (탭, 줄바꿈, 콤마, 쌍따옴표)

이 글은 17만 건의 데이터 추출 요청을 받았다가 데이터의 다양함에 와장창 깨지는 EXCEL 파일을 보고 Sql 수정 노가다하다 열 받아서 기록하는 포스트입니다,,, 

 

DBMS	MSSQL
Tool	Microsoft SQL Server Management Studio (SSMS)

 

SQL Server Management Studio에서 데이터 조회한 결과에서 다음과 같은 처리를 할 때

1. 복사(Y) or 복사(머리글 포함)
2. 다른 이름으로 결과 저장(V)... > CSV(쉼표로 구분) (*.csv)

1번에서 복사한 결과를 Excel에 붙여 넣기 하거나 2번에서 csv파일로 직접 저장시키는 경우 모두 동일하게 컬럼 내에 다음과 같은 문자열이 포함되면 Excel에서 행과 열이 무자비하게 깨지게 됩니다.

 

Char(ASCII CODE)	Symbol	Description (En)	Description (Ko)
Char(9)	HT	Horizontal Tab (\t)	탭
Char(10)	LF	Line Feed (\n)	줄바꿈
Char(13)	CR	Carriage Return (\r)	맨 앞으로 이동
Char(34)	"	Double quote	쌍따옴표, 큰따옴표
Char(44)	,	Comma	콤마

17 만행에 33 열인데 위에 문자열이 포함되면 행바꿈, 열 바꿈 등이 일어나 엑셀이 깨진단 걸 깨닫고 아예 REPLACE처리 해버리기로 결심! REPLACE 함수는 다음과 같이 간단한 형식으로 사용 가능합니다.

REPLACE([컬럼명], [대상 문자열], [대체할 문자열])

그래서 예를 들어 탭, 줄 바꿈, 큰따옴표, 콤마를 빈칸으로 치환하고자 한다 치면

REPLACE([컬럼명], Char(9), ' ')
REPLACE(REPLACE([컬럼명], Char(10), ' '), CHAR(13), ' ')
REPLACE([컬럼명], Char(34), ' ')
REPLACE([컬럼명], Char(44), ' ')

위와 같이 사용하면 되는데 아스키코드 표현 대신 직접 문자열을 사용해도 무방합니다. (예) Char(44) → ',')

물론 다음과 같이 중첩해서 사용할 수도 있습니다. 중첩문은 DB에 부하가 가지 않게 데이터량에 맞춰 사용하세요.

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([컬럼명], Char(9), ' '), CHAR(10), ' '), Char(13), ' '), Char(34), ' '), Char(44), ' ')

위의 문자열 대신 다른 문자열을 치환하고 싶은 경우는 ASCII CODE 사이트를 참고하세요.