Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching

The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.

~4 min readSQL
#SQL#String Functions#Syntax
3D illustration of text tokens passing through concatenation blocks, substring blades and pattern filters into an output row

Quick Summary

String handling in SQL looks portable and is not. The concatenation operator differs by engine (|| in standard SQL and PostgreSQL, CONCAT() everywhere, + only in SQL Server), LIKE and regular expressions behave differently around case and escaping, and implicit padding in CHAR columns breaks equality in ways that produce no error at all. The functions themselves are stable across engines: LENGTH, SUBSTRING, UPPER, LOWER, TRIM, REPLACE, POSITION, and LPAD/RPAD.

The predicate layer matters more than the function layer. LIKE 'abc%' can use a B-tree index because the left-anchored prefix makes the search range known; LIKE '%abc' cannot and scans. A regular expression, a LOWER() wrapper, or a TRIM() wrapper on the indexed column also kills the index unless you build a matching expression index. Case-insensitive matching should be handled by the type or the collation (citext, ILIKE with a trigram index, or a functional index on LOWER(col)), not by scattering UPPER() through every query.

Three practical rules cover most production work. Store email and usernames normalised once at write time instead of normalising on every read. Use concat_ws-style joining when NULLs are possible, because || with one NULL returns NULL for the whole expression. And cast explicitly with substring(... from ... for ...) semantics in mind: string positions are 1-based in SQL, while every mainstream programming language is 0-based.

Basic syntax and query signatures

sql
-- Concatenation SELECT first_name || ' ' || last_name FROM users; -- standard, NULL-propagating SELECT CONCAT(first_name, ' ', last_name) FROM users; -- ignores NULL arguments in MySQL SELECT CONCAT_WS(' ', first_name, last_name) FROM users; -- NULL-safe delimiter join SELECT first_name + ' ' + last_name FROM users; -- SQL Server only -- Extraction: 1-based start position SELECT SUBSTRING(email FROM 1 FOR 3) FROM users; SELECT SUBSTRING(email, 1, 3) FROM users; -- SQL Server / MySQL form SELECT SUBSTR(email, 3) FROM users; -- to end of string SELECT LEFT(email, 3), RIGHT(email, 3) FROM users; -- Length: characters vs bytes SELECT LENGTH('한글'), OCTET_LENGTH('한글'); -- 2, 6 in UTF-8 SELECT CHAR_LENGTH(name), LENGTH(name) FROM users; -- MySQL: characters, bytes -- Case, whitespace, replacement SELECT UPPER(name), LOWER(name), INITCAP(city) FROM users; SELECT TRIM(BOTH ' ' FROM name), LTRIM(name), RTRIM(name) FROM users; SELECT REPLACE(phone, '-', '') FROM users; SELECT POSITION('@' IN email), STRPOS(email, '@') FROM users; SELECT LPAD(id::text, 8, '0'), RPAD(code, 5, 'x') FROM items; -- Pattern matching SELECT * FROM users WHERE email LIKE '%@example.com'; SELECT * FROM users WHERE email ILIKE '%@EXAMPLE.com'; -- PostgreSQL SELECT * FROM users WHERE email ~* '^[a-z0-9._%+-]+@[a-z0-9.-]+$'; SELECT * FROM users WHERE name SIMILAR TO '(Kim|Lee)%'; -- rarely the right tool

string_agg and array_agg are the aggregation counterparts and take the place of application-side joining.

sql
SELECT department, STRING_AGG(name, ', ' ORDER BY name) AS members FROM employees GROUP BY department; SELECT department, ARRAY_AGG(name ORDER BY name) FROM employees GROUP BY department;

Key comparison table

TaskStandard / PostgreSQLMySQLSQL Server
Concatenate`ab`
NULL-safe joinconcat_ws, `` returns NULL
SubstringSUBSTRING(a FROM 1 FOR 3)SUBSTRING(a,1,3)SUBSTRING(a,1,3)
Length (chars)LENGTH / CHAR_LENGTHCHAR_LENGTHLEN (trailing spaces ignored)
PositionPOSITION(x IN a) / STRPOSLOCATE / INSTRCHARINDEX
Case-insensitiveILIKE, citextdefault collationdefault collation
Regex~, ~*, regexp_replaceREGEXP_LIKE, REGEXP_REPLACEnone built in
PredicateUses B-tree indexNotes
col = 'x'YesExact match, collation-sensitive
col LIKE 'x%'YesPrefix form only, text_pattern_ops for non-C collation
col LIKE '%x' / %x%'NoNeeds trigram or full-text index
LOWER(col) = 'x'Only with expression indexFunctional index required
col ILIKE 'x%'Only with trigram / lower() indexCase folding blocks the sort order
col ~ 'regex'NoIndex-friendly design is usually different

Practical edge cases and deep dives

1. || versus CONCAT when a NULL appears

sql
SELECT 'Kim' || NULL; -- NULL SELECT CONCAT('Kim', NULL); -- 'Kim' in MySQL, NULL in PostgreSQL SELECT CONCAT_WS(' ', 'Kim', NULL, 'Seoul'); -- 'Kim Seoul' everywhere

A nullable middle name is enough to blank an entire generated display name. If the column feeds a search index or an email template, NULL propagation is the defect you will find last.

sql
-- Index range scan on idx_users_email (prefix) SELECT id FROM users WHERE email LIKE 'rsh%'; -- Sequential scan, however large the table SELECT id FROM users WHERE email LIKE '%rsh%'; -- Middle-ground: trigram index enables both (PostgreSQL) CREATE EXTENSION pg_trgm; CREATE INDEX idx_users_email_trgm ON users USING gin (email gin_trgm_ops);

For case-insensitive prefix search on a non-C collation, a plain index is not enough; use text_pattern_ops or the trigram route:

sql
CREATE INDEX idx_users_email_lower ON users (lower(email) text_pattern_ops); SELECT id FROM users WHERE lower(email) LIKE 'rsh%';

3. Normalising at write time beats normalising at read time

sql
-- Bad: every read pays for the function, and the index can't be used SELECT * FROM users WHERE lower(email) = lower($1); -- Better: store the normalised form once ALTER TABLE users ADD COLUMN email_norm text; UPDATE users SET email_norm = lower(btrim(email)); CREATE UNIQUE INDEX idx_users_email_norm ON users (email_norm);

The same argument applies to phone numbers: strip separators at insert, store digits as a separate column, and keep the display form for the UI.

4. Character semantics versus byte semantics in slice and pad

sql
SELECT LENGTH('한글'), OCTET_LENGTH('한글'); -- 2, 6 (PostgreSQL) SELECT CHAR_LENGTH('한글'), LENGTH('한글'); -- MySQL: 2, 6 SELECT SUBSTRING('한글' FROM 1 FOR 1); -- character-based: '한'

In MySQL, LENGTH() is bytes; in PostgreSQL, LENGTH() is characters and OCTET_LENGTH() is bytes. A length validator written as LENGTH(name) <= 20 changes meaning depending on the engine, and a VARCHAR(20) column stores 20 characters while an index key is limited in bytes.

5. Escaping % and _ in user input

LIKE treats % and _ as wildcards, so a literal search for "50%" finds everything starting with "50".

sql
-- Escape the pattern, not the data SELECT * FROM products WHERE name LIKE replace(replace($1,'\','\\'),'%','\%') || '%'; -- Or avoid LIKE for exact user input SELECT * FROM products WHERE name = $1;

PostgreSQL's LIKE uses \ as the default escape character; MySQL uses \ unless NO_BACKSLASH_ESCAPES is set; SQL Server uses ESCAPE clauses for anything other than [].

6. CHAR padding breaks equality and LEN()

A CHAR(10) column pads values with spaces. PostgreSQL compares CHAR values blank-padded, so 'abc ' = 'abc' is true there; MySQL and SQL Server also trim or pad depending on the type, and LEN('abc ') returns 3 in SQL Server while DATALENGTH returns 4.

sql
-- Portable comparison: trim explicitly on both sides SELECT * FROM codes WHERE btrim(code) = btrim($1); -- Or store the canonical form ALTER TABLE codes ALTER COLUMN code TYPE varchar(10); UPDATE codes SET code = btrim(code);

7. Splitting, joining and the missing split in standard SQL

sql
-- PostgreSQL: split a delimited column into rows SELECT id, part FROM tags, unnest(string_to_array(tag_list, ',')) AS part; -- MySQL 8.0 has no split function; a recursive CTE or JSON conversion is needed SELECT p.id, JSON_UNQUOTE(JSON_EXTRACT(j, CONCAT('$[', n.i, ']'))) AS part FROM posts p JOIN (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2) n WHERE n.i < JSON_LENGTH(CAST(CONCAT('["', REPLACE(p.tag_list, ',', '","'), '"]') AS JSON));

Storing a comma-separated list is the root cause. A child table with one row per tag removes the query entirely and lets the database index it.

Common mistakes and runtime errors

SymptomCauseFix
Concatenated string is NULLOne operand is NULL with `
`42883 operator does not exist: textinteger`
Truncated result, no errorSilent truncation in MySQL non-strict modeSet STRICT_ALL_TABLES, widen the column
LIKE '100%' matches too much% treated as wildcardEscape the user input
Search slow on large %term%Wildcard on both sidesTrigram or full-text index
Case-sensitive match in MySQL, not in PostgreSQLCollation differencesNormalise with lower() + functional index
SUBSTRING off by one1-based positions in SQLSubtract 1 only when mapping to app indices
LEN() / LENGTH() disagrees with appBytes versus charactersUse CHAR_LENGTH for character counts
Trailing spaces fail a unique indexCHAR padding semanticsSwitch to VARCHAR, trim on write
regexp_replace returns unchanged textPattern anchored by default in one engine and not anotherCheck anchor and flags per engine
string_agg raises 42883Numeric argumentCast: string_agg(x::text, ',')

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.