SQL String Functions with Examples

Every string task in SQL boils down to four actions: join, cut, clean and search. The cheat sheet of the core functions:

Let's walk through each group with examples from a delivery service database — every query can be run right inside the article.

Join: CONCAT and CONCAT_WS

CONCAT merges any number of strings into one:

MySQL 8.1
SELECT CONCAT(name, ' — ', category) AS title
FROM products
ORDER BY product_id
LIMIT 3;

When the separator repeats, CONCAT_WS ("with separator") is handier: the first argument is the separator, the rest are the parts to join:

MySQL 8.1
SELECT CONCAT_WS(', ', country, acquisition_channel) FROM users;
-- 'Germany, Ads'

PostgreSQL and standard SQL also offer the || operator: name || ' — ' || category. In MySQL || means logical OR by default, so CONCAT is the safer choice for portable code.

The NULL Trap in Concatenation

Join functions treat NULL differently, and this is a popular source of empty strings in reports:

• MySQL: CONCAT('a', NULL, 'b') returns NULL — a single empty value "poisons" the whole result.
• PostgreSQL: CONCAT('a', NULL, 'b') returns ab — NULL is simply skipped. The || operator, however, behaves like MySQL: 'a' || NULL yields NULL.

If the columns you join may contain gaps, wrap them in COALESCE — or use CONCAT_WS, which ignores NULL in both DBMS.

What does CONCAT('Order #', order_id, ' with ', promo_code) return in MySQL when promo_code is NULL?

Join a Group of Rows: GROUP_CONCAT and STRING_AGG

A separate superpower is collecting values from multiple table rows into one string. Let's build the product list of each category:

In MySQL this is the job of GROUP_CONCAT:

MySQL 8.1
SELECT category, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS product_list
FROM products
GROUP BY category
ORDER BY category;

In PostgreSQL the same task is solved by STRING_AGG:

PostgreSQL 17.5
SELECT category, STRING_AGG(name, ', ' ORDER BY name) AS product_list
FROM products
GROUP BY category
ORDER BY category;

The functions are named differently — GROUP_CONCAT in MySQL, STRING_AGG in PostgreSQL — but do the same thing. They are aggregate functions like SUM or COUNT, so they work together with GROUP BY.

Which function collects values from multiple table rows into one comma-separated list in PostgreSQL?

Cut: SUBSTRING, LEFT, RIGHT

SUBSTRING(string, position, length) cuts a fragment out of a string; character numbering starts at one. Combined with POSITION, which finds where a substring starts, you get practical tricks — for example, extracting the domain from an email:

MySQL 8.1
SELECT email, SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM users
ORDER BY user_id
LIMIT 3;

POSITION('@' IN email) returns the position of the @ character, and SUBSTRING without the third argument takes everything from that position to the end of the string. Try it yourself: extract the login from the email — the part before the @. You will need the third argument and POSITION('@' IN email) - 1.

For the edges of a string there are shortcuts: LEFT(string, n) — the first n characters, RIGHT(string, n) — the last n.

Clean: TRIM and REPLACE

TRIM removes spaces from both ends of a string — first aid when dealing with human-entered data:

MySQL 8.1
SELECT TRIM('  SUMMER20  ');
-- 'SUMMER20'

REPLACE(string, search, replacement) replaces every occurrence of a substring:

MySQL 8.1
SELECT phone, REPLACE(REPLACE(phone, '-', ''), ' ', '') AS normalized
FROM users
WHERE user_id IN (1, 4)
ORDER BY user_id;

Nested REPLACE calls are the usual trick for normalizing phone numbers and SKUs before comparison. For pattern-based replacements there is REGEXP_REPLACE — patterns are covered in detail in the lesson The REGEXP operator.

A special cleaning case is empty strings and strings of spaces standing in for NULL. That is solved by the COALESCE(NULLIF(TRIM(...), ''), ...) combo we dissected in the COALESCE article.

Case and Length: UPPER, LOWER, LENGTH

UPPER and LOWER convert a string to upper and lower case. The classic use is case-insensitive comparison: LOWER(email) = LOWER('User@Mail.com').

String length has a nuance worth knowing in advance:

• MySQL: LENGTH() counts bytes, while CHAR_LENGTH() counts characters. For Latin text they coincide, but a Cyrillic letter takes two bytes in UTF-8: LENGTH('привет') returns 12 while CHAR_LENGTH('привет') returns 6.
• PostgreSQL: LENGTH() counts characters right away — LENGTH('привет') returns 6.

If you need the length as a human sees it — use CHAR_LENGTH: it works the same in both DBMS.

MySQL vs PostgreSQL: The Difference Table

What's Next

• how the string data type works — in the lesson String data type;
• pattern search — in the lessons The LIKE operator and The REGEXP operator;
• quick function references — in the handbook: CONCAT, SUBSTRING, TRIM, REPLACE, GROUP_CONCAT;
• practice string tasks — in the SQL trainer.