On this page
Syntax
LEFT(string, n)LEFT(expression, n) | Parameter | Type | Required | Description |
|---|---|---|---|
string |
string or expression | yes | The source text you want to take characters from. Any value that resolves to a string works, including a column, a literal or the result of another function. |
n |
integer | yes | How many characters to take from the start of the string. If n is larger than the length of the string the whole string comes back, and if n is 0 the result is an empty string. |
How it works
LEFT() takes two arguments, a string and a number, and returns the first n characters of that string reading from the left. It is the natural partner of RIGHT(), which does the same thing from the other end, and it is one of the handiest tools for slicing fixed width prefixes such as area codes, country codes and product prefixes.
Behind the scenes LEFT(str, n) is exactly equivalent to SUBSTRING(str, 1, n). SQL starts counting characters at position 1, so asking for the first n characters is the same as asking for n characters starting at position 1. If you ever work on an engine that lacks LEFT(), that substring form is a drop in replacement.
One important portability note: SQLite does not have a LEFT() function at all. On SQLite you use SUBSTR(str, 1, n) instead, which is why the function list on this page shows MySQL, PostgreSQL and SQL Server but not SQLite. See the full SQL functions reference for the engines each function supports.
Examples
Take the first N characters of a string
SELECT LEFT('CodeWithSQL', 4) AS first_four;first_four .......... Code
Extract an area code from a phone number
SELECT phone,
LEFT(phone, 3) AS area_code
FROM customers;phone | area_code .............|.......... 415 555 0182 | 415 212 555 0110 | 212 312 555 0148 | 312
Pull a country prefix from a product code
SELECT product_code,
LEFT(product_code, 2) AS country_prefix
FROM products;product_code | country_prefix .............|............... US1043 | US UK0928 | UK DE5521 | DE
Group customers by the first letter of their name
SELECT LEFT(name, 1) AS initial,
COUNT(*) AS customers
FROM customers
GROUP BY LEFT(name, 1)
ORDER BY initial;initial | customers ........|.......... A | 42 B | 31 C | 58
LEFT is the same as SUBSTRING from position 1
SELECT LEFT('database', 4) AS with_left,
SUBSTRING('database', 1, 4) AS with_substring;with_left | with_substring ..........|............... data | data
Common mistakes
SELECT LEFT('Hi', 10) AS result;SELECT LEFT('Hi', 10) AS whole_string,
LEFT('Hi', LEAST(10, LENGTH('Hi'))) AS clamped;When n is larger than the length of the string, LEFT() returns the entire string rather than raising an error. This is usually fine, but do not rely on it to validate that a value is at least n characters long.
SELECT LEFT(name, 3) FROM customers;SELECT SUBSTR(name, 1, 3) FROM customers;SQLite does not implement LEFT(). Use SUBSTR(str, 1, n) there. The same substring form also works on MySQL, PostgreSQL and SQL Server, so it is the more portable choice.
SELECT LEFT(name, 3) AS left FROM customers;SELECT LEFT(name, 3) AS name_prefix FROM customers;The word LEFT also appears in LEFT JOIN, so it reads as a keyword in many contexts. The function call itself is fine, but avoid using left as a bare column or alias name; quote it or choose another name to keep parsers happy.
Performance
LEFT() is a lightweight per row operation, so it is cheap to compute on the rows a query already returns. The cost is proportional to the number of rows processed, not to the length of each string, because it only ever copies the first n characters.
The trap is using LEFT() in a WHERE clause, as in WHERE LEFT(code, 2) = 'US'. Wrapping a column in a function makes the predicate non sargable, so the engine cannot use an ordinary index on that column and falls back to scanning every row. A range test such as code LIKE 'US%' stays index friendly and returns the same rows.
When you frequently filter or group by a prefix, consider a functional index on LEFT(col, n) where your engine supports it, or store the prefix in its own indexed column. See the indexing guide for how sargable predicates let the optimiser use an index.
Interview questions
What does LEFT(string, n) return?
It returns the leftmost n characters of the string, counting from the first character at position 1. If n is larger than the length of the string, the whole string is returned.
How is LEFT() related to SUBSTRING()?
LEFT(str, n) is exactly equivalent to SUBSTRING(str, 1, n), because taking the first n characters is the same as taking n characters starting at position 1. The substring form is more portable across engines.
SELECT LEFT('database', 4) AS a,
SUBSTRING('database', 1, 4) AS b;What happens when n is greater than the length of the string?
Nothing errors. LEFT() simply returns the entire string. So LEFT('Hi', 10) returns Hi rather than raising an out of range error.
Does SQLite support LEFT()?
No. SQLite has no LEFT() function. On SQLite you use SUBSTR(str, 1, n) to take the leftmost characters. MySQL, PostgreSQL and SQL Server all provide LEFT() natively.
Why can filtering with LEFT() be slow, and what is the fix?
Wrapping a column in LEFT() inside a WHERE clause makes the condition non sargable, so an ordinary index on the column cannot be used and the engine scans every row. Rewriting LEFT(code, 2) = 'US' as code LIKE 'US%' keeps the predicate index friendly.