LEFT()
SQL · String Functions
Theory
LEFT(string, n) returns the first n characters of a string. LEFT('Jaipur', 3) returns 'Jai'. If n is larger than the string length, you simply get the whole string; if n is 0 you get an empty string. It is the same as SUBSTRING(string, 1, n), just shorter to read.
LEFT exists in MySQL, SQL Server, Postgres and Snowflake, but not in Oracle or SQLite, where you write SUBSTR(string, 1, n) instead. It is often used to build short codes and prefixes: UPPER(LEFT(city, 3)) || '-' || id turns Jaipur and id 1 into 'JAI-1'. It is also handy for grouping by a prefix, such as the first two digits of a PIN code (region) or the first letter of a name for an alphabetical index. When the data has leading spaces, apply TRIM first, otherwise the spaces count as characters.
Why we use it
Prefixes carry meaning in many codes: the first letters of a city, the first digits of a PIN code or account number, an initial. LEFT gets them in one short call, which keeps queries readable.
When to use it
Use LEFT for fixed-length prefixes, codes and initials. Where it does not exist (SQLite, Oracle), use SUBSTR(string, 1, n). If the prefix length varies (up to a separator), combine SUBSTRING with INSTR/POSITION instead.
How it works
- Take the string and n
- n > string length? Yes: Return the whole string. No: Take characters 1 to n.
- Leading spaces count as characters
- Return the prefix
Syntax
LEFT(string, n)Example
SELECT LEFT(name, 3) FROM employees;Key points
- Not available in SQLite or Oracle: use SUBSTR(str, 1, n) instead
- Same as SUBSTRING(str, 1, n)
- Returns the whole string if n exceeds its length
- Great for building prefixes and codes
- TRIM first if values may have leading spaces