SUBSTRING(text, start, length) returns length characters of text, beginning at position start. Positions count from 1, not 0. SQLite and Oracle call it SUBSTR; MySQL, SQL Server and PostgreSQL accept SUBSTRING (MySQL, PostgreSQL and SQLite also accept SUBSTR).
Jump to: syntax by database · sample data · examples · after a character · from the right · edge cases · mistakes · FAQ
SUBSTRING syntax by database
| Database | Syntax | Notes |
|---|---|---|
| MySQL | SUBSTRING(str, pos, len) or SUBSTR(...) | len optional; also SUBSTRING(str FROM pos FOR len); negative pos counts from the end |
| PostgreSQL | SUBSTRING(str FROM pos FOR len), SUBSTRING(str, pos, len) or SUBSTR(...) | len optional; also accepts a regex: SUBSTRING(str FROM 'pattern') |
| SQL Server | SUBSTRING(str, pos, len) | len is required; there is no SUBSTR |
| SQLite | SUBSTR(str, pos, len) or SUBSTRING(...) | len optional; negative pos counts from the end |
| Oracle | SUBSTR(str, pos, len) | len optional; negative pos counts from the end |
The three arguments mean the same everywhere: the text, the 1-based start position, and how many characters to return. Leave the length out (where allowed) to get everything from the start position to the end of the string.
Sample data
Every query below was run on SQLite 3.45 (so it uses SUBSTR), and the output is copied from the real result. The same queries work in MySQL and Oracle, in PostgreSQL for the positive-start examples, and in SQL Server after renaming SUBSTR to SUBSTRING and supplying a length.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, phone TEXT, sku TEXT);
INSERT INTO customers VALUES
(1, 'Ana Torres', '[email protected]', '415-555-0142', 'EL-2041-BK'),
(2, 'Ben Okafor', '[email protected]', '212-555-0199', 'HM-0310-WH'),
(3, 'Chloe Dupont', '[email protected]', '303-555-0111', 'EL-1177-RD'),
(4, 'Dev Patel', '[email protected]', '512-555-0170', 'GD-8890-GR');Examples
1. Get the first N characters
Start at position 1 and ask for 3 characters:
SELECT name, SUBSTR(name, 1, 3) AS first3 FROM customers;name | first3
Ana Torres | Ana
Ben Okafor | Ben
Chloe Dupont | Chl
Dev Patel | DevThis is the answer to "sql get first n characters". (SQL Server, MySQL and PostgreSQL also have LEFT(name, 3), which does the same thing.)
2. Get everything from a position onward
Leave out the length and you get the rest of the string:
SELECT name, SUBSTR(name, 5) AS from5 FROM customers;name | from5
Ana Torres | Torres
Ben Okafor | Okafor
Chloe Dupont | e Dupont
Dev Patel | PatelNotice Chloe: her last name starts at position 7, not 5, so fixed positions only work when the layout is fixed. Example 6 handles variable lengths properly. In SQL Server, where the length is required, pass a number larger than the string, such as SUBSTRING(name, 5, 8000), or use LEN(name).
3. Split a fixed-format code into parts
Product codes like EL-2041-BK have a fixed layout: a two-letter category, a four-digit number and a two-letter colour.
SELECT sku,
SUBSTR(sku, 1, 2) AS category,
SUBSTR(sku, 4, 4) AS num,
SUBSTR(sku, -2) AS colour
FROM customers;sku | category | num | colour
EL-2041-BK | EL | 2041 | BK
HM-0310-WH | HM | 0310 | WH
EL-1177-RD | EL | 1177 | RD
GD-8890-GR | GD | 8890 | GRThe last column uses a negative start, which counts from the end (see from the right).
4. Filter with SUBSTRING in WHERE
You can use it anywhere an expression is allowed. This finds customers with the 415 area code:
SELECT name, SUBSTR(phone, 5, 3) AS exchange
FROM customers
WHERE SUBSTR(phone, 1, 3) = '415';name | exchange
Ana Torres | 555Wrapping a column in a function usually stops the database using an index on it, so on big tables prefer WHERE phone LIKE '415%' when you only need a prefix match.
5. Group by part of a string
SELECT SUBSTR(phone, 1, 3) AS area_code, COUNT(*) AS customers
FROM customers
GROUP BY 1
ORDER BY 1;area_code | customers
212 | 1
303 | 1
415 | 1
512 | 1This pattern (derive a bucket from a string, then GROUP BY it) is how you count rows per area code, per year in a text date, or per SKU prefix.
6. Variable lengths: SUBSTRING plus INSTR
Names and emails are not fixed width, so you need to find the split point first. INSTR(text, 'x') returns the position of the first x (CHARINDEX('x', text) in SQL Server, POSITION('x' IN text) in PostgreSQL; see how to use INSTR). Split a full name at the space:
SELECT name,
SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name,
SUBSTR(name, INSTR(name, ' ') + 1) AS last_name
FROM customers;name | first_name | last_name
Ana Torres | Ana | Torres
Ben Okafor | Ben | Okafor
Chloe Dupont | Chloe | Dupont
Dev Patel | Dev | PatelThe first name is everything before the space (length = position of the space minus 1), and the last name is everything after it (start = position of the space plus 1).
7. Capitalise the first letter
Combine UPPER, LOWER and || concatenation (CONCAT() in MySQL and SQL Server, see concatenating columns):
SELECT name, UPPER(SUBSTR(name, 1, 1)) || LOWER(SUBSTR(name, 2)) AS tidy
FROM customers LIMIT 2;name | tidy
Ana Torres | Ana torres
Ben Okafor | Ben okaforIt capitalises only the first letter of the whole string, as designed. Title-casing every word needs a loop or a database-specific function such as PostgreSQL's INITCAP.
Get the text after (or before) a character
This is the most common real-world use of SUBSTRING: pull the domain out of an email, or the extension out of a filename. Find the character with INSTR, then slice from the next position:
SELECT email,
INSTR(email, '@') AS at_pos,
SUBSTR(email, INSTR(email, '@') + 1) AS domain,
SUBSTR(email, 1, INSTR(email, '@') - 1) AS username
FROM customers;email | at_pos | domain | username
[email protected] | 11 | acme.com | ana.torres
[email protected] | 4 | globex.io | ben
[email protected] | 8 | initech.org | chloe.d
[email protected] | 4 | acme.com | devOnce you have the domain you can analyse it. Which companies have the most customers?
SELECT SUBSTR(email, INSTR(email, '@') + 1) AS domain, COUNT(*) AS n
FROM customers
GROUP BY domain
ORDER BY n DESC, domain;domain | n
acme.com | 2
globex.io | 1
initech.org | 1If a row might not contain the character, INSTR returns 0 and the expression returns the wrong text, so guard with CASE WHEN INSTR(email, '@') > 0 THEN ... END or filter with WHERE email LIKE '%@%'. For splitting a string into many pieces see how to split a string in MySQL.
Get characters from the right
MySQL, SQLite and Oracle let you pass a negative start position, which counts backwards from the end of the string:
SELECT name,
SUBSTR(name, -5) AS last5,
SUBSTR(name, -5, 3) AS from_minus5_len3
FROM customers;name | last5 | from_minus5_len3
Ana Torres | orres | orr
Ben Okafor | kafor | kaf
Chloe Dupont | upont | upo
Dev Patel | Patel | PatSUBSTR(name, -5) means "the last five characters". The second form starts five characters from the end and then reads three forward. SQL Server and PostgreSQL do not treat a negative start this way; use RIGHT(name, 5) there (it also works in MySQL).
Edge cases worth knowing
All of these were run on SQLite 3.45; other databases differ at the margins, so test yours.
SELECT SUBSTR('Hello', 2, 100), -- length past the end
SUBSTR('Hello', 10), -- start past the end
SUBSTR('Hello', 0, 3), -- start of 0
SUBSTR(NULL, 1, 2); -- NULL inello | (empty string) | He | NULL- Length longer than the string is fine: you just get what is there.
- Start past the end returns an empty string, not an error.
- Start of 0 behaves oddly. In SQLite
SUBSTR('Hello', 0, 3)returnsHebecause position 0 uses up one of the three characters. Always start at 1. - NULL in, NULL out. Wrap the column in COALESCE if you need a default.
Common mistakes
- Counting from 0. SQL strings start at position 1.
SUBSTR(name, 0, 3)is almost never what you mean. - Mixing up length and end position. The third argument is how many characters to return, not where to stop.
SUBSTR(name, 5, 3)returns 3 characters, not characters 5 to 3. - Forgetting the length in SQL Server.
SUBSTRING(name, 5)is a syntax error there. - Off-by-one around the delimiter. The text before a delimiter is
INSTR - 1long; the text after it starts atINSTR + 1. - Assuming a fixed layout. Hard-coded positions break the moment one row has a longer value. Locate the delimiter with
INSTR/CHARINDEXinstead. - Using it in WHERE on large tables. A function on the column defeats indexes. Use
LIKE 'prefix%'for prefix matches. - Not handling NULL or missing delimiters. NULL returns NULL, and a missing delimiter silently returns the wrong slice.
FAQ
What does SUBSTRING do in SQL?
It returns part of a string, starting at a given position and running for a given number of characters. SUBSTRING('Hello', 2, 3) returns ell.
What is the difference between SUBSTRING and SUBSTR?
Nothing functional. They are two names for the same function. SQL Server only has SUBSTRING, Oracle only has SUBSTR, and MySQL, PostgreSQL and SQLite accept both.
Does SQL SUBSTRING start at 0 or 1?
1. The first character is position 1.
How do I get the last N characters of a string in SQL?
Use RIGHT(col, n) in MySQL, SQL Server and PostgreSQL, or SUBSTR(col, -n) in MySQL, SQLite and Oracle.
How do I get the text after a specific character?
Find the character's position with INSTR (or CHARINDEX) and take SUBSTR(col, position + 1), as in the email domain example above.
How do I use SUBSTRING in SQL Server?
SUBSTRING(expression, start, length), with all three arguments required. For example SELECT SUBSTRING(name, 1, 3) FROM customers;.
Can I use SUBSTRING on numbers or dates?
Most databases convert them to text first, but explicit conversion (CAST(col AS TEXT)) is safer. For dates, use date functions such as EXTRACT or strftime instead of slicing text.
Practice
Try the free interactive SUBSTR lesson and the LIKE lesson in your browser, then work through the SQL practice problems. String cleanup is also a staple of everyday SQL queries, and the SQL cheat sheet lists the other string functions at a glance. Cleaning text before comparing it also helps when you remove duplicates.