SUBSTRING()
SQL · String Functions
Theory
SUBSTRING(string, start, length) returns a piece of a string: start is the position of the first character you want (positions start at 1, not 0) and length is how many characters to take. SUBSTRING('2025-04-12', 1, 4) returns '2025' and SUBSTRING('2025-04-12', 6, 2) returns '04'. If you leave out length, you get everything from start to the end of the string.
Names vary: SQLite and Oracle use SUBSTR, SQL Server uses SUBSTRING and requires the length argument, and MySQL and Postgres accept both. The SQL-standard form is SUBSTRING(string FROM start FOR length). SQLite and Oracle also accept a negative start that counts from the end, so SUBSTR(x, -4) gives the last 4 characters. Combined with INSTR or POSITION, SUBSTRING can cut at a character whose position varies, such as everything before the '@' in an email.
Why we use it
Real data often packs several facts into one string: year and month in a date stored as text, a state code inside an ID, a domain inside an email. SUBSTRING pulls out exactly the part you need.
When to use it
Use SUBSTRING when the piece you need is at a known or calculable position. For fixed prefixes or suffixes LEFT/RIGHT read more clearly where available. For real dates, prefer date functions (EXTRACT, YEAR) over cutting text.
How it works
- Take string, start position and optional length
- Start past the end? Yes: Return an empty string. No: Move to the start position.
- Was a length given? Yes: Take that many characters. No: Take everything to the end.
- Return the extracted piece
Syntax
SUBSTRING(string, start, length)Example
SELECT SUBSTRING(name, 1, 4) FROM employees;Key points
- Positions start at 1, not 0
- SQLite and Oracle spell it SUBSTR(); SQL Server needs the length
- Omitting length returns everything to the end of the string
- A negative start counts from the end in SQLite and Oracle
- Combine with INSTR/POSITION to cut at a variable position