DuckDB 字符串函数全家桶——数据清洗不再痛苦
你有没有遇到过这种数据？
- 订单号格式混乱：‘ORD-2026-001’、‘ord_001’、‘ORD2026001’
- 用户姓名有空格：’ 张三 ‘、‘李四 ‘、’ 王五’
- 手机号被包裹：’(010)12345678’、’+86-13800138000’
- 地址包含多余信息：‘北京市海淀区中关村大街1号 3号楼’
- 邮箱大小写混乱：‘[email protected]’、’[email protected]’
用 Python 写正则？写一天都写不完。用 SQL 的 CASE WHEN？代码比数据还长。
今天讲 DuckDB 的字符串函数全家桶，20 个常用函数，覆盖 90% 的数据清洗场景。
一、基础截取与拼接
STRPOS / POSITION —— 找子串位置
SELECT
'ORD-2026-001' AS order_id,
STRPOS('ORD-2026-001', '-') AS first_dash, -- 3
STRPOS('ORD-2026-001', '-', 4) AS second_dash; -- 8
关键点：STRPOS(str, substr) 返回子串第一次出现的位置，从 1 开始计数。第二个参数可以指定起始位置。
SUBSTRING / SUBSTR —— 截取子串
SELECT
'ORD-2026-001' AS order_id,
SUBSTRING('ORD-2026-001' FROM 5 FOR 4) AS year, -- '2026'
SUBSTRING('ORD-2026-001' FROM 8) AS num; -- '001'
关键点：DuckDB 支持 SQL 标准语法 SUBSTRING(str FROM start FOR length)，也支持简写 SUBSTRING(str FROM start)。
LEFT / RIGHT —— 从两端截取
SELECT
LEFT('2026-08-14 10:30:00', 10) AS date_part, -- '2026-08-14'
RIGHT('2026-08-14 10:30:00', 8) AS time_part; -- '10:30:00'
CONCAT / || —— 拼接字符串
SELECT
CONCAT('hello', ' ', 'world') AS result1, -- 'hello world'
'hello' || ' ' || 'world' AS result2; -- 'hello world'（推荐）
关键点：|| 是 SQL 标准拼接符，比 CONCAT 更通用。DuckDB 中 || 遇到 NULL 返回 NULL，可以用 CONCAT 自动跳过 NULL。
CONCAT_WS —— 带分隔符拼接
SELECT
CONCAT_WS('-', '2026', '08', '14') AS date_str, -- '2026-08-14'
CONCAT_WS(',', '张三', '男', '北京') AS info; -- '张三,男,北京'
关键点：CONCAT_WS(separator, ...) 第一个参数是分隔符，自动跳过 NULL 值。
二、大小写与格式化处理
UPPER / LOWER / INITCAP —— 大小写控制
SELECT
UPPER('[email protected]') AS upper_email, -- '[email protected]'
LOWER('[email protected]') AS lower_email, -- '[email protected]'
INITCAP('hello world') AS capitalized; -- 'Hello World'
关键点：INITCAP 把每个单词的首字母大写，非常适合处理姓名、标题。
LPAD / RPAD —— 补零对齐
SELECT
LPAD('1', 5, '0') AS padded_left, -- '00001'
RPAD('1', 5, '0') AS padded_right; -- '10000'
关键点：LPAD(str, length, pad) 在左侧填充，RPAD 在右侧填充。常用于生成固定长度的编码。
TRIM / LTRIM / RTRIM —— 去空格
SELECT
TRIM(' 张三 ') AS trimmed, -- '张三'
LTRIM(' 张三 ') AS left_trim, -- '张三 '
RTRIM(' 张三 ') AS right_trim; -- ' 张三'
关键点：TRIM 默认去两端空格，也可以用 TRIM(LEADING/RTRAILING/BOTH) 控制方向。
三、替换与清理
REPLACE —— 全局替换
SELECT
REPLACE('ORD-2026-001', '-', '') AS no_dash, -- 'ORD2026001'
REPLACE('2026-08-14', '-', '/') AS changed; -- '2026/08/14'
REGEXP_REPLACE —— 正则替换（杀手锏）
SELECT
REGEXP_REPLACE('(010)12345678', '[^0-9]', '') AS clean_phone, -- '01012345678'
REGEXP_REPLACE('hello123world456', '[0-9]', '-') AS no_digits; -- 'hello-world-'
关键点：REGEXP_REPLACE(str, pattern, replacement) 用正则表达式匹配并替换，是处理非标准格式数据的终极武器。
四、分割与提取
SPLIT_PART —— 按分隔符取第 N 段
SELECT
order_id,
SPLIT_PART(order_id, '-', 1) AS prefix, -- 'ORD'
SPLIT_PART(order_id, '-', 2) AS year, -- '2026'
SPLIT_PART(order_id, '-', 3) AS num; -- '001'
FROM (VALUES
('ORD-2026-001'),
('ORD-2026-002'),
('ORD-2025-100')
) AS t(order_id);
关键点：SPLIT_PART(str, delimiter, field_num) 是 DuckDB 最常用的字符串分割函数，比 Python 的 split()[n] 简洁得多。
REGEXP_SPLIT_TO_TABLE —— 正则分割成行
SELECT * FROM REGEXP_SPLIT_TO_TABLE('Python,SQL,DuckDB', ',');
结果：
regex_split_to_table
---------------------
Python
SQL
DuckDB
REGEXP_EXTRACT —— 正则提取
SELECT
REGEXP_EXTRACT('ORD-2026-001', '(\\d{4})') AS year, -- '2026'
REGEXP_EXTRACT('ORD-2026-001', '(\\d{3})$') AS num; -- '001'
关键点：REGEXP_EXTRACT(str, pattern) 提取第一个匹配，配合 COALESCE 提供默认值。
五、长度与查找
LENGTH / CHAR_LENGTH —— 字符串长度
SELECT
LENGTH('DuckDB') AS byte_len, -- 6
LENGTH('张三') AS cn_byte_len; -- 6（UTF-8 中文占 3 字节）
SELECT
CHAR_LENGTH('DuckDB') AS char_len, -- 6
CHAR_LENGTH('张三') AS cn_char_len; -- 2（按字符数）
关键点：LENGTH 按字节计算，CHAR_LENGTH 按字符计算。处理中文数据时用 CHAR_LENGTH。
CONTAINS / LIKE —— 匹配检查
SELECT
'DuckDB' LIKE 'Duck%' AS starts_with_duck, -- true
'DuckDB' LIKE '%DB' AS ends_with_db, -- true
'DuckDB' LIKE '%uck%' AS contains_uck; -- true
-- 更推荐的写法：CONTAINS（DuckDB 特有）
SELECT
CONTAINS('DuckDB', 'uck') AS has_uck; -- true
关键点：CONTAINS(str, substring) 是 DuckDB 的专用函数，比 LIKE '%xxx%' 更直观。
六、实战案例
案例一：电话号码清洗
真实场景：客户表里的手机号格式混乱，需要统一清洗。
CREATE TABLE raw_customers AS
SELECT * FROM VALUES
('Alice', '(010)12345678'),
('Bob', '+86-13800138000'),
('Charlie', '13800138000'),
('David', '138 0013 8000'),
('Eve', '13800138000 ext. 123')
AS t(name, phone_raw);
-- 清洗 SQL：去掉所有非数字字符
SELECT
name,
phone_raw AS original,
REGEXP_REPLACE(phone_raw, '[^0-9]', '') AS phone_clean
FROM raw_customers;
结果：
name | original | phone_clean
--------|-------------------|-------------
Alice | (010)12345678 | 01012345678
Bob | +86-13800138000 | 8613800138000
Charlie | 13800138000 | 13800138000
David | 138 0013 8000 | 13800138000
Eve | 13800138000... | 13800138000
关键点：一行 REGEXP_REPLACE(phone, '[^0-9]', '') 搞定所有格式变体，比写 10 个 CASE WHEN 简洁 10 倍。
案例二：订单号标准化
CREATE TABLE orders AS
SELECT * FROM VALUES
('ORD-2026-001'),
('ord_001'),
('ORD2026001'),
('order-2026-002'),
('2026-003')
AS t(order_id_raw);
-- 标准化：统一格式为 'ORD-YYYY-NNN'
SELECT
order_id_raw,
REGEXP_EXTRACT(order_id_raw, '(\\d{4})') AS year,
REGEXP_EXTRACT(order_id_raw, '(\\d{3})$') AS num,
'ORD-'
|| COALESCE(REGEXP_EXTRACT(order_id_raw, '(\\d{4})'), '2026')
|| '-'
|| LPAD(COALESCE(REGEXP_EXTRACT(order_id_raw, '(\\d{3})$'), '001'), 3, '0')
AS order_id_std
FROM orders;
结果：
order_id_raw | year | num | order_id_std
----------------|-------|------|-------------
ORD-2026-001 | 2026 | 001 | ORD-2026-001
ord_001 | NULL | 001 | ORD-2026-001
ORD2026001 | 2026 | 001 | ORD-2026-001
order-2026-002 | 2026 | 002 | ORD-2026-002
2026-003 | 2026 | 003 | ORD-2026-003
七、与传统工具对比
| 操作 | Python + re | SQL CASE WHEN | DuckDB 字符串函数 |
|---|---|---|---|
| 去空格 | s.strip() | 多个 CASE | TRIM(s) |
| 转大写 | s.upper() | 多个 CASE | UPPER(s) |
| 正则替换 | re.sub() | 不支持 | REGEXP_REPLACE() |
| 分割取值 | s.split()[n] | 不支持 | SPLIT_PART(s, '-', 2) |
| 补零对齐 | 自定义函数 | 不支持 | LPAD(s, 5, '0') |
| 包含检查 | sub in s | 多个 CASE | CONTAINS(s, sub) |
结论：DuckDB 字符串函数让数据清洗从"写代码"变成"写 SQL"，一行搞定原来需要多行的逻辑。
八、函数速查表
| 函数 | 用途 | 示例 |
|---|---|---|
TRIM(str) | 去两端空格 | TRIM(' hello ') → 'hello' |
UPPER/LOWER | 大小写转换 | UPPER('abc') → 'ABC' |
SUBSTRING(str, n, m) | 截取子串 | SUBSTRING('hello', 2, 3) → 'ell' |
CONCAT_WS(sep, ...) | 带分隔符拼接 | CONCAT_WS('-', 2026, 8, 14) → '2026-8-14' |
SPLIT_PART(str, sep, n) | 按分隔符取第N段 | SPLIT_PART('a-b-c', '-', 2) → 'b' |
REPLACE(str, old, new) | 全局替换 | REPLACE('abc', 'b', 'x') → 'axc' |
REGEXP_REPLACE(str, pat, rep) | 正则替换 | REGEXP_REPLACE('a1b2', '[0-9]', '-') → 'a-b-' |
REGEXP_EXTRACT(str, pat) | 正则提取 | REGEXP_EXTRACT('abc123', '(\d+)') → '123' |
LPAD/RPAD(str, len, pad) | 补零对齐 | LPAD('5', 3, '0') → '005' |
LENGTH/CHAR_LENGTH | 长度（字节/字符） | CHAR_LENGTH('张三') → 2 |
CONTAINS(str, sub) | 包含检查 | CONTAINS('hello', 'ell') → true |
POSITION(sub IN str) | 查找位置 | POSITION('@' IN 'a@b') → 2 |
九、避坑指南
- NULL 传播：任何字符串函数遇到 NULL 输入返回 NULL，用
COALESCE(str, '')先处理 - 字节 vs 字符：
LENGTH按字节，CHAR_LENGTH按字符。中文数据务必用CHAR_LENGTH - REGEXP 性能：复杂正则在大表上较慢，先用
LIKE过滤再REGEXP_REPLACE - 空字符串 vs NULL：
TRIM('')返回空字符串''，不是 NULL。注意区分 - SPLIT_PART 越界：请求的段号超出实际段数时返回 NULL，不要假设一定存在
十、变现建议
1. 卖数据清洗服务
很多中小企业的数据"又脏又乱"，但又没有数据团队。你可以用 DuckDB 字符串函数快速清洗客户数据、销售数据，按项目收费 3000-10000 元。
2. 做数据清洗 SaaS
把常用的清洗逻辑（手机号标准化、地址清洗、订单号格式化）封装成 API，按调用次数收费。月入 2000-5000 元不难。
3. 卖自动化报表模板
把字符串函数 + MERGE 增量更新的组合做成"日报自动生成器"，面向电商、SaaS 公司销售，一次性收费 3000-8000 元。
4. 做 DuckDB 培训课程
字符串函数是 DuckDB 最实用的技能之一，可以做成系列课程，定价 99-299 元/人。
核心卖点：一行 SQL 搞定原来需要写一天 Python 的数据清洗工作。
总结
DuckDB 字符串函数覆盖数据清洗的 90% 场景：
- 截取拼接：
SUBSTRING、CONCAT_WS、LPAD - 格式化处理：
TRIM、UPPER/LOWER、INITCAP - 替换清理：
REPLACE、REGEXP_REPLACE - 分割提取：
SPLIT_PART、REGEXP_EXTRACT
记住这个心法：标准格式用内置函数，混乱格式用正则。
下次遇到数据清洗的痛点，先想想 DuckDB 字符串函数能不能一行搞定——大概率可以。
📖 详细图文教程和更多实战案例见 duckdblab.org