SQL Reference ยท Scalar Functions
SQL SUBSTR() and SUBSTRING() functions
Reference for SQL SUBSTR() and SUBSTRING(): extract part of a string by start position and length, negative positions, edge cases and dialect differences.
SUBSTR(s, start, length) returns a piece of a string, beginning at a position and running for a number of characters. Positions are 1-based: the first character is position 1. It is the main tool for cutting fixed-width codes apart, taking prefixes and building values such as initials.
The standard spelling is SUBSTRING(s FROM start FOR length). Most databases also accept a comma form, and several offer the short name SUBSTR. SQLite understands both substr() and substring() (the latter since version 3.34).
SELECT SUBSTR('abcdef', 2, 3) AS piece; -- bcd
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SUBSTR(string, start [, length]) SUBSTRING(string FROM start [FOR length]) SUBSTRING(string, start [, length])
| Name | Description |
|---|---|
string |
The text to cut from. |
start |
Position of the first character to take; 1 is the first character. |
length |
Optional. How many characters to take. Without it, you get everything to the end. |
Returns: the extracted text, or an empty string if nothing is left. NULL if any argument is NULL.
Dialect differences
| Database | Function | Negative start |
Notes |
|---|---|---|---|
| SQLite | SUBSTR, SUBSTRING |
counts from the end | start 0 behaves like a position before the string, so substr('abcdef', 0, 3) is ab |
| PostgreSQL | SUBSTRING(s FROM a FOR n), SUBSTR(s, a, n) |
positions before 1 use up the length | a negative length is an error |
| MySQL | SUBSTRING, SUBSTR, MID |
counts from the end | start 0 gives an empty string |
| SQL Server | SUBSTRING(s, a, n) |
no end-counting | length is required; use RIGHT(s, n) for the end |
| Oracle | SUBSTR(s, a [, n]) |
counts from the end | start 0 is treated as 1 |
-- PostgreSQL SELECT SUBSTRING('abcdef' FROM 2 FOR 3); -- bcd -- SQL Server: length is required SELECT SUBSTRING('abcdef', 2, LEN('abcdef'));
Usage notes
- Start at 1, not 0. Passing 0 gives different results in different databases; avoid it.
- Negative start (SQLite, MySQL, Oracle) counts from the right end:
SUBSTR(s, -3)is the last three characters. PostgreSQL and SQL Server needRIGHT(s, 3)orSUBSTRING(s, LENGTH(s) - 2). - A length longer than the string is fine; you just get what is there.
- A negative length in SQLite takes characters before the start:
substr('abcdef', 2, -1)isa. Other databases error or return an empty string. - Combine with
INSTR()to cut around a delimiter: take up to the first space withSUBSTR(s, 1, INSTR(s, ' ') - 1). If the delimiter is missing,INSTRreturns 0, so check first. - Characters, not bytes, for text values (blobs are measured in bytes in SQLite).
- Cutting on a column in
WHERE(SUBSTR(code, 1, 2) = 'AB') cannot use a normal index; prefix searches withLIKE 'AB%'can.
Related
INSTR(),LENGTH(),TRIM(),||concatenation,LIKE- Lessons: String functions