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.

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
-- 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 toolstring_agg and array_agg are the aggregation counterparts and take the place of application-side joining.
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
| Task | Standard / PostgreSQL | MySQL | SQL Server |
|---|---|---|---|
| Concatenate | `a | b` | |
| NULL-safe join | concat_ws, ` | ` returns NULL | |
| Substring | SUBSTRING(a FROM 1 FOR 3) | SUBSTRING(a,1,3) | SUBSTRING(a,1,3) |
| Length (chars) | LENGTH / CHAR_LENGTH | CHAR_LENGTH | LEN (trailing spaces ignored) |
| Position | POSITION(x IN a) / STRPOS | LOCATE / INSTR | CHARINDEX |
| Case-insensitive | ILIKE, citext | default collation | default collation |
| Regex | ~, ~*, regexp_replace | REGEXP_LIKE, REGEXP_REPLACE | none built in |
| Predicate | Uses B-tree index | Notes |
|---|---|---|
col = 'x' | Yes | Exact match, collation-sensitive |
col LIKE 'x%' | Yes | Prefix form only, text_pattern_ops for non-C collation |
col LIKE '%x' / %x%' | No | Needs trigram or full-text index |
LOWER(col) = 'x' | Only with expression index | Functional index required |
col ILIKE 'x%' | Only with trigram / lower() index | Case folding blocks the sort order |
col ~ 'regex' | No | Index-friendly design is usually different |
Practical edge cases and deep dives
1. || versus CONCAT when a NULL appears
SELECT 'Kim' || NULL; -- NULL
SELECT CONCAT('Kim', NULL); -- 'Kim' in MySQL, NULL in PostgreSQL
SELECT CONCAT_WS(' ', 'Kim', NULL, 'Seoul'); -- 'Kim Seoul' everywhereA 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.
2. Left-anchored prefix versus wildcard search
-- 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:
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
-- 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
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".
-- 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.
-- 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
-- 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
| Symptom | Cause | Fix |
|---|---|---|
Concatenated string is NULL | One operand is NULL with ` | |
| `42883 operator does not exist: text | integer` | |
| Truncated result, no error | Silent truncation in MySQL non-strict mode | Set STRICT_ALL_TABLES, widen the column |
LIKE '100%' matches too much | % treated as wildcard | Escape the user input |
Search slow on large %term% | Wildcard on both sides | Trigram or full-text index |
| Case-sensitive match in MySQL, not in PostgreSQL | Collation differences | Normalise with lower() + functional index |
SUBSTRING off by one | 1-based positions in SQL | Subtract 1 only when mapping to app indices |
LEN() / LENGTH() disagrees with app | Bytes versus characters | Use CHAR_LENGTH for character counts |
| Trailing spaces fail a unique index | CHAR padding semantics | Switch to VARCHAR, trim on write |
regexp_replace returns unchanged text | Pattern anchored by default in one engine and not another | Check anchor and flags per engine |
string_agg raises 42883 | Numeric argument | Cast: string_agg(x::text, ',') |
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
SQL JSON and JSONB: Functions, Operators, and Indexing Strategies
Why jsonb is the default for queryable documents, when containment can use a GIN index, and the expression-index fixes for scalar lookups.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.