从‘Hello World’到数据清洗：用PostgreSQL字符串函数搞定脏数据（附正则表达式秘籍）
当你第一次在PostgreSQL里写下SELECT 'Hello World'时，大概不会想到有朝一日要用它来处理"张*三,138-xxxx|李四(无效数据)"这样的字符串。现实世界的数据就像刚搬完家的衣柜——你永远不知道会翻出什么奇怪的东西。本文将带你超越基础语法，用PostgreSQL的字符串函数驯服这些"数据野兽"。
1. 为什么SQL工程师需要字符串处理武器库
在理想国度的教科书里，数据总是整齐地躺在规范的字段中。但真实世界的业务系统会给你这些惊喜：
- 客户姓名和电话被塞在同一个字段："王五#1581234XXXX"
- 地址信息自由发挥："北京市海淀区/中关村南大街5号（临时仓库）"
- 日志文件里的错误信息："ERR2023-12:磁盘空间不足|WARN:内存使用超阈值"
脏数据的三大原罪：
- 分隔符混乱： CSV文件用逗号分隔，但数据本身包含逗号
- 结构嵌套： JSON字符串被存为普通文本字段
- 人工输入随意性： "北京" vs "北京市" vs "Beijing"
提示：数据清洗黄金法则——永远不要相信原始数据的格式一致性
2. 基础拆解：SPLIT_PART的实战技巧
SPLIT_PART是处理固定分隔符场景的瑞士军刀。看这个混乱的订单数据示例：
SELECT order_id, SPLIT_PART(customer_info, '|', 1) AS customer_name, SPLIT_PART(SPLIT_PART(customer_info, '|', 2), '-', 2) AS phone_tail FROM ( VALUES (1, '张三|手机-13800138000'), (2, '李四|座机-010-87654321'), (3, '王五|无效数据') ) AS orders(order_id, customer_info);进阶技巧：
处理可能缺失的字段：
COALESCE(SPLIT_PART(text, ',', 3), 'N/A')安全获取最后一个元素：
WITH split_count AS ( SELECT array_length(string_to_array(text, ','), 1) AS cnt FROM table ) SELECT SPLIT_PART(text, ',', cnt) FROM table, split_count;
3. 数组化处理：STRING_TO_ARRAY的高阶玩法
当数据像俄罗斯套娃一样多层嵌套时，STRING_TO_ARRAY配合UNNEST能创造奇迹：
-- 处理多值标签字段 SELECT product_id, trim(tag) AS clean_tag FROM products, LATERAL UNNEST(STRING_TO_ARRAY(tags, ';')) AS tag WHERE tags LIKE '%促销%';典型应用场景对比：
| 场景 | 推荐函数 | 示例 |
|---|---|---|
| 固定位置提取 | SPLIT_PART | 截取URL中的域名部分 |
| 多值标签统计 | STRING_TO_ARRAY | 分析用户兴趣标签分布 |
| 非结构化日志分析 | REGEXP_SPLIT_TO_ARRAY | 从日志提取错误代码和时间戳 |
4. 正则表达式：数据清洗的终极武器
面对最顽固的脏数据，正则表达式是你的特种部队。以下是经过实战检验的模式库：
常用正则模式速查表：
-- 提取中文姓名 SELECT regexp_matches('联系人：张三丰（经理）', '([\u4e00-\u9fa5]{2,4})', 'g'); -- 标准化日期格式 SELECT regexp_replace('2023年12月31日', '(\d{4})年(\d{1,2})月(\d{1,2})日', '\1-\2-\3'); -- 清理HTML标签 UPDATE articles SET content = regexp_replace(content, '<[^>]+>', '', 'g');性能优化技巧：
- 简单模式用
~操作符比正则函数快3-5倍 - 对千万级数据使用
WHERE text ~ 'pattern'先过滤 - 预编译正则表达式：
CREATE FUNCTION clean_phone(text) RETURNS text AS $$...$$
5. 综合实战：从混乱到规范的完整案例
假设我们要处理这样的原始客户数据：
"ID:1001 | 姓名: 张*三 | 联系方式: 138-1234-5678（微信同号） | 地址: 北京市海淀区/中关村"分步清洗方案：
WITH raw_data AS ( SELECT 'ID:1001 | 姓名: 张*三 | 联系方式: 138-1234-5678（微信同号） | 地址: 北京市海淀区/中关村' AS customer_info ), step1 AS ( SELECT regexp_matches(customer_info, 'ID:(\d+)', 'g')[1] AS customer_id, regexp_replace(regexp_matches(customer_info, '姓名: ([^|]+)', 'g')[1], '\*', '') AS customer_name, regexp_matches(customer_info, '(\d{3}-\d{4}-\d{4})', 'g')[1] AS phone, regexp_replace(regexp_matches(customer_info, '地址: ([^|]+)', 'g')[1], '/', '') AS address FROM raw_data ) SELECT customer_id, customer_name, regexp_replace(phone, '-', '') AS clean_phone, CASE WHEN address ~ '海淀区' THEN '海淀区' ELSE '其他区域' END AS district FROM step1;6. 避坑指南：那些年我踩过的字符串处理坑
- 编码陷阱：处理中文时确保数据库编码为UTF-8，否则
length('中文')可能返回错误值 - 性能黑洞：在大表上直接使用正则函数，建议先提取到临时表
- 隐式转换：
SPLIT_PART返回的是text类型，数学计算前记得类型转换 - 空格幽灵：
trim()函数是你的好朋友，特别是处理人工输入数据时
有一次我花了三小时debug，最终发现是因为数据里藏了个零宽度空格（\u200b）。现在我的清洗流程总会先跑一遍：
UPDATE table SET text = regexp_replace(text, '[\u200b\u00a0]', '', 'g');
