SUBSTRING

Functions Buy Me A Coffee

Extract a portion of a string starting at a given position, optionally for a given length.

Extracts a portion of a string by position or length. Variations like SUBSTR, SUBSTRING, and MID exist across databases. Handles out-of-bounds gracefully in most databases (returns empty string or NULL). 1-indexed in most SQL dialects.

Compatibility

SQL SUBSTRING Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all SUBSTRING(str, pos, len) and SUBSTR() are synonyms. Position is 1-based. If pos is negative, counts from the end of the string.
PostgreSQL Native all SUBSTRING(str FROM pos FOR len) is the SQL-standard form. SUBSTRING(str, pos, len) also works. Supports regex: SUBSTRING(str FROM pattern) extracts the first match.
SQL Server Native all SUBSTRING(str, start, length). 1-based position. All three arguments are required. No negative indexing from end — use LEN() arithmetic instead.
Oracle Native all SUBSTR(str, position, length). Both SUBSTR and SUBSTRING work. Position is 1-based; negative position counts from the end.
SQLite Native all SUBSTR(str, start, length). 1-based. Negative start counts from the end. SUBSTRING is also accepted as a synonym.

Details

Function name is the main portability trap: MySQL/SQL Server/PostgreSQL use SUBSTRING, Oracle prefers SUBSTR (though both work on PG and Oracle). The SQL-standard form is SUBSTRING(str FROM pos FOR len) — only PostgreSQL supports it. PostgreSQL uniquely supports regex extraction with SUBSTRING(str FROM pattern).

Standard Syntax

SELECT SUBSTRING(column, start_position, length) FROM tbl;

Version Support

MySQL: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

SUBSTRING(str, pos, len) and SUBSTR() are synonyms. Position is 1-based. If pos is negative, counts from the end of the string.

SELECT SUBSTRING('Hello World', 7, 5); -- 'World' SELECT SUBSTRING('Hello World', -5); -- 'World' (from end) SELECT MID('Hello World', 7, 5); -- 'World' (synonym)

PostgreSQL Native syntax

SUBSTRING(str FROM pos FOR len) is the SQL-standard form. SUBSTRING(str, pos, len) also works. Supports regex: SUBSTRING(str FROM pattern) extracts the first match.

SELECT SUBSTRING('Hello World' FROM 7 FOR 5); -- 'World' SELECT SUBSTRING('Hello World', 7, 5); -- 'World' SELECT SUBSTRING('abc123' FROM '[0-9]+'); -- '123' (regex)

SQL Server Native syntax

SUBSTRING(str, start, length). 1-based position. All three arguments are required. No negative indexing from end — use LEN() arithmetic instead.

SELECT SUBSTRING('Hello World', 7, 5); -- 'World' -- Last 5 chars: SELECT SUBSTRING(col, LEN(col) - 4, 5) FROM tbl;

Oracle Native syntax

SUBSTR(str, position, length). Both SUBSTR and SUBSTRING work. Position is 1-based; negative position counts from the end.

SELECT SUBSTR('Hello World', 7, 5) FROM DUAL; -- 'World' SELECT SUBSTR('Hello World', -5) FROM DUAL; -- 'World'

SQLite Native syntax

SUBSTR(str, start, length). 1-based. Negative start counts from the end. SUBSTRING is also accepted as a synonym.

SELECT SUBSTR('Hello World', 7, 5); -- 'World' SELECT SUBSTR('Hello World', -5); -- 'World'