如何识别特殊空字符并通过Redshift SQL过滤含该字符的数据？

一、特殊字符是什么 #

你看到的红色空字符大概率是ASCII不可打印控制字符（比如ASCII 0的NULL字符、ASCII 127的删除符，或是其他非打印ASCII码字符）。这类字符在普通文本编辑器（如Mac自带文本工具）中不会显示，但VSCode会将其标记为红色占位符，它们会破坏CSV的格式解析逻辑，进而导致Tableau报错。

二、Redshift SQL匹配过滤方法 #

1. 定位字符的ASCII码 #

用以下SQL查询涉事字段的字符细节，确定具体的ASCII值：

-- 替换col_name为涉事字段名，your_table为表名
SELECT col_name,
       ASCII(SUBSTRING(col_name, POSITION(REGEXP_SUBSTR(col_name, '[^[:print:]]') IN col_name), 1)) AS char_ascii_code,
       OCTET_LENGTH(col_name) AS byte_length,
       CHAR_LENGTH(col_name) AS char_length
FROM your_table
WHERE col_name LIKE '%' || CHR(0) || '%' -- 先尝试匹配NULL字符，无效再换其他控制符
OR col_name LIKE '%' || CHR(127) || '%';

注：[^[:print:]]是正则表达式匹配所有非可打印字符，CHR(n)用于生成对应ASCII码的字符。

2. 过滤包含该字符的数据 #

如果确定了具体ASCII码（比如是0），直接执行过滤：

-- 删除包含目标字符的行
DELETE FROM your_table
WHERE col_name LIKE '%' || CHR(0) || '%';

-- 先查询验证结果
SELECT * FROM your_table
WHERE col_name LIKE '%' || CHR(0) || '%';

若不确定具体ASCII码，用正则匹配所有非可打印字符：

-- 查询包含非可打印字符的行
SELECT * FROM your_table
WHERE REGEXP_LIKE(col_name, '[^[:print:]]');

-- 删除包含非可打印字符的行
DELETE FROM your_table
WHERE REGEXP_LIKE(col_name, '[^[:print:]]');

3. 替换特殊字符（如需保留数据） #

若不想删除数据，可替换掉特殊字符：

UPDATE your_table
SET col_name = REGEXP_REPLACE(col_name, '[^[:print:]]', '')
WHERE REGEXP_LIKE(col_name, '[^[:print:]]');

内容的提问来源于stack exchange，提问作者Phenix