Text search is a common and important requirement in data analysis and processing. Whether it’s correcting user input typos, product name matching, or log full-text retrieval, DuckDB provides a rich set of text processing tools. This article will walk you through practical business scenarios to master fuzzy search and text processing techniques in DuckDB.
Scenario: E-commerce Product Search
Imagine you’re building a product search system for an e-commerce platform. User input often contains typos, synonyms, or requires fuzzy matching. We need a system that can handle these scenarios efficiently.
Basic Fuzzy Matching: LIKE Operator
Fig: DuckDB text processing architecture — from user input to final search results
LIKE is the most basic fuzzy matching method, supporting % (any character sequence) and _ (single character) wildcards.
-- Sample data
CREATE TABLE products AS
SELECT * FROM (VALUES
('iPhone 15 Pro Max 256GB', 8999),
('iPhone 15 Pro 128GB', 7999),
('iPhone 14 Plus 128GB', 6999),
('Samsung Galaxy S24 Ultra', 9699),
('Samsung Galaxy S23 FE', 4999),
('Xiaomi 14 Ultra', 5999),
('Xiaomi 14 Pro', 4299),
('Huawei Mate 60 Pro', 6999),
('Huawei Pura 70 Ultra', 7999),
('OPPO Find X7 Ultra', 5499)
) AS t(name, price);
-- Match products containing "iPhone"
SELECT name, price
FROM products
WHERE name LIKE '%iPhone%';
| name | price |
|---|---|
| iPhone 15 Pro Max 256GB | 8999 |
| iPhone 15 Pro 128GB | 7999 |
| iPhone 14 Plus 128GB | 6999 |
LIKE is great for simple pattern matching, but falls short when handling typos.
Advanced Regular Expressions: regexp
DuckDB supports standard regex functions including regexp_matches, regexp_extract, and regexp_replace.
-- Extract brand names
SELECT
name,
regexp_extract(name, '^(iPhone|Samsung|Xiaomi|Huawei|OPPO)', 1) AS brand,
regexp_extract(name, '\d+\s*GB', 0) AS storage
FROM products;
| name | brand | storage |
|---|---|---|
| iPhone 15 Pro Max 256GB | iPhone | 256GB |
| Samsung Galaxy S24 Ultra | Samsung | NULL |
| Xiaomi 14 Ultra | Xiaomi | NULL |
| Huawei Mate 60 Pro | Huawei | NULL |
| OPPO Find X7 Ultra | OPPO | NULL |
-- Clean price format: remove currency symbols, extract digits
SELECT
name,
regexp_replace(name, '[¥$]', '') AS clean_name,
CAST(regexp_extract(name, '\d+', 'g') AS VARCHAR) AS price_digits
FROM products
LIMIT 5;
Spell Correction: Levenshtein Distance
DuckDB has a built-in levenshtein function that calculates the edit distance between two strings. This is extremely useful for handling user input typos.
-- User search term
WITH search AS (
SELECT 'Iphon 15' AS query
),
-- Calculate edit distance with all products
distance AS (
SELECT
p.name,
p.price,
levenshtein(s.query, p.name) AS dist
FROM products p, search s
)
-- Sort by distance, get top 3 most similar results
SELECT name, price, dist
FROM distance
ORDER BY dist ASC
LIMIT 3;
| name | price | dist |
|---|---|---|
| iPhone 15 Pro Max 256GB | 8999 | 9 |
| iPhone 15 Pro 128GB | 7999 | 9 |
| iPhone 14 Plus 128GB | 6999 | 10 |
Fig: Levenshtein distance query execution result (DuckDB CLI)
-- Batch correction: recommend most similar product names
WITH search_terms AS (
SELECT UNNEST(['Iphon 15', 'Samsung Galaxy', 'Xiaomi 14', 'Huawei Mate']) AS query
),
ranked AS (
SELECT
s.query,
p.name AS recommended,
p.price,
levenshtein(s.query, p.name) AS dist,
ROW_NUMBER() OVER (PARTITION BY s.query ORDER BY levenshtein(s.query, p.name)) AS rank
FROM search_terms s
CROSS JOIN products p
)
SELECT query, recommended, price, dist
FROM ranked
WHERE rank <= 2
ORDER BY query, dist;
FTS Full-Text Search Extension
For large-scale text search, DuckDB’s FTS (Full-Text Search) extension provides database-level full-text search capabilities.
-- Load FTS extension
INSTALL fts;
LOAD fts;
-- Create FTS index
CREATE TABLE articles AS
SELECT * FROM (VALUES
('DuckDB Getting Started: From Installation to Practice', 'Detailed guide on DuckDB installation and basic usage'),
('Advanced Window Functions: RANK and LAG Applications', 'In-depth explanation of advanced window function applications in data analysis'),
('Time Series Analysis: Rolling Aggregation and Trend Prediction', 'How to use DuckDB for time series data analysis'),
('JSON Data Processing: Nested Structure Unpacking Techniques', 'Best practices for handling complex JSON data'),
('DuckDB vs PostgreSQL: Performance Comparison Tests', 'Comparing performance of both databases in different scenarios'),
('Building Real-time Data Pipelines with DuckDB', 'Complete workflow from data collection to visualization'),
('SQL Optimization Techniques: Reducing Scans for Better Performance', 'Practical SQL query optimization methods'),
('DuckDB Extension Ecosystem: Plugins and Connectors', 'Introduction to DuckDB''s rich extension system')
) AS t(title, content);
-- Create FTS virtual table
CREATE VIRTUAL TABLE v_articles_fts AS FTS_EXPAND('articles');
-- Full-text search
SELECT title, content, rank
FROM v_articles_fts
WHERE v_articles_fts MATCH 'window functions time series'
ORDER BY rank ASC
LIMIT 5;
The FTS extension also supports boolean queries (AND, OR, NOT), phrase search, and ranked sorting — ideal for building search suggestions and highlighting features.
Comprehensive Practice: Product Search Suggestion System
Combining the above techniques to build a complete product search suggestion system:
-- Search suggestion system
WITH user_query AS (
SELECT 'Samsng Galxy S2' AS query
),
-- 1. Exact match
exact_match AS (
SELECT name, price, 0 AS priority, 'exact' AS match_type
FROM products
WHERE name ILIKE '%Samsng Galxy S2%'
),
-- 2. Regex fuzzy match
regexp_match AS (
SELECT name, price, 1 AS priority, 'regexp' AS match_type
FROM products
WHERE regexp_like(name, 'Sam(sun)?g.*Galax(y|se).*S2')
AND name NOT IN (SELECT name FROM exact_match)
),
-- 3. Levenshtein fuzzy match
fuzzy_match AS (
SELECT
p.name, p.price,
2 AS priority,
'fuzzy' AS match_type,
levenshtein(u.query, p.name) AS dist
FROM products p, user_query u
WHERE levenshtein(u.query, p.name) <= 8
AND p.name NOT IN (SELECT name FROM exact_match)
AND p.name NOT IN (SELECT name FROM regexp_match)
ORDER BY dist
LIMIT 3
),
-- Merge results
all_matches AS (
SELECT * FROM exact_match
UNION ALL
SELECT * FROM regexp_match
UNION ALL
SELECT * FROM fuzzy_match
)
SELECT
name,
price,
match_type,
CASE match_type
WHEN 'fuzzy' THEN dist
ELSE NULL
END AS similarity_score
FROM all_matches
ORDER BY priority, similarity_score;
| name | price | match_type | similarity_score |
|---|---|---|---|
| Samsung Galaxy S24 Ultra | 9699 | fuzzy | 6 |
| Samsung Galaxy S23 FE | 4999 | fuzzy | 7 |
Performance Optimization Tips
- LIKE Indexing: For frequent prefix queries (e.g.,
name LIKE 'Samsung%'), create B-tree indexes. - FTS Indexing: The FTS extension uses inverted indexes, ideal for large-scale text search.
- Batch Levenshtein: Avoid computing Levenshtein distance on the entire table — use LIKE to narrow the scope first.
- ILIKE instead of LIKE: Use
ILIKEfor case-insensitive matching to avoid extra case conversion overhead.
Summary
DuckDB provides a complete text processing toolkit from simple to complex:
- LIKE/ILIKE: Fast prefix and suffix matching
- Regular Expressions: Flexible pattern matching and extraction
- Levenshtein: Spell correction and fuzzy matching
- FTS Extension: Large-scale full-text search
Choose the right tool for your scenario to significantly improve data processing efficiency and accuracy.
For more DuckDB practical tips, follow DuckDB Lab (duckdblab.org).