SUBSTRING()

Syntax

How it works

SUBSTRING() returns a slice of a string. You give it the source text, the position to start at, and optionally how many characters to take. It is the standard way to pull a fixed piece out of a larger value, such as the first three letters of a code or the year out of a formatted date.

The single most important rule is that SUBSTRING() is 1 based: position 1 is the first character, position 2 is the second, and so on. There is no position 0. If you omit the length, most engines return everything from the start position to the end of the string. SQL Server is the exception and expects all three arguments.

SUBSTR() is a common alias for the same operation in MySQL, Oracle and SQLite, so SUBSTR(name, 1, 3) and SUBSTRING(name, 1, 3) behave the same on those engines. PostgreSQL also accepts the SQL standard keyword form SUBSTRING(string FROM start FOR length), and a regex form SUBSTRING(string FROM pattern) that returns the first match of a pattern. To locate the start position dynamically, pair it with POSITION(), and use LEFT() or RIGHT() when you only need one end of the string.

Examples

Take the first 3 characters

-- Extract a country code prefix from a product SKU
SELECT sku,
       SUBSTRING(sku, 1, 3) AS prefix
FROM products;

sku        | prefix
-----------+-------
 UAE100245 | UAE
 KSA773100 | KSA
 EGY000581 | EGY

Take a slice from the middle

-- Characters 4 through 6 (start at position 4, length 3)
SELECT SUBSTRING('UAE100245', 4, 3) AS middle;

middle
------
100

From a position to the end (omit length)

-- Everything from position 4 onward
SELECT SUBSTRING('UAE100245', 4) AS tail;

tail
------
100245

Using the SUBSTR alias

-- SUBSTR is identical to SUBSTRING on MySQL, Oracle and SQLite
SELECT SUBSTR(email, 1, 5) AS start_of_email
FROM customers;

start_of_email
--------------
 omara
 laila
 hasan

PostgreSQL keyword form

-- SQL standard FROM ... FOR ... syntax
SELECT SUBSTRING('2026-07-05' FROM 1 FOR 4) AS year;

year
----
2026

PostgreSQL regex form

-- Return the first run of digits in the string
SELECT SUBSTRING('order#4821 paid' FROM '[0-9]+') AS order_no;

order_no
--------
4821

Common mistakes

-- Assuming positions start at 0 like an array
-- This skips the first character you wanted
SELECT SUBSTRING('hello', 0, 3);  -- 'he', not 'hel'

-- Positions are 1 based: 1 is the first character
SELECT SUBSTRING('hello', 1, 3);  -- 'hel'

-- Expecting a negative start to work the same everywhere
-- On SQL Server this does NOT count from the end
SELECT SUBSTRING('hello', -3, 3);  -- '' on SQL Server

-- MySQL and SQLite count from the end with a negative start
SELECT SUBSTRING('hello', -3);      -- 'llo' on MySQL
-- On SQL Server, use LEN() to compute the position instead
SELECT SUBSTRING('hello', LEN('hello') - 2, 3);  -- 'llo'

-- SQL Server requires the length argument
SELECT SUBSTRING('hello', 2) AS rest;  -- syntax error

-- Provide a length, or use a large number to reach the end
SELECT SUBSTRING('hello', 2, LEN('hello')) AS rest;  -- 'ello'

Performance

SUBSTRING() itself is cheap: it is a straightforward character copy over one value. The performance cost shows up when you wrap a column in it inside a WHERE clause, because WHERE SUBSTRING(code, 1, 3) = 'UAE' is not sargable and forces a full scan. The database cannot use a normal index on code when the column is buried inside a function.

When you filter on a fixed prefix, prefer a range or LIKE predicate such as code LIKE 'UAE%', which can use an index on code. If you must filter on a computed slice, create a functional or computed column index over the same SUBSTRING() expression so the optimiser can seek it directly.

For large text values, remember that SUBSTRING() counts characters, not bytes, on most engines, so multibyte encodings do the right thing but add a small amount of work. See the indexing guide for why wrapping columns in functions defeats index seeks.

Interview questions

SELECT SUBSTR('hello', 1, 3);      -- MySQL, Oracle, SQLite
SELECT SUBSTRING('hello', 1, 3);   -- all four engines

SELECT SUBSTRING('UAE100245', 4);           -- '100245'
SELECT SUBSTRING('UAE100245', 4, LEN('UAE100245'));  -- SQL Server

SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS local_part
FROM customers;