String functions

Overview

String functions are built-in SQL functions for working with VARCHAR/text data: measuring length, changing case, searching and replacing substrings, trimming whitespace, splitting and joining, and formatting output. They show up constantly in data cleaning, parsing semi-structured text, and building human-readable reports.

Common functions

Copy code
SELECT
  LENGTH(email) AS email_length,
  LOWER(email) AS email_lower,
  TRIM(name) AS trimmed_name,
  email || ' <verified>' AS annotated_email,          -- concatenation
  SPLIT_PART(email, '@', 2) AS domain,
  REPLACE(phone, '-', '') AS phone_digits_only,
  SUBSTRING(sku, 1, 3) AS sku_prefix,
  STARTS_WITH(url, 'https://') AS is_https
FROM customers;

Common categories: case conversion (UPPER/LOWER/INITCAP), trimming (TRIM/LTRIM/RTRIM), search (POSITION, CONTAINS, LIKE), extraction (SUBSTRING, SPLIT_PART, LEFT/RIGHT), and aggregation (STRING_AGG/GROUP_CONCAT to join many rows' strings into one).

Copy code
SELECT department, STRING_AGG(employee_name, ', ' ORDER BY employee_name) AS team
FROM employees
GROUP BY department;

DuckDB specifics

DuckDB implements a large, Postgres-compatible string function library plus several conveniences: printf()/format() for template-style formatting, list_string_agg semantics via STRING_AGG, CONTAINS/STARTS_WITH/ENDS_WITH as readable alternatives to LIKE patterns, and native regular-expression functions (regexp_matches, regexp_replace, regexp_extract, regexp_extract_all). DuckDB also supports the || concatenation operator across strings, lists, and even structs in some contexts, and string_split/str_split to turn a delimited string directly into a LIST:

Copy code
SELECT string_split('a,b,c', ',') AS parts;
-- ['a', 'b', 'c']