SQL String Functions: TRIM, SUBSTRING, CONCAT
· 7 min read
SQL string functions change or test text values: change case, cut off spaces, pull out part of a value, glue values together, or search for a pattern. For analysts, almost all of it comes down to one job: making messy text consistent enough to group, join and filter on.
SELECT lead_id,
TRIM(full_name) AS name,
LOWER(TRIM(email)) AS email,
UPPER(TRIM(city)) AS city
FROM leads
ORDER BY lead_id;This guide works through the functions you will use most on one small, messy table. Every output below comes from DuckDB, with notes where Postgres, MySQL and SQL Server differ.
The example table
| lead_id | full_name | city | plan_code | |
|---|---|---|---|---|
| 1 | Ana Lopez | Ana.Lopez@Acme.io | austin | PRO-2025-M |
| 2 | Ben Ito | ben@birch.co | Boston | BASIC-2024-Y |
| 3 | Cara Diaz | CARA@cobalt.dev | AUSTIN | PRO-2025-Y |
| 4 | Dev Rao | NULL | Denver | TEAM-2025-M |
The table hides some spaces you cannot see here: Ana's name has two leading spaces and one trailing, Ben's email ends in a space, and Cara's city is 'AUSTIN ' with a trailing space. Cara's name also has a double space in the middle.
LOWER, UPPER and TRIM
The query at the top returns:
| lead_id | name | city | |
|---|---|---|---|
| 1 | Ana Lopez | ana.lopez@acme.io | AUSTIN |
| 2 | Ben Ito | ben@birch.co | BOSTON |
| 3 | Cara Diaz | cara@cobalt.dev | AUSTIN |
| 4 | Dev Rao | NULL | DENVER |
LOWERandUPPERchange case. Use them so'austin'and'AUSTIN'count as the same city.TRIMremoves spaces at both ends.LTRIMandRTRIMdo one side only.- Every string function returns
NULLfor aNULLinput, so Dev's email staysNULL.
TRIM does not touch spaces in the middle, which is why Cara still has two. REPLACE, below, handles those.
Why bother? Look at how many cities the table has, before and after cleaning:
SELECT COUNT(DISTINCT city) AS raw_cities,
COUNT(DISTINCT LOWER(TRIM(city))) AS clean_cities
FROM leads;| raw_cities | clean_cities |
|---|---|
| 4 | 3 |
Raw, Austin counts as two cities, and any GROUP BY city would split its leads. See SQL COUNT DISTINCT.
LENGTH: find hidden spaces
LENGTH counts characters. Comparing it before and after TRIM is the quickest way to find values with invisible spaces:
SELECT lead_id,
LENGTH(full_name) AS raw_length,
LENGTH(TRIM(full_name)) AS trimmed_length
FROM leads
ORDER BY lead_id;| lead_id | raw_length | trimmed_length |
|---|---|---|
| 1 | 12 | 9 |
| 2 | 7 | 7 |
| 3 | 10 | 10 |
| 4 | 7 | 7 |
Ana's name is 12 characters stored, 9 visible. SQL Server's LEN ignores trailing spaces, so this check needs DATALENGTH there.
SUBSTRING, LEFT, RIGHT and SPLIT_PART
SUBSTRING(text, start, length) returns part of a string. Positions start at 1. LEFT(text, n) and RIGHT(text, n) take characters from either end.
SELECT plan_code,
LEFT(plan_code, 3) AS first_3,
SUBSTRING(plan_code, 5, 4) AS chars_5_to_8,
SPLIT_PART(plan_code, '-', 2) AS plan_year,
RIGHT(plan_code, 1) AS billing
FROM leads
ORDER BY lead_id;| plan_code | first_3 | chars_5_to_8 | plan_year | billing |
|---|---|---|---|---|
| PRO-2025-M | PRO | 2025 | 2025 | M |
| BASIC-2024-Y | BAS | C-20 | 2024 | Y |
| PRO-2025-Y | PRO | 2025 | 2025 | Y |
| TEAM-2025-M | TEA | -202 | 2025 | M |
SUBSTRING(plan_code, 5, 4) works for PRO codes and returns garbage for BASIC and TEAM, because the year does not always start at position 5. Fixed positions are only safe when every value has the same layout. Otherwise split on the separator: SPLIT_PART(text, '-', 2) returns the second piece. SPLIT_PART works in DuckDB and Postgres; MySQL uses SUBSTRING_INDEX, and SQL Server needs CHARINDEX with SUBSTRING.
Everything after a character: POSITION
To get an email's domain, find the @ and take everything after it:
SELECT TRIM(email) AS email,
POSITION('@' IN TRIM(email)) AS at_sign,
LOWER(SUBSTRING(TRIM(email), POSITION('@' IN TRIM(email)) + 1)) AS domain
FROM leads
ORDER BY lead_id;| at_sign | domain | |
|---|---|---|
| Ana.Lopez@Acme.io | 10 | acme.io |
| ben@birch.co | 4 | birch.co |
| CARA@cobalt.dev | 5 | cobalt.dev |
| NULL | NULL | NULL |
SUBSTRING with no length runs to the end of the string. POSITION(x IN y) is standard SQL; Postgres and DuckDB also accept STRPOS(y, x), and SQL Server uses CHARINDEX(x, y).
Concatenate two columns: || and CONCAT
There are two ways to join strings, and they disagree about NULL:
SELECT TRIM(full_name) || ' <' || TRIM(email) || '>' AS with_pipes,
CONCAT(TRIM(full_name), ' <', TRIM(email), '>') AS with_concat
FROM leads
ORDER BY lead_id;| with_pipes | with_concat |
|---|---|
| Ana Lopez Ana.Lopez@Acme.io | Ana Lopez Ana.Lopez@Acme.io |
| Ben Ito ben@birch.co | Ben Ito ben@birch.co |
| Cara Diaz CARA@cobalt.dev | Cara Diaz CARA@cobalt.dev |
| NULL | Dev Rao <> |
With ||, anything combined with NULL is NULL, so Dev's label disappears. CONCAT treats NULL as an empty string. Choose on purpose, or handle the gap with COALESCE.
|| works in DuckDB, Postgres, SQLite and Oracle. SQL Server uses +. In MySQL, || means OR by default, and its CONCAT returns NULL if any argument is NULL.
REPLACE
REPLACE(text, find, replace_with) swaps every occurrence of one substring for another:
SELECT lead_id,
REPLACE(TRIM(full_name), ' ', ' ') AS clean_name,
REPLACE(plan_code, '-', ' / ') AS readable_plan
FROM leads
ORDER BY lead_id;| lead_id | clean_name | readable_plan |
|---|---|---|
| 1 | Ana Lopez | PRO / 2025 / M |
| 2 | Ben Ito | BASIC / 2024 / Y |
| 3 | Cara Diaz | PRO / 2025 / Y |
| 4 | Dev Rao | TEAM / 2025 / M |
This fixes Cara's name. Three spaces in a row would leave two behind; a regular expression handles that.
LIKE and ILIKE: pattern matching
LIKE matches a pattern with two wildcards: % for any run of characters (including none) and _ for exactly one character. It is case-sensitive in DuckDB and Postgres:
SELECT lead_id, city
FROM leads
WHERE city LIKE 'A%';| lead_id | city |
|---|---|
| 3 | AUSTIN |
Only the upper-case Austin matches. ILIKE is the case-insensitive version:
SELECT lead_id, city
FROM leads
WHERE city ILIKE 'a%';| lead_id | city |
|---|---|
| 1 | austin |
| 3 | AUSTIN |
ILIKE exists in DuckDB, Postgres and Snowflake but not in MySQL or SQL Server, where LIKE is usually case-insensitive already (it depends on the column's collation). The portable version is LOWER(city) LIKE 'a%'. Use _ when the length matters: '___ %' matches a first word of exactly three letters.
The trailing space trap
WHERE email LIKE '%.co' returns no rows on this table, even though Ben's email clearly ends in .co. The stored value ends in .co with a space, and % cannot match past the end. Trim first:
SELECT lead_id, email
FROM leads
WHERE TRIM(email) LIKE '%.co';| lead_id | |
|---|---|
| 2 | ben@birch.co |
When a text filter returns fewer rows than expected, suspect hidden spaces first. AI-written queries often skip the TRIM because the sample rows looked clean.
Regular expressions, briefly
When a pattern is too complex for LIKE, use a regular expression. DuckDB has REGEXP_MATCHES (true or false), REGEXP_EXTRACT (the matching part) and REGEXP_REPLACE:
SELECT plan_code,
REGEXP_EXTRACT(plan_code, '[0-9]{4}') AS plan_year,
REGEXP_MATCHES(plan_code, '-M$') AS is_monthly
FROM leads
ORDER BY lead_id;| plan_code | plan_year | is_monthly |
|---|---|---|
| PRO-2025-M | 2025 | true |
| BASIC-2024-Y | 2024 | false |
| PRO-2025-Y | 2025 | false |
| TEAM-2025-M | 2025 | true |
Postgres uses plan_code ~ '-M$' to test and SUBSTRING(plan_code FROM '[0-9]{4}') to extract. In both, REGEXP_REPLACE replaces only the first match unless you pass the 'g' flag.
STRING_AGG: group by and concatenate strings
STRING_AGG is an aggregate: it joins the values of a group into one string, the way SUM adds them.
SELECT SPLIT_PART(plan_code, '-', 1) AS tier,
COUNT(*) AS leads,
STRING_AGG(TRIM(full_name), ', ' ORDER BY lead_id) AS names
FROM leads
GROUP BY SPLIT_PART(plan_code, '-', 1)
ORDER BY tier;| tier | leads | names |
|---|---|---|
| BASIC | 1 | Ben Ito |
| PRO | 2 | Ana Lopez, Cara Diaz |
| TEAM | 1 | Dev Rao |
Without the inner ORDER BY, name order is not guaranteed. This syntax works in DuckDB and Postgres. SQL Server writes STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY lead_id), and MySQL uses GROUP_CONCAT(name ORDER BY lead_id SEPARATOR ', '). For more on grouping, see SQL GROUP BY.
FAQ
How do I capitalise the first letter of each word?
Postgres and Oracle have INITCAP('ana lopez'), which returns Ana Lopez. DuckDB has no INITCAP.
Why does my join on a text column miss rows?
Usually case or spaces. Clean both keys the same way (LOWER(TRIM(...))) before joining. See how to join two tables.
Is SUBSTRING the same as SUBSTR?
In DuckDB, Postgres and MySQL, yes. Oracle only has SUBSTR and SQL Server only SUBSTRING.
Text cleaning is most of the work in a real data import. You can practise these functions on a deliberately messy CRM export on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Find a customer in the messy CRM
- Names with a double space inside
- One phone number, two contacts
- Duplicate contacts on one line each