SQL LEFT Function: Syntax, Examples, and Differences

Formulas & functions · Updated November 22, 2026

SQL’s LEFT function returns a specified number of characters from the beginning of a string. For example, LEFT('Database', 4) returns 'Data' in SQL Server, MySQL, and PostgreSQL; the exact behavior and available syntax can vary by database.

What the SQL LEFT function does

LEFT is a string function: it takes a string and a length, then returns the leftmost characters up to that length. “Left” means the start of the string as it is stored, not the left side of a screen after formatting. The function does not alter the source value; it produces a result that a query can display, compare, group, or pass to another expression.

Suppose a table contains product codes such as AB-1048 and CD-2091, where the first two characters identify a product family. LEFT(product_code, 2) returns AB and CD. This can help inspect or categorize data, but it does not validate that a code has the expected format. A malformed value such as A? will still yield its first two characters, if present.

The function is commonly used for display, grouping, and extracting fixed-width prefixes. It is not a universal SQL feature with identical behavior in every database, so check the documentation for the database engine and version behind your application.

Syntax in common database systems

In SQL Server, MySQL, and PostgreSQL, the general form is LEFT(string, length). The first argument is the character expression to read; the second is an integer specifying how many characters to return. For example:

  • SQL Server: SELECT LEFT('Northwind', 5); returns North.
  • MySQL: SELECT LEFT('Northwind', 5); returns North.
  • PostgreSQL: SELECT LEFT('Northwind', 5); returns North.

SQLite does not provide a built-in function named LEFT. Use substr(string, start, length) instead: SELECT substr('Northwind', 1, 5); returns North. SQLite’s string positions start at 1 for this use. Other database systems may provide SUBSTRING or SUBSTR rather than LEFT; their syntax and edge cases are not necessarily interchangeable.

For portable SQL, SUBSTRING is often worth considering, but its syntax differs across products. SQL Server accepts forms such as SUBSTRING(value, 1, length), while PostgreSQL and MySQL also support substring forms with their own documented variations. If a query must run on multiple engines, confirm the supported syntax rather than assuming that a function name or argument order is universal.

Using LEFT in a query

A basic query can return a prefix alongside the original value. Given a table named customers with a postal_code column, this SQL Server example returns the first three characters of each code:

SELECT postal_code, LEFT(postal_code, 3) AS postal_prefix FROM customers;

The alias postal_prefix labels the calculated column in the result. The original postal code remains unchanged in the table. If the value has fewer than three characters, the result contains the characters that are available; LEFT('AB', 3) returns AB, not an error or a padded value.

You can also use the expression to group records by prefix. For example, with a table named orders and a reference_code column, a SQL Server query can count orders by the first two characters:

SELECT LEFT(reference_code, 2) AS code_prefix, COUNT(*) AS order_count FROM orders GROUP BY LEFT(reference_code, 2);

This groups values that share the same two-character prefix. It does not guarantee that the prefix is a meaningful category: that interpretation depends on the data’s rules. In longer queries, repeating the expression in both the select list and GROUP BY is common; some database systems also allow grouping by a selected alias, but support differs.

For a filter, you might write WHERE LEFT(account_code, 3) = 'NY-' to find values with that prefix. This is readable, but there may be a more efficient or more portable way to express a prefix search, especially on a large table.

Length, NULL, and empty-string behavior

The requested length is a count of characters, not a request to fill or format the result. A length of zero produces an empty string in the common implementations, and a length greater than the string’s length returns the whole string. A negative length is a portability trap: SQL Server raises an error for a negative count, while PostgreSQL defines negative values differently, returning the string with characters removed from its end. Do not pass a negative value unless the behavior is deliberate and specific to your database.

NULL inputs also matter. In the major implementations, a NULL string or NULL length produces NULL rather than an ordinary prefix. For example, if postal_code is NULL, LEFT(postal_code, 3) evaluates to NULL. This can affect comparisons and grouping: NULL is not the same as an empty string, and a condition such as LEFT(postal_code, 3) = '123' does not match a NULL value.

If the length comes from a column or parameter, check its range before relying on the result. A bad value may cause an error in one database and a different result in another. If the application should treat missing input as an empty string, handle that explicitly with the appropriate NULL-handling function for the database, rather than assuming LEFT converts NULL automatically.

Characters, bytes, and Unicode

In ordinary text use, LEFT counts characters rather than bytes. That distinction matters for multibyte text: a non-English character may occupy more than one byte in a database encoding, yet still count as one character for the function. Byte-oriented operations are different and can cut encoded text incorrectly if used without care.

“Character” does not always mean one complete symbol as a person sees it. A visible character can consist of a base letter plus a combining mark, or an emoji can be represented by multiple code points joined together. A function that counts database characters may split such a sequence at its boundary. SQL Server’s handling of supplementary characters also depends on collation: collations that support supplementary characters count a surrogate pair as one character for LEFT; other collations can treat its two UTF-16 code units separately. Test text that matters to your application using its actual database, encoding, and collation.

Do not use LEFT as a substitute for byte-level truncation, Unicode normalization, or validation. If a column has a maximum storage length, the database’s type and encoding rules determine what can be stored; returning a prefix in a query does not change those rules.

Common uses and safer patterns

Typical uses include extracting a known identifier prefix, creating a short display label, or checking whether a value begins with a marker. For example, the following SQL Server predicate checks the first two characters of a code:

WHERE LEFT(code, 2) = 'Q7'

That expression is easy to understand, but avoid treating a prefix as proof that the rest of the value is valid. If the complete code must match a defined pattern, validate the complete value with rules suitable for the database or application. Likewise, using a short prefix as a unique identifier can create collisions: many full values may share the same first few characters.

For a prefix search, an expression such as code LIKE 'Q7%' may be preferable. Whether it can use an index efficiently depends on the database, collation, index, and query plan; a leading wildcard such as LIKE '%Q7' generally presents a different indexing problem. Inspect the execution plan for important queries rather than assuming either expression will be fast.

When a prefix is repeatedly queried at scale, consider whether the data model should store that meaningful prefix separately, or whether a computed/generated column and an appropriate index are suitable for the database. These approaches add maintenance and design considerations, so they are not automatically better for a small table or an occasional report.

LEFT compared with SUBSTRING and SUBSTR

LEFT(value, n) directly communicates that the query wants the first n characters. A substring function is more flexible because it can start at a position other than the beginning. For example, in SQL Server, SUBSTRING(value, 1, 4) can return the same first four characters as LEFT(value, 4). In SQLite, substr(value, 1, 4) is the common alternative because LEFT is unavailable.

Choose the form that is supported by your database and clearest to the people maintaining the query. If portability is important, document the engine-specific version or use a syntax supported by every target engine. Check negative lengths, NULL handling, character counting, and argument conventions when translating between functions; similar names do not guarantee identical behavior.

Frequently Asked Questions

What does LEFT do in SQL?
LEFT(string, length) returns up to the specified number of characters from the start of a string. It returns a value in a query; it does not modify the stored string.

Does SQL LEFT count characters or bytes?
For normal text use, it counts characters according to the database’s text and collation rules, not simply bytes. Some Unicode sequences that look like one symbol can contain multiple characters, so truncation may split a visible symbol.

What happens if the string is shorter than the requested length?
The function returns the entire available string. For example, requesting five characters from 'AB' returns 'AB'; the value is not padded.

Can I use LEFT in SQLite?
SQLite has no built-in LEFT function. Use substr(value, 1, length) to take a prefix, and check SQLite’s documented behavior for the inputs you use.

Why does LEFT fail with a negative length?
Negative-length behavior differs among database systems. SQL Server reports an error, while PostgreSQL gives negative lengths a defined meaning that removes characters from the end. Use a nonnegative length or handle the value explicitly for the target database.

Is LEFT(code, 3) always faster than LIKE ‘abc%’?
No. Performance depends on the engine, indexes, collation, and query plan. A function applied to a column can affect index use, while a prefix pattern may be indexable in some configurations. Check the plan and test with representative data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.