LEFT

Return the leftmost n characters of the input string.

Category: stringReturns: VARCHARDialect: Standard

Syntax

LEFT(str, n)

Description

## Overview Returns the leftmost `n` characters of the input string. LEFT is a shorter, more readable alternative to SUBSTRING(str, 1, n) for the common case of extracting a prefix of fixed length. Use it for product class codes, country prefixes, fixed-width record keys, and any case where the first N characters carry meaning. LEFT supports a negative-length idiom: LEFT(s, -k) returns everything except the last `k` characters, which is useful for stripping a known-length suffix such as a file extension or a trailing checksum. ## Behavior - Returns NULL when the input string is NULL. - Returns NULL when `n` is NULL. - If `n` is 0, returns an empty string. - If `n` is greater than or equal to the character length of the string, returns the full string. - If `n` is negative, returns all characters except the last |n|. If |n| is greater than the string length, returns an empty string. - Counts Unicode code points, so multi-byte UTF-8 characters each count as one position. - Never raises an error for out-of-range `n`. ## Compatibility - Widely supported across SQL dialects with the same positive-n semantics. - The negative-n convention matches the common convention and is equivalent to SUBSTRING(str, 1, CHAR_LENGTH(str) + n).

Parameters

Name	Type	Description
`str`		Specifies the input string from which leading characters are extracted.
`n`		Specifies the number of characters to return from the left. Values greater than the string length return the full string. Negative values return all but the last \|n\| characters. Zero returns an empty string.

Examples

-- First 3 characters
SELECT LEFT('hello', 3);  -- 'hel'

-- n greater than length returns the full string
SELECT LEFT('hi', 10);  -- 'hi'

-- Zero returns empty string
SELECT LEFT('hello', 0);  -- ''

-- Negative n excludes the last |n| characters
SELECT LEFT('hello', -2);  -- 'hel'

-- Extract the product class prefix from a SKU column
SELECT LEFT(sku, 4) AS product_class
FROM retail.products.catalog;

-- NULL propagates
SELECT LEFT(CAST(NULL AS VARCHAR), 3);  -- NULL

Pitfalls

LEFT counts characters, not bytes. A column of CJK text or emoji will yield a shorter byte-count result than a naive byte-slice would.
Negative n is a subtract-from-end idiom, not an error. Passing -1 by mistake returns everything except the last character, which can silently corrupt downstream keys.
LEFT on a grapheme cluster may split a base character from its combining marks. Normalise with NORMALIZE NFC first if visual integrity matters.
LEFT(col, n) in a WHERE clause usually prevents index use on col. Consider a generated prefix column if this filter is hot.