SUBSTRING

Extract a contiguous slice of characters from the input string by 1-based position and length.

Category: stringReturns: VARCHARDialect: Standard

Syntax

SUBSTRING(str, pos, len)

Description

## Overview Returns a contiguous slice of the input string starting at the 1-based character position `pos` and continuing for `len` characters. SUBSTRING is the workhorse for structured-string parsing: extracting codes from fixed-width records, trimming prefixes and suffixes by known length, and splitting concatenated identifiers. SUBSTR is a supported alias. The SQL-standard keyword form SUBSTRING(str FROM pos FOR len) is also accepted. ## Behavior - Returns NULL when any argument is NULL. - Positions are 1-based: the first character is at position 1, not 0. - If `pos` is greater than the character length of the string, the result is an empty string. - If `pos + len` extends past the end of the string, the function returns the slice up to the end without error. - If `len` is 0 or negative, the result is an empty string. - Operates on Unicode code points, so multi-byte UTF-8 characters count as one position and are never split. - The SQL-standard syntax SUBSTRING(str FROM pos FOR len) is equivalent to SUBSTRING(str, pos, len). ## Compatibility - Conforms to the SQL standard SUBSTRING semantics for the three-argument form. - SUBSTR is a widely used alias and returns the same result.

Parameters

Name	Type	Description
`str`		Specifies the input string from which the slice is extracted.
`pos`		Specifies the 1-based starting character position. A value of 1 refers to the first character; values greater than the string length produce an empty result.
`len`		Specifies the number of characters to extract. Non-positive values yield an empty string. A length longer than the remainder returns the remainder without error.

Examples

-- Extract 5 characters starting at position 7
SELECT SUBSTRING('hello world', 7, 5);  -- 'world'

-- Extract the first 5 characters
SELECT SUBSTRING('hello world', 1, 5);  -- 'hello'

-- Extract the area code from a 10-digit phone number column
SELECT SUBSTRING(phone, 1, 3) AS area_code
FROM retail.customers.profiles;

-- Position beyond the string length yields an empty string
SELECT SUBSTRING('hello', 20, 5);  -- ''

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

-- Unicode: each character counts as one, regardless of byte width
SELECT SUBSTRING('café', 3, 2);  -- 'fé'

Pitfalls

SUBSTRING is 1-based, not 0-based. Developers coming from array-indexed languages often pass 0 expecting the first character and end up with a value one position off.
SUBSTRING counts characters, not bytes. Do not use it to slice a specific number of UTF-8 bytes from a payload; use a binary substring or OCTET_LENGTH-aware encoding.
When slicing grapheme clusters (base letter plus combining accents), SUBSTRING may cut inside a cluster. Apply NORMALIZE NFC before slicing if you need visually clean splits.
Out-of-range positions do not raise an error. They silently return an empty string. Validate inputs upstream if you need a failure signal.
Some SQL dialects accept a two-argument form SUBSTRING(str, pos) that returns the suffix from `pos` to the end. DeltaForge also accepts this form; if you need the explicit three-argument behaviour, always pass a length.