SUBSTRINGFunctions
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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 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 ✓ Supported 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 ✓ Supported all SUBSTRING(str, start, length). 1-based position. All three arguments are required. No negative indexing from end — use LEN() arithmetic instead.
Oracle ✓ Supported all SUBSTR(str, position, length). Both SUBSTR and SUBSTRING work. Position is 1-based; negative position counts from the end.
SQLite ✓ Supported 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: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

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

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

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

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

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'