Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 38 ยท SQL Dialect and Types

Strings and Regular Expressions

Clean and parse text in DuckDB: concatenation, slicing, trimming, LIKE and ILIKE, regexp_matches, regexp_extract, regexp_replace, string_split, format and fuzzy matching.

Text is the messiest data you will meet: stray spaces, mixed case, codes glued to names, dates written five ways. DuckDB ships with a large set of string functions and a fast regular-expression engine (Google's RE2), so most cleanup can be done in a single query before the data reaches a table.

SQLite has only a small string toolbox and no built-in regular expressions. Runnable stand-ins here use SQLite's substr, instr, replace, trim, LIKE and GLOB. DuckDB-only functions are read-only blocks.

The basics

String literals use single quotes; to include a quote, double it ('it''s'). The usual functions:

SQL
-- DuckDB (read-only example)
SELECT 'duck' || 'db'                    AS joined,        -- duckdb
       concat('a', NULL, 'b')            AS skips_null,    -- ab
       upper('Quack'), lower('Quack'),
       length('quack')                   AS chars,         -- 5
       reverse('abc'),                                    -- cba
       repeat('ab', 3),                                   -- ababab
       trim('  padded  '),
       lpad('7', 3, '0')                 AS padded;        -- 007

Note the || operator returns NULL if either side is NULL, while concat() skips NULLs. Use concat_ws(', ', a, b, c) to join with a separator.

Slicing and searching

DuckDB strings are 1-indexed and support slicing with brackets:

SQL
-- DuckDB (read-only example)
SELECT 'duckdb'[1:4]            AS first_four,  -- duck
       'duckdb'[-2:]            AS last_two,    -- db
       substring('duckdb', 5, 2),               -- db
       left('duckdb', 4), right('duckdb', 2),
       position('db' IN 'duckdb') AS where_,    -- 5
       contains('duckdb', 'uck'),               -- true
       starts_with('duckdb', 'duck'),
       replace('a-b-c', '-', '+');              -- a+b+c

The runnable stand-in uses SQLite's names for the same operations:

SQL Stand-in: slice, search and replace ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Matching with LIKE, ILIKE and friends

LIKE matches with % (any run of characters) and _ (one character). ILIKE ignores case. DuckDB also offers SIMILAR TO and the glob operator:

SQL
-- DuckDB (read-only example)
SELECT 'Quacker' ILIKE 'quack%';          -- true
SELECT 'file_01.csv' LIKE 'file\_%.csv' ESCAPE '\';
SELECT 'report.csv' GLOB '*.csv';          -- true
SQL Stand-in: LIKE patterns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Regular expressions

When patterns are too complex for LIKE, use regular expressions. The important functions:

Function Purpose
regexp_matches(s, pattern) true if the pattern is found anywhere in s
regexp_full_match(s, pattern) true only if the whole string matches
regexp_extract(s, pattern, group) returns text captured by a group
regexp_extract_all(s, pattern) list of all matches
regexp_replace(s, pattern, replacement) replace the first match (add 'g' for all)
regexp_split_to_array(s, pattern) split into a list
s ~ 'pattern' operator form of a full match in some contexts
SQL
-- DuckDB (read-only example)
SELECT regexp_matches('order-2026-0042', '\d{4}-\d{4}');                   -- true
SELECT regexp_extract('order-2026-0042', '(\d{4})-(\d{4})', 1);            -- 2026
SELECT regexp_extract_all('call 555-1234 or 555-9876', '\d{3}-\d{4}');     -- [555-1234, 555-9876]
SELECT regexp_replace('  too   many   spaces ', '\s+', ' ', 'g');          -- " too many spaces "

The extra argument is a flags string: 'g' for global replace, 'i' for case-insensitive, 's' for dot-matches-newline. Patterns use RE2 syntax, which supports character classes, groups, alternation and lazy quantifiers, but not back-references or look-around (the trade-off that keeps it fast and safe from catastrophic backtracking).

Tip: write regular expressions in raw strings or double the backslashes, depending on your language when you run SQL from Python. In SQL itself, backslashes in a normal string are literal, so '\d' already means backslash-d.

Splitting and joining

SQL
-- DuckDB (read-only example)
SELECT string_split('a,b,c', ',')               AS parts,       -- [a, b, c]
       split_part('a,b,c', ',', 2)              AS second,      -- b
       array_to_string(['a','b','c'], '-')      AS joined;      -- a-b-c

SELECT string_agg(name, ', ' ORDER BY name) FROM products;      -- one string

string_split gives you a list, which you can UNNEST to turn "tag1,tag2,tag3" columns into rows (see Lists, structs and maps). string_agg is the aggregate that concatenates the values of a group, a favorite for reports. Its SQLite counterpart is group_concat:

SQL Stand-in: aggregate strings per group ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Formatting

format() and printf() build strings from values:

SQL
-- DuckDB (read-only example)
SELECT format('{} has {} ducks', 'Ana', 3);              -- Ana has 3 ducks
SELECT format('{:.2f}', 3.14159);                         -- 3.14
SELECT printf('%05d | %s', 42, 'padded');                 -- 00042 | padded
SELECT format('{:,}', 1234567);                           -- 1,234,567

Unicode, accents and fuzzy matching

DuckDB strings are UTF-8, and length() counts characters, not bytes (use strlen() for bytes). Useful helpers for messy real-world names:

SQL
-- DuckDB (read-only example)
SELECT strip_accents('Lucia Ferreyra'),                  -- accents removed from letters like e-acute
       levenshtein('duck', 'dock')                       AS edits,        -- 1
       jaro_winkler_similarity('Martha', 'Marhta')       AS similarity,   -- close to 1
       damerau_levenshtein('ab', 'ba')                   AS swapped;      -- 1

Fuzzy matching lets you join two lists of names that do not match exactly. A typical pipeline lowercases, strips accents, removes punctuation with regexp_replace, and then compares with a similarity threshold.

Collation and sorting

By default, strings sort by their binary (byte) order, so 'Zebra' comes before 'apple'. For case-insensitive or locale-aware sort, use COLLATE NOCASE or a language collation from the ICU extension, e.g. ORDER BY name COLLATE NOCASE.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does concat('a', NULL, 'b') return in DuckDB?

    concat() skips NULL values, while the || operator returns NULL if any operand is NULL.
  2. 2. Which function pulls out text captured by a regular-expression group?

    regexp_extract(string, pattern, group) returns the text of the selected capture group.
  3. 3. What is the difference between LIKE and ILIKE?

    ILIKE is a case-insensitive LIKE.
  4. 4. What does string_agg(name, ', ') do in a GROUP BY query?

    string_agg concatenates the values within each group using the separator.

Summary

  • || returns NULL with a NULL operand; concat() skips NULLs; concat_ws adds a separator.
  • Strings are 1-indexed and sliceable; use substring, position, contains, replace.
  • LIKE, ILIKE and GLOB match simple patterns; regexp_* functions use RE2 for the rest.
  • string_split and string_agg convert between strings and lists.
  • format, strip_accents and similarity functions help with output and messy names.

Next: Dates, times and intervals