SQL string functions: cleaning, combining and matching text correctly

This lesson continues the shop example from lessons P07–P11. The five customers now have email addresses. A new signups table contains newsletter registrations submitted through a web form.

The examples run in DuckDB. Where PostgreSQL behaves the same way, the explanation names both databases. Where it differs, a note explains what to change.

The sample data

The shop stores customer email addresses in lowercase, without surrounding spaces.

The newsletter form contains the values people entered.

Throughout the lesson, quotes make otherwise hard-to-see differences visible. We show surrounding spaces, repeated spaces, tabs and empty strings inside quotes. The quotes are display markers, not part of the stored value. \t represents an actual tab.

A few details matter:

• Asha, Ben, Carla, Esha and Dev are existing customers. Reuben and Sam are not.
• Asha’s missing middle name is stored as an empty string, ''. Several other missing middle names are stored as NULL.
• Carla’s surname looks like “Muñoz,” but its ñ is stored as two Unicode code points: n followed by a combining tilde. §07 explains why this matters.
• Dev entered dev.example.com, with a full stop where the @ should be.

Names, email addresses, phone numbers, addresses and product codes are commonly stored as text.

DuckDB uses the type VARCHAR; TEXT and STRING are aliases for the same type. PostgreSQL also provides text and varchar.

A few basic rules for writing text in SQL queries:

• Write text values inside single quotes.
• Double quotes identify table or column names.
• To include an apostrophe inside a text value, write it twice.

As shown in the below example:

SELECT
    'O''Brien' AS with_apostrophe,
    ''         AS empty_string,
    length('') AS empty_length,
    '' IS NULL AS empty_is_null;

An empty string contains zero characters. It is still a value. NULL represents a missing or unknown value, as discussed in lesson P02.

Marketing wants to identify newsletter sign-ups from existing customers so those people do not receive an offer intended for new customers.

Five sign-ups came from existing customers. The analyst tries to find them by joining the two tables on email.

SELECT s.signup_id, s.email, c.customer_id
FROM signups AS s
JOIN customers AS c ON c.email = s.email
ORDER BY s.signup_id;

Checking the lengths helps reveal some of the differences:

SELECT signup_id, email, length(email) AS chars
FROM signups
ORDER BY signup_id;

Each missed customer has a different problem:

• Asha: asha@example.com has 16 characters. Her submitted value has 18 because it includes a space at each end. Its capitalisation also differs.
• Carla: Her address is in upper case.
• Esha: Her address ends with a tab, adding one character.
• Dev: His address contains a full stop instead of @.

Changing case can fix Carla’s address. Trimming can fix Asha’s spaces and, with the right settings, Esha’s. Dev’s address needs correction rather than formatting.

• lower(): converts letters to lower case.
• upper(): converts them to upper case.

These functions change capitalisation without removing surrounding spaces or fixing spelling. The shop stores customer emails in lower case, so the analyst applies lower() to the submitted addresses:

SELECT s.signup_id, c.customer_id
FROM signups AS s
JOIN customers AS c ON c.email = lower(s.email)
ORDER BY s.signup_id;

Carla now matches. Asha’s address is also converted to lower case, but the spaces remain, so it still does not match.

Only the signup side needs conversion here because the customer table already follows the shop’s rule. If either side may contain different capitalisation, apply the same rule to both:

lower(first_value) = lower(second_value)

Another approach is to use a case-insensitive collation. This changes how the comparison works without changing the displayed values:

SELECT signup_id, email
FROM signups
WHERE email = 'carla@example.com' COLLATE NOCASE;

DuckDB and SQLite provide a collation named NOCASE. PostgreSQL does not have a built-in collation with that exact name; use lower() or an appropriate configured collation.

• trim(): removes specified characters from both ends of a string.
• ltrim(): works on the left end.
• rtrim(): works on the right.

These functions leave the middle of the value unchanged.

SELECT s.signup_id, c.customer_id
FROM signups AS s
JOIN customers AS c ON c.email = lower(trim(s.email))
ORDER BY s.signup_id;

To remove a specific set of characters, provide them as the second argument:

SELECT s.signup_id, c.customer_id
FROM signups AS s
JOIN customers AS c ON c.email = lower(trim(s.email, E' \t\r\n'))
ORDER BY s.signup_id;

trim() removes any of those characters from either end. Four customers now match. Dev’s address still contains the wrong character, which trimming cannot correct.

Use = when you want to compare a complete value. Use LIKE when you want to match a pattern. In a LIKE pattern, % represents any sequence of characters, including an empty sequence.

Now, marketing wants sign-ups whose addresses end with @example.com:

SELECT signup_id, email
FROM signups
WHERE email LIKE '%@example.com'
ORDER BY signup_id;

The query finds three addresses. The pattern also requires the value to end immediately after .com. Asha’s trailing space and Esha’s tab prevent their addresses from matching.

ILIKE performs case-insensitive pattern matching in both DuckDB and PostgreSQL:

SELECT signup_id, email
FROM signups
WHERE email ILIKE '%@example.com'
ORDER BY signup_id;

Carla now matches. Asha and Esha still have extra characters after the domain. Cleaning first solves both problems:

lower(trim(email, E' \t\r\n')) LIKE '%@example.com'

That condition finds all six addresses with the intended domain.

Using = would change the meaning. The condition email = '%@example.com' searches for that exact text, including the percent sign. % acts as a wildcard only in a pattern operation such as LIKE.

An older import also used underscores in some addresses. The analyst tries to find those addresses with the following pattern.

SELECT signup_id, email
FROM signups
WHERE email LIKE '%_%'
ORDER BY signup_id;

To search for an actual underscore, escape it:

SELECT signup_id, email
FROM signups
WHERE email LIKE '%\_%' ESCAPE '\'
ORDER BY signup_id;

ESCAPE '\' identifies the backslash as the escape character. The sequence \_ then means a literal underscore. The same approach works for a literal percent sign.

For a fixed piece of text, DuckDB’s contains(email, '_') avoids wildcard syntax altogether. In PostgreSQL, an equivalent check is strpos(email, '_') > 0.

Marketing wants to count sign-ups by email domain.

In DuckDB and PostgreSQL, split_part(text, delimiter, part_number) splits text at the delimiter and returns the requested piece. Part numbers start at 1:

SELECT
    signup_id,
    email,
    split_part(email, '@', 1) AS local_part,
    split_part(email, '@', 2) AS domain
FROM signups
WHERE signup_id IN (102, 107)
ORDER BY signup_id;

You can also extract text by position:

The analyst uses position() to find @, then extracts everything after it.

SELECT
    signup_id,
    email,
    substring(email, position('@' IN email) + 1) AS domain
FROM signups
WHERE signup_id IN (102, 106)
ORDER BY signup_id;

split_part() handles the missing piece differently:

SELECT
    signup_id,
    position('@' IN email)   AS at_position,
    split_part(email, '@', 2) AS split_domain
FROM signups
WHERE signup_id IN (102, 106)
ORDER BY signup_id;

Because there is no second piece, split_part() returns an empty string. Neither result automatically becomes NULL. A report could therefore contain an incorrect domain or an empty group label.

Use NULLIF() to make an empty result explicit:

SELECT
    signup_id,
    NULLIF(split_part(email, '@', 2), '') AS domain
FROM signups
WHERE signup_id IN (102, 106)
ORDER BY signup_id;

NULLIF(first_value, second_value) returns NULL when the values are equal. Otherwise, it returns the first value.

SELECT
    substring('ben@example.com', 0, 4) AS from_0,
    substring('ben@example.com', 1, 4) AS from_1;

Starting at 0 with a length of 4 reaches positions 0–3, so only the first three actual characters are returned.

In DuckDB, length() counts Unicode code points, while strlen() counts storage bytes. These counts can differ because UTF-8 characters may use more than one byte:

SELECT signup_id, middle_name,
       length(middle_name) AS chars,
       strlen(middle_name) AS bytes
FROM signups
WHERE signup_id = 103;

“Inés” has four characters but five bytes because é uses two bytes.

A visible character can also contain multiple code points. Carla’s ñ is stored as n plus a combining tilde. Her surname therefore has five visible characters, six code points and seven bytes. DuckDB’s length_grapheme() counts the visible character groups.

This difference also affects exact comparisons. Searching for 'Muñoz' with a single-code-point ñ finds no row. NFC normalisation converts equivalent Unicode representations into a consistent form:

SELECT signup_id, last_name
FROM signups
WHERE nfc_normalize(last_name) = 'Muñoz';

Normalise both sides if either may contain different representations. NFC preserves accents; it does not make Muñoz equal to Munoz.

The welcome email needs a full name built from the first, middle and last name columns.

In DuckDB and PostgreSQL, || joins text values. concat() also combines values, but the two handle NULL differently:

SELECT
    signup_id,
    first_name || ' ' || middle_name || ' ' || last_name AS with_pipes,
    concat(first_name, ' ', middle_name, ' ', last_name) AS with_concat
FROM signups
WHERE signup_id IN (102, 103)
ORDER BY signup_id;

Ben’s middle name is NULL.

With ||, that missing value makes the entire result NULL.

concat() skips the NULL, but it still includes both separately supplied spaces. Ben’s full name therefore contains two spaces between the first and last names.

concat_ws(separator, ...) means “concatenate with separator.” It places the separator between the values and skips NULL arguments.

SELECT signup_id, concat_ws(' ', first_name, middle_name, last_name) AS full_name
FROM signups
WHERE signup_id IN (101, 102)
ORDER BY signup_id;

Ben’s NULL middle name is skipped, so his result has just one separator. When an empty string means “no value,” convert it to NULL before combining the parts:

SELECT signup_id, concat_ws(' ', first_name, NULLIF(middle_name, ''), last_name) AS full_name
FROM signups
ORDER BY signup_id;

The names keep the capitalisation people entered. Sorting or changing that capitalisation is a separate operation. To combine values from multiple rows into one string, use the aggregate string_agg():

SELECT string_agg(first_name, ', ' ORDER BY signup_id) AS first_names
FROM signups;

replace(text, old_text, new_text) replaces every occurrence of a fixed piece of text.

In DuckDB and PostgreSQL, the replacement is literal and case-sensitive. Replacing 'a' does not also replace 'A' or 'á'.

Below is an example with replace:

SELECT signup_id, phone, replace(phone, ' ', '') AS no_spaces
FROM signups
WHERE signup_id = 101;

When you need to describe a set of characters or a more complicated pattern, use a regular expression. Below is a list of patterns and their meanings:

DuckDB’s regexp_matches(text, pattern) returns true when it finds a match. The following query cleans the email addresses, then finds those that fail a simple format check:

SELECT signup_id, email
FROM signups
WHERE NOT regexp_matches(lower(trim(email, E' \t\r\n')), '^[a-z0-9._]+@[a-z0-9-]+(\.[a-z0-9-]+)+$')
ORDER BY signup_id;

The pattern requires text before @, a domain after it, and at least one full stop within the domain. The ^ and $ require the pattern to cover the whole value.

DuckDB also provides regexp_extract(text, pattern, group_number). It returns the text captured by a parenthesised group. If there is no match, it returns an empty string, so handle missing results just as you did with split_part().

The system now asks for digits only, including removal of the leading +. The analyst uses [^0-9] to identify characters to remove.

SELECT signup_id, phone, regexp_replace(phone, '[^0-9]', '') AS digits
FROM signups
WHERE signup_id = 101;

Add the option 'g', meaning global, to replace every match:

SELECT signup_id, phone, regexp_replace(phone, '[^0-9]', '', 'g') AS digits
FROM signups
ORDER BY signup_id;

The same approach can clean spaces inside text:

regexp_replace(name, ' {2,}', ' ', 'g')

The pattern means “two or more consecutive ordinary spaces.” Each run becomes one space. Unlike trim(), this can change the middle of a value. Replacement defaults differ across databases. MySQL and Oracle replace all matches by default. SQL Server’s built-in regular-expression functions, available from SQL Server 2025, also use all occurrences as the default for replacement.

The events team wants a printed list of first names in alphabetical order. Two people entered their names in lower case: ben and reuben.

SELECT first_name
FROM signups
ORDER BY first_name;

To ignore capitalisation when sorting these names, sort by their lower-case forms:

SELECT first_name
FROM signups
ORDER BY lower(first_name), first_name;

The first sort expression ignores capitalisation. The second uses the original name to resolve ties between values that differ only in case.

ORDER BY first_name COLLATE NOCASE also produces this order in DuckDB.

The earlier sections handled one problem at a time. We can now bring the cleaning steps together. Define the cleaned email in one place, using a CTE as in lesson P10. Then join on that value.

A LEFT JOIN keeps every sign-up, including those with no matching customer:

WITH clean_signups AS (
    SELECT signup_id, lower(trim(email, E' \t\r\n')) AS clean_email
    FROM signups
)
SELECT s.signup_id, s.clean_email, c.customer_id
FROM clean_signups AS s
LEFT JOIN customers AS c ON c.email = s.clean_email
ORDER BY s.signup_id;

Four sign-ups match existing customers. Reuben and Sam correctly remain unmatched. Dev also remains unmatched because his submitted address is malformed. That value needs review.

The CTE keeps the cleaning rule together and gives the result a clear name. It does not, by itself, guarantee that the database physically calculates the expression only once.

A colleague suggests a looser join: accept a sign-up whenever its email contains a customer’s email anywhere inside it. This would allow extra characters at either end without explicitly removing them.

SELECT s.signup_id, c.customer_id
FROM signups AS s
JOIN customers AS c ON lower(s.email) LIKE '%' || c.email || '%'
ORDER BY s.signup_id;

Keep the join based on equality after applying a defined cleaning rule. Leave values that still fail to match for review rather than accepting increasingly loose matches.

Text problems often return plausible results instead of errors. A query may miss a customer, split one group into two, or join two unrelated records.

Use the following checks to investigate unexpected results:

Before writing the query, answer four questions:

1. Where did the text come from? Check what the application enforces and what users can enter.
2. Which differences should matter? Decide how to handle capitalisation, spaces, accents and punctuation.
3. What happens when a piece is missing? Check whether each function returns 0, an empty string or NULL.
4. Where will the cleaning rule be defined? Keep it consistent, whether it runs during loading or in a shared query expression.

The examples use DuckDB. Other databases provide similar tools, but their comparison rules, function names and handling of missing values can differ.

PostgreSQL supports many of the same functions used here, including lower, trim, ILIKE, split_part, strpos, concat_ws and string_agg. These differences need particular attention:

See PostgreSQL’s string functions, pattern matching and collation rules for details.

For the other engines, keep these differences in mind:

Start by deciding which differences should matter when comparing text. Apply consistent rules for capitalisation, surrounding characters and equivalent Unicode representations. Check what each function returns when it cannot find the requested text.

Use = for complete-value comparisons and LIKE for patterns. Escape wildcard characters when you mean them literally. When combining values, handle NULL and empty strings deliberately. When replacing patterns, check whether the function changes one match or all matches.

For reliable reports and joins, keep the cleaning rule in one place, check that cleaned keys remain unique, and review values that still do not match. A believable row count does not guarantee correct matches.

Next, lesson P13 covers data types and CAST: how a value’s type affects its behaviour, and how to convert text into numbers and dates safely.