String Data Types

Learning Focus

Use this lesson to understand String Data Types in DuckDB and common string operations.

Available String Types

Type	Description
VARCHAR	Variable-length string (unlimited length)
VARCHAR(n)	Variable-length string with max n characters
TEXT	Alias for VARCHAR
CHAR(n)	Fixed-length, padded with spaces
BLOB	Binary large object

CREATE TABLE users (
    id       INTEGER,
    username VARCHAR(50),
    bio      TEXT,
    avatar   BLOB
);

DuckDB vs MySQL Strings

DuckDB's VARCHAR has no practical upper limit (unlike MySQL's 65535-byte row limit). You don't need TEXT vs VARCHAR(255) distinctions — just use VARCHAR.

Common String Functions

-- Length
SELECT length('DuckDB');              -- 6
SELECT strlen('DuckDB');              -- 6

-- Case
SELECT upper('hello');                -- HELLO
SELECT lower('WORLD');                -- world

-- Trimming
SELECT trim('  hello  ');             -- 'hello'
SELECT ltrim('  hello');              -- 'hello'
SELECT rtrim('hello  ');              -- 'hello'

-- Substring
SELECT substring('DuckDB', 1, 4);    -- Duck
SELECT substr('DuckDB', 5);          -- DB

-- Concatenation
SELECT 'Hello' || ' ' || 'World';    -- Hello World
SELECT concat('Hello', ' ', 'World'); -- Hello World

-- Replace
SELECT replace('foo bar', 'bar', 'baz');  -- foo baz

-- Reverse
SELECT reverse('DuckDB');             -- BDkcuD

Pattern Matching

-- LIKE: % = any chars, _ = single char
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE username LIKE '_ob';

-- ILIKE: case-insensitive LIKE
SELECT * FROM users WHERE username ILIKE 'alice%';

-- SIMILAR TO (POSIX regex)
SELECT * FROM users WHERE username SIMILAR TO '[A-Za-z]+';

-- regexp_matches
SELECT regexp_matches('DuckDB 1.0', '\d+\.\d+');  -- true
SELECT regexp_extract('Order-12345', '\d+');       -- 12345

String Aggregation

-- Concatenate all values in a group
SELECT dept, string_agg(name, ', ') AS members
FROM employees
GROUP BY dept;

-- With ordering
SELECT dept, string_agg(name, ', ' ORDER BY name) AS members
FROM employees
GROUP BY dept;

Type Conversion

SELECT 42::VARCHAR;                    -- '42'
SELECT CAST(3.14 AS TEXT);             -- '3.14'
SELECT '2024-01-15'::DATE;            -- date value
SELECT TRY_CAST('abc' AS INTEGER);     -- NULL (safe cast)

Common Pitfalls

Pitfall	Consequence	Prevention
Using VARCHAR(255) unnecessarily	No actual benefit in DuckDB	Just use VARCHAR — size limit is rarely needed
Case-sensitive LIKE	Missing matches	Use ILIKE for case-insensitive matching
Concatenating NULLs	Result is NULL	Use coalesce(val, '') before concatenation

Quick Reference

SELECT upper('text'), lower('TEXT');
SELECT length('DuckDB');
SELECT 'a' || 'b' || 'c';               -- abc
SELECT string_agg(col, ',') FROM table;
SELECT regexp_extract('v1.2.3', '\d+'); -- 1

What's Next

• Next: Date and Time Data Types - Handle temporal data in DuckDB.
• Module Overview - Return to this module index.