string
SUBSTRING
Extracts a substring by 1-based start position and optional length. All three dialects support the comma-argument form; Postgres also accepts the SQL-standard FROM/FOR clauses and POSIX regex extraction.
SUBSTRING(string FROM start FOR length) | SUBSTRING(string, start, length)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| FROM n | Start position (1-based) |
| FOR len | Number of characters to return |
| SUBSTRING(str FROM pattern) | Postgres: regex extraction |
| SUBSTR | Alias supported in SQLite/MySQL |
Examples
SELECT SUBSTRING('hello world', 1, 5);Returns 'hello'
SELECT SUBSTRING('hello world' FROM 7);Postgres/SQL-standard: returns 'world' (from position 7 to end)
SELECT SUBSTRING(email, POSITION('@' IN email) + 1) FROM users;Extract domain portion of each email
SELECT SUBSTRING('abc123' FROM '[0-9]+');Postgres regex extraction — returns '123'
Dialect notes / Gotcha
Positions are 1-based, not 0-based. A negative start position is undefined in MySQL/Postgres; use RIGHT() or arithmetic with LENGTH() instead.
Related functions
LEFT / RIGHT
LEFT returns the first n characters, RIGHT returns the last n characters. Available in Postgres and MySQL. SQLite has no LEFT/RIGHT — use SUBSTR(s, 1, n) for LEFT and SUBSTR(s, -n) for RIGHT.35+; a portable fallback is SUBSTRING(str, 1, n) or SUBSTRING(str, LENGTH(str)-n+1).
LENGTH / CHAR_LENGTH
Returns the number of characters in a string (CHAR_LENGTH) or the number of bytes (LENGTH in MySQL). Postgres and SQLite LENGTH return character count for text, matching CHAR_LENGTH.
POSITION / INSTR
Returns the 1-based index of the first occurrence of substr in string, or 0 if not found. POSITION is SQL-standard (Postgres/MySQL); INSTR is the MySQL/SQLite shorthand.
CONCAT
Joins two or more strings end-to-end and returns a single string. CONCAT is portable across MySQL, Postgres 9.1+, and SQLite 3.44+; the standard || operator works in Postgres and SQLite but not MySQL unless PIPES_AS_CONCAT is set.
REPLACE
Replaces every occurrence of from_str inside source with to_str. Case-sensitive in Postgres and SQLite; MySQL follows the column's collation.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet