Features
SUBSTRING() extracts part of a text value in SQL. Supply a source string, a starting position (1-based), and an optional length. The function returns the requested slice as a new string, letting you isolate codes, dates, or any token inside a column without modifying the original data.
SUBSTRING() extracts a specified slice of text from a string column or literal, returning only the characters you request.
Use SUBSTRING() to isolate IDs, date parts, file extensions, or any token inside a longer value without updating the stored data.
The portable form is SUBSTRING(source FROM start FOR length); most engines also accept SUBSTRING(source, start, length).
Start positions are 1-based. A start of 1 returns the first character; negative starts count from the end in databases that allow it.
Yes. When length is omitted, SUBSTRING() returns every character from the start position to the end of the string.
Pass the column, start, and length. Example: SUBSTRING(order_id FROM 1 FOR 3) returns the first three characters of every order_id.
Combine LENGTH() with SUBSTRING(): SUBSTRING(sku FROM LENGTH(sku)-3) returns the last four characters.
Wrap SUBSTRING() inside WHERE and compare with LIKE. Example: WHERE SUBSTRING(email FROM 1 FOR 3) = 'dev'.
SUBSTRING() is CPU-only and blocks index use on the operated column, so avoid it in WHERE for large tables or create computed indexes.
Nest SUBSTRING() inside CONCAT(), REPLACE(), or TRIM() to clean and reassemble strings in a single query step.
Core behavior is ANSI, but argument order, negative indexes, and alias names (e.g., SUBSTR) vary. Check your engine’s docs.
Validate string length before slicing, document 1-based positions in comments, and create indexed computed columns for heavy filters.
See code samples below for real-world patterns.
Review the pitfalls section for off-by-one errors, NULL handling, and index misuse.
SUBSTRING() is a reliable, portable way to extract text slices. Know the 1-based index, test edge cases, and optimize when used in filters.
Implicit casts let SUBSTRING() operate on numeric columns, but casting to TEXT first is clearer and avoids engine-specific behavior.
SUBSTRING() stops at the string end without error, returning the available characters.
Yes in most engines; SUBSTR is an alias. Always verify argument order and zero-based quirks.
Use SUBSTRING(col FROM 2). Omitting length returns the rest of the string.
.avif){kind=logo}
