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

Filter by Database
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'