[SQL] DB 데이터 추출 시 행 밀림 문제 해결하기

문제 상황

DB에서 엑셀 등으로 데이터를 추출할 때, 데이터에 줄바꿈 등 특수 문자 코드가 있다면 데이터 이동 시 그대로 적용되어 행이 밀려버리는 현상이 발생합니다.

해결 방법

이 문제를 해결하기 위해 데이터 추출 시 특수 문자 코드를 제거하는 SQL 쿼리를 사용할 수 있습니다.

기본 문법: REPLACE 함수

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

특수 문자 코드

줄바꿈과 탭 문자를 제거하기 위해 알아야 할 특수 문자 코드는 다음과 같습니다:

특수 문자	코드
Tab	CHAR(9)
Line feed	CHAR(10)
Carriage return	CHAR(13)

실제 사용 예시

엔터(줄바꿈) 제거하기

-- CR(Carriage Return)과 LF(Line Feed) 모두 제거
SELECT REPLACE(REPLACE([컬럼명], CHAR(13), ''), CHAR(10), '') FROM 테이블명;

탭 제거하기

SELECT REPLACE([컬럼명], CHAR(9), '') FROM 테이블명;

텍스트 타입 데이터 처리 주의사항

텍스트(TEXT) 타입의 데이터에 REPLACE 함수를 직접 적용하면 replace 함수의 인수 1에 대한 인수 데이터 형식 text이(가) 잘못되었습니다.라는 오류가 발생할 수 있습니다.

텍스트 타입 데이터 처리 방법

TEXT 타입 데이터를 처리하기 위해서는 VARCHAR와 같은 다른 타입으로 변환 후 REPLACE 함수를 적용해야 합니다.

SELECT REPLACE(CONVERT(VARCHAR(MAX), [텍스트컬럼명]), CHAR(13), '') FROM 테이블명;

(참고) 데이터베이스별 타입 변환 문법

데이터베이스	변환 문법	예시 (TEXT → VARCHAR)
MS SQL Server	CONVERT(타입, 값) CAST(값 AS 타입)	CONVERT(VARCHAR(MAX), text_column) CAST(text_column AS VARCHAR(MAX))
MySQL/MariaDB	CAST(값 AS 타입)	CAST(text_column AS CHAR(255))
Oracle	TO_CHAR(값) CAST(값 AS 타입)	TO_CHAR(clob_column) CAST(clob_column AS VARCHAR2(4000))
PostgreSQL	값::타입 CAST(값 AS 타입)	text_column::VARCHAR CAST(text_column AS VARCHAR)
SQLite	CAST(값 AS 타입)	CAST(text_column AS VARCHAR)
IBM DB2	CAST(값 AS 타입) 타입(값)	CAST(clob_column AS VARCHAR(32000)) VARCHAR(clob_column, 32000)

통합 사용 예시

SELECT 
    일반컬럼,
    REPLACE(REPLACE([일반컬럼], CHAR(13), ''), CHAR(10), '') AS 줄바꿈제거_일반컬럼,
    REPLACE(REPLACE(CONVERT(VARCHAR(MAX), [텍스트컬럼]), CHAR(13), ''), CHAR(10), '') AS 줄바꿈제거_텍스트컬럼
FROM 테이블명;

정리

REPLACE 함수를 사용하여 특수 문자 코드를 제거함으로써 데이터 이동 시 발생할 수 있는 행 밀림 현상을 방지할 수 있습니다.
텍스트 타입 데이터의 경우 CONVERT 함수를 통해 VARCHAR 타입으로 변환한 후 REPLACE 함수를 적용해야 합니다.