SQL String Functions Cheat Sheet: 14 Tasks Across 4 Dialects
Concatenation, trimming, substring, split-to-rows, regex replace, and padding — the string operations you write weekly, mapped with working syntax for PostgreSQL, MySQL, BigQuery, and SQL Server.
Quick Answer
- Concat:
||in PostgreSQL/BigQuery/SQL Server,CONCAT()in MySQL — CONCAT_WS skips NULLs on PG/MySQL. - Substring:
SUBSTRING(s FROM 1 FOR 3)is the standard form and works nearly everywhere; note all engines are 1-indexed (except MySQL's SUBSTRING with negative positions). - Split to rows: PostgreSQL
unnest(string_to_array(s, ',')), MySQLJSON_TABLE, BigQuerySPLIT(s, ',')with UNNEST, SQL ServerSTRING_SPLIT.
The Master Table
| Task | PostgreSQL | MySQL 8 | BigQuery | SQL Server |
|---|---|---|---|---|
| Concatenate | a || b | CONCAT(a, b) | CONCAT(a, b) | a + b or CONCAT(a, b) |
| Concat, NULL-safe | CONCAT_WS('-', a, b) | CONCAT_WS('-', a, b) | COALESCE(a,'') || b | CONCAT(a, b) (skips NULLs) |
| Length | LENGTH(s) (chars), OCTET_LENGTH | CHAR_LENGTH(s) | LENGTH(s) | LEN(s) (trailing spaces ignored) |
| Uppercase / lowercase | UPPER/LOWER | UPPER/LOWER | UPPER/LOWER | UPPER/LOWER |
| Trim (both sides) | TRIM(s) | TRIM(s) | TRIM(s) | TRIM(s) |
| Trim specific chars | TRIM('x' FROM s) | TRIM(BOTH 'x' FROM s) | TRIM(s, 'x') | TRIM('x' FROM s) |
| Substring | SUBSTRING(s FROM 2 FOR 3) | SUBSTRING(s, 2, 3) | SUBSTR(s, 2, 3) | SUBSTRING(s, 2, 3) |
| Position of substring | POSITION('x' IN s) | LOCATE('x', s) or INSTR | STRPOS(s, 'x') | CHARINDEX('x', s) |
| Replace all | REPLACE(s, old, new) | REPLACE(s, old, new) | REPLACE(s, old, new) | REPLACE(s, old, new) |
| Regex replace | REGEXP_REPLACE(s, p, r, 'g') | REGEXP_REPLACE(s, p, r) | REGEXP_REPLACE(s, p, r) | No native — STRING_SPLIT+STRING_AGG hack (2022+) |
| Left/right padding | LPAD(s, 5, '0') | LPAD(s, 5, '0') | LPAD(s, 5, '0') | FORMAT / RIGHT('00000' + s, 5) |
| Reverse | REVERSE(s) | REVERSE(s) | REVERSE(s) | REVERSE(s) |
| Split to array | STRING_TO_ARRAY(s, ',') | No array type — use JSON | SPLIT(s, ',') | No array — use STRING_SPLIT to rows |
| Split to rows | unnest(STRING_TO_ARRAY(s, ',')) | JSON_TABLE(s, '$[*]') hack | UNNEST(SPLIT(s, ',')) | STRING_SPLIT(s, ',') |
| Aggregate strings | STRING_AGG(s, ',') | GROUP_CONCAT(s SEPARATOR ',') | STRING_AGG(s, ',') | STRING_AGG(s, ',') |
Split-to-Rows: The Pattern You Will Actually Use
CSV-in-a-column ("tags" = 'sql,indexes,perf') shows up in every messy import. PostgreSQL and BigQuery make it one call:
SELECT id, TRIM(tag) AS tag FROM posts, unnest(STRING_TO_ARRAY(tags, ',')) AS tag;
MySQL 8 gets there through JSON: if the string is valid JSON array (['a','b']), JSON_TABLE(tags, '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')) expands it to rows. For plain comma strings, MySQL 8.0.17+ can use a recursive CTE, or you normalize at write time — which is the honest recommendation for MySQL users anyway.
Padding and Zero-Fill: Report Formatting
Invoice numbers as INV-00042: 'INV-' || LPAD(id::text, 5, '0') in PostgreSQL; CONCAT('INV-', LPAD(id, 5, '0')) in MySQL; cast trick in SQL Server. One caveat that bites: LPAD does not truncate by default in PostgreSQL (it does — to length, actually: LPAD('123456', 5, '0') returns '12345'), so pad only after you are sure the value fits.
Case-Insensitive Matching Without Regex
WHERE LOWER(email) = LOWER(?) is the portable pattern, but it defeats regular indexes on email. The index-friendly alternatives: PostgreSQL WHERE email ILIKE ? with a trigram index (CREATE EXTENSION pg_trgm; CREATE INDEX ON users USING gin (email gin_trgm_ops);), MySQL WHERE email = ? with a case-insensitive collation (utf8mb4_0900_ai_ci is the MySQL 8 default — matching is already case-insensitive), SQL Server with a CI collation. The lesson generalizes: check whether your column is already case-insensitive before wrapping it in LOWER().
Cleaning Real Data: A Recipe
An import arrives with padded, mixed-case, double-spaced names. The pipeline that fixes it in order: TRIM(REGEXP_REPLACE(LOWER(name), '\s+', ' ', 'g')) — lowercase, collapse internal whitespace runs to single spaces, then trim the ends. On MySQL, REGEXP_REPLACE needs no flags; on PostgreSQL, forgetting the 'g' flag replaces only the first occurrence, which is the number one gotcha with that function: PCRE-style semantics treat it as "replace one". Verify with a SELECT before you UPDATE.
Try It Yourself
Describe the transformation — "split the tags column on commas into one row per tag" — and our SQL generator writes the dialect-correct function chain, including the JSON_TABLE route for MySQL.