Built-in functions clean, slice, combine and format text. Names differ between engines more than anywhere else — learn the ideas, then look up the local spelling.

The essentials

Task	SQLite / standard	MySQL	SQL Server	PostgreSQL
Length	`length(s)`	`CHAR_LENGTH(s)` (`LENGTH` = bytes)	`LEN(s)`	`length(s)`
Upper / lower	`upper(s)`, `lower(s)`	same	same	same
Substring	`substr(s, start, len)`	`SUBSTRING(s, start, len)`	`SUBSTRING(s, start, len)`	`substring(s from 2 for 3)`
First / last n	`substr(s, 1, n)`, `substr(s, -n)`	`LEFT(s, n)`, `RIGHT(s, n)`	`LEFT`, `RIGHT`	`left`, `right`
Trim	`trim(s)`, `ltrim`, `rtrim`	same	`TRIM` (2017+), `LTRIM`, `RTRIM`	same
Find position	`instr(s, sub)`	`INSTR(s, sub)` / `LOCATE`	`CHARINDEX(sub, s)`	`position(sub in s)`
Replace	`replace(s, from, to)`	same	same	same
Concatenate	`a \|\| b`, `concat(a, b)`	`CONCAT(a, b)`	`a + b`, `CONCAT`	`a \|\| b`, `concat`
Pad	`printf('%5s', s)`	`LPAD(s, 5, ' ')`	`RIGHT(SPACE(5) + s, 5)`	`lpad(s, 5)`

Slicing and searching

SQL

SELECT email,
       length(email)                              AS len,
       instr(email, '@')                          AS at_pos,
       substr(email, 1, instr(email, '@') - 1)    AS username,
       substr(email, instr(email, '@') + 1)       AS domain,
       substr(email, -2)                          AS last_two
FROM employees
WHERE emp_id IN (1, 8)
ORDER BY emp_id;

Output

email          | len | at_pos | username | domain  | last_two
---------------+-----+--------+----------+---------+---------
asha@corp.in   | 12  | 5      | asha     | corp.in | in
vikram@corp.in | 14  | 7      | vikram   | corp.in | in

Cleaning text

SQL

SELECT '[' || trim('   padded   ') || ']'        AS trimmed,
       ltrim('000742', '0')                      AS no_zeros,
       replace('2024/03/15', '/', '-')           AS iso_date,
       lower('MiXeD CaSe')                       AS lowered;

Output

trimmed  | no_zeros | iso_date   | lowered
---------+----------+------------+-----------
[padded] | 742      | 2024-03-15 | mixed case

Building strings

SQL

SELECT name || ' (' || COALESCE(email, 'n/a') || ')'   AS with_pipe,
       concat(name, ' - ', dept_id)                    AS with_concat,
       concat_ws(', ', name, email, dept_id)           AS with_separator,
       printf('%-6s|%8d', name, salary)                AS formatted
FROM employees
WHERE emp_id IN (7, 10)
ORDER BY emp_id;

Output

with_pipe  | with_concat | with_separator | formatted
-----------+-------------+----------------+----------------
Neha (n/a) | Neha - 2    | Neha, 2        | Neha  |   72000
Dev (n/a)  | Dev -       | Dev            | Dev   |   60000

Aggregating strings

SQL

SELECT dept_id,
       group_concat(name, ', ' ORDER BY name) AS team
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;

Output

dept_id | team
--------+-------------------------
1       | Asha, Karan, Meera, Ravi
2       | Arjun, Neha, Priya
3       | Sara, Vikram

Spellings: STRING_AGG(name, ', ' ORDER BY name) (PostgreSQL, SQLite 3.44+), STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) (SQL Server), GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') (MySQL), LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) (Oracle).

How do you extract the domain from an email in SQL?