DuckDB String Functions Complete Guide — Data Cleaning Without the Pain

Have you ever encountered data like this?

• Messy order IDs: 'ORD-2026-001', 'ord_001', 'ORD2026001'
• Names with extra spaces: ' 张三 ', '李四 ', ' 王五'
• Phone numbers in various formats: '(010)12345678', '+86-13800138000'
• Addresses with redundant info: '北京市海淀区中关村大街1号 3号楼'
• Emails with inconsistent casing: 'User@Example.COM', 'test@example.com'

Writing Python regex? Takes a day. Writing SQL CASE WHEN? The code is longer than the data.

Today we cover DuckDB’s complete string function toolkit — 20 essential functions that handle 90% of data cleaning scenarios.

1. Basic Extraction and Concatenation

STRPOS / POSITION — Find Substring Location

SELECT 
 'ORD-2026-001' AS order_id,
 STRPOS('ORD-2026-001', '-') AS first_dash, -- 3
 STRPOS('ORD-2026-001', '-', 4) AS second_dash; -- 8

Key point: STRPOS(str, substr) returns the 1-based position of the first occurrence. The third argument specifies the starting position.

SUBSTRING / SUBSTR — Extract Substring

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'

Key point: DuckDB supports standard SQL syntax SUBSTRING(str FROM start FOR length) and the shorthand SUBSTRING(str FROM start).

LEFT / RIGHT — Extract from Ends

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 / || — String Concatenation

SELECT 
 CONCAT('hello', ' ', 'world') AS result1, -- 'hello world'
 'hello' || ' ' || 'world' AS result2; -- 'hello world' (recommended)

Key point: || is the SQL standard concatenation operator. Unlike CONCAT, it returns NULL if any argument is NULL. Use CONCAT to automatically skip NULLs.

CONCAT_WS — Concatenation with Separator

SELECT 
 CONCAT_WS('-', '2026', '08', '14') AS date_str, -- '2026-08-14'
 CONCAT_WS(',', 'Alice', 'Engineer', 'Beijing') AS info; -- 'Alice,Engineer,Beijing'

Key point: CONCAT_WS(separator, ...) takes the separator as the first argument and automatically skips NULL values.

2. Case and Formatting

UPPER / LOWER / INITCAP — Case Control

SELECT 
 UPPER('user@example.com') AS upper_email, -- 'USER@EXAMPLE.COM'
 LOWER('USER@EXAMPLE.COM') AS lower_email, -- 'user@example.com'
 INITCAP('hello world') AS capitalized; -- 'Hello World'

Key point: INITCAP capitalizes the first letter of each word — perfect for names and titles.

LPAD / RPAD — Padding and Alignment

SELECT 
 LPAD('1', 5, '0') AS padded_left, -- '00001'
 RPAD('1', 5, '0') AS padded_right; -- '10000'

Key point: LPAD(str, length, pad) pads on the left, RPAD on the right. Essential for generating fixed-length codes.

TRIM / LTRIM / RTRIM — Remove Whitespace

SELECT 
 TRIM(' Alice ') AS trimmed, -- 'Alice'
 LTRIM(' Alice ') AS left_trim, -- 'Alice '
 RTRIM(' Alice ') AS right_trim; -- ' Alice'

Key point: TRIM removes whitespace from both ends by default. Use TRIM(LEADING/RTRAILING/BOTH) for directional control.

3. Replacement and Cleaning

REPLACE — Global Replacement

SELECT 
 REPLACE('ORD-2026-001', '-', '') AS no_dash, -- 'ORD2026001'
 REPLACE('2026-08-14', '-', '/') AS changed; -- '2026/08/14'

REGEXP_REPLACE — Regex Replacement (The Killer Feature)

SELECT 
 REGEXP_REPLACE('(010)12345678', '[^0-9]', '') AS clean_phone, -- '01012345678'
 REGEXP_REPLACE('hello123world456', '[0-9]', '-') AS no_digits; -- 'hello-world-'

Key point: REGEXP_REPLACE(str, pattern, replacement) uses regex to match and replace — the ultimate weapon for handling non-standard data formats.

4. Splitting and Extraction

SPLIT_PART — Extract Nth Segment by Delimiter

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);

Key point: SPLIT_PART(str, delimiter, field_num) is DuckDB’s most-used string splitting function — much cleaner than Python’s split()[n].

REGEXP_SPLIT_TO_TABLE — Split into Rows

SELECT * FROM REGEXP_SPLIT_TO_TABLE('Python,SQL,DuckDB', ',');

Result:

regex_split_to_table
---------------------
Python
SQL
DuckDB

REGEXP_EXTRACT — Regex Extraction

SELECT 
 REGEXP_EXTRACT('ORD-2026-001', '(\d{4})') AS year, -- '2026'
 REGEXP_EXTRACT('ORD-2026-001', '(\d{3})$') AS num; -- '001'

Key point: REGEXP_EXTRACT(str, pattern) extracts the first match. Combine with COALESCE for default values.

5. Length and Search

LENGTH / CHAR_LENGTH — String Length

SELECT 
 LENGTH('DuckDB') AS byte_len, -- 6
 LENGTH('张三') AS cn_byte_len; -- 6 (UTF-8: 3 bytes per Chinese char)
 
SELECT 
 CHAR_LENGTH('DuckDB') AS char_len, -- 6
 CHAR_LENGTH('张三') AS cn_char_len; -- 2 (by character count)

Key point: LENGTH counts bytes, CHAR_LENGTH counts characters. Always use CHAR_LENGTH for Chinese data.

CONTAINS / LIKE — Pattern Matching

SELECT 
 'DuckDB' LIKE 'Duck%' AS starts_with_duck, -- true
 'DuckDB' LIKE '%DB' AS ends_with_db, -- true
 'DuckDB' LIKE '%uck%' AS contains_uck; -- true
 
-- Recommended: CONTAINS (DuckDB-specific)
SELECT 
 CONTAINS('DuckDB', 'uck') AS has_uck; -- true

Key point: CONTAINS(str, substring) is DuckDB’s dedicated function — more intuitive than LIKE '%xxx%'.

6. Real-World Cases

Case 1: Phone Number Cleaning

Real scenario: customer phone numbers in messy formats need standardization.

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);

-- Clean: remove all non-digit characters
SELECT 
 name,
 phone_raw AS original,
 REGEXP_REPLACE(phone_raw, '[^0-9]', '') AS phone_clean
FROM raw_customers;

Result:

name | original | phone_clean
--------|-------------------|-------------
Alice | (010)12345678 | 01012345678
Bob | +86-13800138000 | 8613800138000
Charlie | 13800138000 | 13800138000
David | 138 0013 8000 | 13800138000
Eve | 13800138000... | 13800138000

Key point: One line of REGEXP_REPLACE(phone, '[^0-9]', '') handles all format variations — 10x cleaner than writing 10 CASE WHEN statements.

Case 2: Order ID Standardization

CREATE TABLE orders AS
SELECT * FROM VALUES
 ('ORD-2026-001'),
 ('ord_001'),
 ('ORD2026001'),
 ('order-2026-002'),
 ('2026-003')
AS t(order_id_raw);

-- Standardize to 'ORD-YYYY-NNN' format
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;

Result:

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

7. Comparison with Traditional Tools

Conclusion: DuckDB string functions turn data cleaning from “writing code” into “writing SQL” — one line replaces what used to take multiple lines.

8. Quick Reference Cheat Sheet

9. Pitfalls to Avoid

1. NULL propagation: Any string function returns NULL when given NULL input. Use COALESCE(str, '') first.
2. Bytes vs characters: LENGTH counts bytes, CHAR_LENGTH counts characters. Always use CHAR_LENGTH for Chinese data.
3. REGEXP performance: Complex regex is slow on large tables. Filter with LIKE first, then apply REGEXP_REPLACE.
4. Empty string vs NULL: TRIM('') returns empty string '', not NULL. Know the difference.
5. SPLIT_PART out of bounds: Returns NULL when the requested segment doesn’t exist. Don’t assume it always exists.

Summary

DuckDB string functions cover 90% of data cleaning scenarios:

• Extract & join: SUBSTRING, CONCAT_WS, LPAD
• Format handling: TRIM, UPPER/LOWER, INITCAP
• Replace & clean: REPLACE, REGEXP_REPLACE
• Split & extract: SPLIT_PART, REGEXP_EXTRACT

Remember this mantra: Use built-in functions for standard formats, regex for messy data.

Next time you face a data cleaning pain point, think — can DuckDB string functions handle it in one line? Chances are, they can.

📖 Full tutorial and more实战 cases at duckdblab.org

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。