본문 바로가기
Database/Common

[DataBase] 보이지않는 비표시 문자와 SQL별 대처법

by hwangmono__o 2024. 7. 1.

제로 너비 공간이란?

제로 너비 공간(ZWSP)은 텍스트에서 눈에 보이지 않는 공백으로, 그 자체는 아무런 너비도 가지지 않지만 텍스트의 형식을 제어하는 데 사용됩니다.
이 문자들은 텍스트의 가독성을 해치지 않으면서도 데이터 처리 과정에서 문제를 일으킬 수 있습니다.
특히 SQL 데이터베이스에서는 데이터 무결성을 저해하거나 예상치 못한 결과를 초래할 수 있습니다.

주요 제로 너비 문자 종류 및 설명

제로 너비 공간 (Zero Width Space, ZWSP)  - U+200B
설명: 두 글자 사이에 보이지 않는 간격을 추가합니다. 줄 바꿈 지점을 표시하거나 텍스트를 구분하는 데 사용됩니다.
용도: 줄 바꿈 허용 지점 표시, 언어별 단어 분리
제로 너비 비조인자 (Zero Width Non-Joiner, ZWNJ)  - U+200C
설명: 아랍어나 힌디어와 같은 언어에서 두 문자가 결합되는 것을 방지합니다. 결합되지 않는 상태로 두 문자를 표시합니다.
용도: 문자 결합 방지, 특정 글자 형태 유지
제로 너비 조인자 (Zero Width Joiner, ZWJ) - U+200D
설명: 아랍어, 힌디어 등에서 두 문자가 결합되는 것을 촉진합니다. 결합된 형태로 두 문자를 표시합니다.
용도: 문자 결합 촉진, 이모지 결합
제로 너비 비브레이크 공간 (Zero Width No-Break Space, ZWNBSP)  - U+FEFF
설명: 줄 바꿈을 방지하는 보이지 않는 간격입니다. 보통 BOM(Byte Order Mark)로 사용되며, 파일의 바이트 순서를 표시합니다.
용도: 줄 바꿈 방지, 파일의 바이트 순서 표시
왼쪽에서 오른쪽 표시 (Left-to-Right Mark, LRM)  - U+200E
설명: 텍스트의 방향을 좌에서 우로 강제합니다.
용도: 텍스트 방향 제어
오른쪽에서 왼쪽 표시 (Right-to-Left Mark, RLM)  - U+200F
설명: 텍스트의 방향을 우에서 좌로 강제합니다.
용도: 텍스트 방향 제어
단어 결합자 (Word Joiner, WJ)  - U+2060
설명: 줄 바꿈 없이 단어를 연결합니다.
용도: 줄 바꿈 방지
보이지 않는 구분자 (Invisible Separator)  - U+2063
설명: 보이지 않는 구분자로 사용됩니다.
용도: 논리적 구분
소프트 하이픈 (Soft Hyphen, SHY)  - U+00AD
설명: 줄 바꿈 시 하이픈을 표시하지만, 줄 바꿈이 없을 경우 보이지 않습니다.
용도: 하이픈 표시 제어

제로 너비 문자의 문제점

데이터 무결성 문제: 제로 너비 문자는 보이지 않기 때문에 데이터 입력 시 실수로 포함될 수 있으며, 이는 데이터 무결성에 영향을 줄 수 있습니다.
검색 및 매칭 문제: 검색어에 제로 너비 문자가 포함되면 일치하는 결과가 나오지 않을 수 있습니다.
데이터 처리 문제: 데이터 전처리 과정에서 예상치 못한 동작을 일으킬 수 있습니다.

SQL별 제로 너비 문자 대처법

1.  MySQL

 REPLACE 함수와 정규 표현식을 사용할 수 있습니다.

SELECT REPLACE(REPLACE(REPLACE(REPLACE(column_name, CHAR(8203), ''), CHAR(8204), ''), CHAR(8205), ''), CHAR(65279), '') AS cleaned_column
FROM table_name;

위 예제에서는 주요 제로 너비 문자(Zero Width Space, Zero Width Non-Joiner, Zero Width Joiner, Zero Width No-Break Space)를 제거하는 방법을 보여줍니다.

 

2. PostgreSQL

regexp_replace 함수를 사용하여 제로 너비 문자를 제거할 수 있습니다.

SELECT regexp_replace(column_name, '[\u200B\u200C\u200D\uFEFF]', '', 'g') AS cleaned_column
FROM table_name;

이 예제에서는 유니코드 정규 표현식을 사용하여 제로 너비 문자를 찾아 제거합니다.

 

3. SQL Server

REPLACE 함수를 중첩하여 제로 너비 문자를 제거할 수 있습니다.

SELECT REPLACE(REPLACE(REPLACE(REPLACE(column_name, NCHAR(8203), ''), NCHAR(8204), ''), NCHAR(8205), ''), NCHAR(65279), '') AS cleaned_column
FROM table_name;

위 예제에서는 NCHAR 함수를 사용하여 제로 너비 문자를 제거합니다.

 

4. Oracle

REGEXP_REPLACE 함수를 사용하여 제로 너비 문자를 제거할 수 있습니다.

SELECT REGEXP_REPLACE(column_name, '[\u200B\u200C\u200D\uFEFF]', '') AS cleaned_column
FROM table_name;

이 예제에서는 유니코드 정규 표현식을 사용하여 제로 너비 문자를 찾아 제거합니다.

SQL 테스트

CREATE TABLE UNI_TEXT_TEST (
  ID NUMERIC,
    UNI_TEXT_FIELD VARCHAR(4000),
    TEXT_FIELD VARCHAR(4000)
);
-- POSTGRESQL
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (1, E'다른게뭘까\u200B', '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (2, E'다른게\u200B뭘까', '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (3, E'\u200B다른게뭘까', '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (4, E'다른게뭘까\u200C', '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (5, E'다른게\u200C뭘까', '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (6, E'\u200C다른게뭘까', '다른게뭘까');

-- ORACLE
INSERT INTO TEST04.UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (1, '다른게뭘까'||UNISTR('\200B'), '다른게뭘까');
INSERT INTO TEST04.UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (2, '다른게'||UNISTR('\200B')||'뭘까', '다른게뭘까');
INSERT INTO TEST04.UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (3, UNISTR('\200B')||'다른게뭘까', '다른게뭘까');
INSERT INTO TEST04.UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (4, '다른게뭘까'||UNISTR('\200C'), '다른게뭘까');
INSERT INTO TEST04.UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (5, '다른게'||UNISTR('\200C')||'뭘까', '다른게뭘까');
INSERT INTO TEST04.UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (6, UNISTR('\200C')||'다른게뭘까', '다른게뭘까');

-- POSTGRESQL OR ORACLE
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (1, '다른게뭘까' || chr(8203), '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (2, '다른게' || chr(8203) || '뭘까', '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (3, chr(8203) || '다른게뭘까', '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (4, '다른게뭘까' || chr(8204), '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (5, '다른게' || chr(8204) || '뭘까', '다른게뭘까');
INSERT INTO UNI_TEXT_TEST (ID, UNI_TEXT_FIELD, TEXT_FIELD) VALUES (6, chr(8204) || '다른게뭘까', '다른게뭘까');
select * from UNI_TEXT_TEST;

 

 

TEXT와 REPLACE 한 값 비교 - 1

WITH RECURSIVE T AS (
    SELECT
    	ID,
        UNI_TEXT_FIELD,
        TEXT_FIELD,
        LENGTH(UNI_TEXT_FIELD) AS UNI_TEXT_FIELD_LENGTH,
        LENGTH(TEXT_FIELD) AS TEXT_FIELD_LENGTH,
        1 AS LEVEL,
        ASCII(SUBSTRING(UNI_TEXT_FIELD FROM 1 FOR 1))::TEXT AS UNI_TEXT_FIELD_ASCII,
        ASCII(SUBSTRING(TEXT_FIELD FROM 1 FOR 1))::TEXT AS TEXT_FIELD_ASCII
    FROM
        UNI_TEXT_TEST
    UNION ALL
    SELECT
    	ID,
        UNI_TEXT_FIELD,
        TEXT_FIELD,
        UNI_TEXT_FIELD_LENGTH,
        TEXT_FIELD_LENGTH,
        LEVEL + 1,
        UNI_TEXT_FIELD_ASCII || ' ' || ASCII(SUBSTRING(UNI_TEXT_FIELD FROM LEVEL + 1 FOR 1))::TEXT,
        TEXT_FIELD_ASCII || ' ' || ASCII(SUBSTRING(TEXT_FIELD FROM LEVEL + 1 FOR 1))::TEXT
    FROM
        T
    WHERE
        LEVEL < GREATEST(LENGTH(UNI_TEXT_FIELD), LENGTH(TEXT_FIELD))
)
SELECT
	ID,
    UNI_TEXT_FIELD,
    TEXT_FIELD,
    UNI_TEXT_FIELD_LENGTH,
    TEXT_FIELD_LENGTH,
    MAX(UNI_TEXT_FIELD_ASCII) AS UNI_TEXT_FIELD_ASCII,
    MAX(TEXT_FIELD_ASCII) AS TEXT_FIELD_ASCII,
    CASE
        WHEN UNI_TEXT_FIELD = TEXT_FIELD THEN 'EQUAL'
        ELSE 'NOT EQUAL'
    END AS COMPARISON_RESULT,
    CASE
        WHEN REGEXP_REPLACE(UNI_TEXT_FIELD, CHR(8203) || '|' || CHR(8204), '', 'g') = TEXT_FIELD THEN 'EQUAL'
        ELSE 'NOT EQUAL'
    END AS CLEANED_COMPARISON_RESULT
FROM
    T
GROUP BY
	ID,
    UNI_TEXT_FIELD,
    TEXT_FIELD,
    UNI_TEXT_FIELD_LENGTH,
    TEXT_FIELD_LENGTH
   ORDER BY ID;

 

TEXT와 REPLACE 한 값 비교 - 2

-- id가 1,2인 데이터 단순 값 비교시 결과 : Y(같지않다) 
select CASE WHEN (select uni_text_field from UNI_TEXT_TEST where id=1) != (select uni_text_field from UNI_TEXT_TEST where id=2) THEN 'Y' ELSE 'N' END AS TEST
from UNI_TEXT_TEST;

-- id가 1,2인 데이터 유니코드 replace 후 값 비교시 결과 : N(같다) 
SELECT CASE WHEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
           (SELECT uni_text_field FROM UNI_TEXT_TEST WHERE id = 1),
           E'\u200B', ''),  -- Zero Width Space
           E'\u200C', ''),  -- Zero Width Non-Joiner
           E'\u200D', ''),  -- Zero Width Joiner
           E'\u00A0', ''),  -- No-Break Space
           E'\u200E', ''),  -- Left-To-Right Mark
           E'\u200F', ''),  -- Right-To-Left Mark
           E'\uFEFF', ''),  -- Zero Width No-Break Space
           E'\u2063', '')   -- Invisible Separator
       !=
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
           (SELECT uni_text_field FROM UNI_TEXT_TEST WHERE id = 2),
           E'\u200B', ''),  -- Zero Width Space
           E'\u200C', ''),  -- Zero Width Non-Joiner
           E'\u200D', ''),  -- Zero Width Joiner
           E'\u00A0', ''),  -- No-Break Space
           E'\u200E', ''),  -- Left-To-Right Mark
           E'\u200F', ''),  -- Right-To-Left Mark
           E'\uFEFF', ''),  -- Zero Width No-Break Space
           E'\u2063', '')   -- Invisible Separator
      THEN 'Y' ELSE 'N' END AS TEST
FROM UNI_TEXT_TEST;

 

티스토리툴바