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: '[email protected]', '[email protected]'

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('[email protected]') AS upper_email,       -- '[email protected]'
    LOWER('[email protected]') AS lower_email,       -- '[email protected]'
    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.

10. Monetization Advice

1. Sell Data Cleaning Services

Many small and medium businesses have “dirty and messy” data but no data team. You can use DuckDB string functions to quickly clean customer data, sales data, etc. Charge 3,000-10,000 RMB per project.

2. Build a Data Cleaning SaaS

Package common cleaning logic (phone standardization, address cleaning, order ID formatting) into an API. Charge per API call. 2,000-5,000 RMB/month is achievable.

3. Sell Automated Report Templates

Combine string functions with MERGE incremental updates to create an “automated daily report generator.” Sell to e-commerce and SaaS companies for 3,000-8,000 RMB one-time.

4. Create DuckDB Training Courses

String functions are one of DuckDB’s most practical skills. Package them into a course series priced at 99-299 RMB per person.

Core selling point: One line of SQL that replaces a day of Python data cleaning work.

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