SUBSTR

Extract a contiguous slice of characters from the input string (alias of SUBSTRING).

Category: stringReturns: VARCHARDialect: Standard

Syntax

SUBSTR(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. SUBSTR is an alias of SUBSTRING and behaves identically; choose whichever name matches your project's conventions. Both function names are common in SQL code bases. SUBSTR is often preferred for brevity, especially in scripts that already use SUBSTRING_INDEX or SPLIT_PART for other slicing work. ## Behavior - Returns NULL when any argument is NULL. - Positions are 1-based. - If `pos` is greater than the character length of the string, the result is an empty string. - If `pos + len` extends past the end, 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; multi-byte UTF-8 characters count as one position. ## Compatibility - Functionally identical to SUBSTRING in both positional and SQL-standard (FROM/FOR) call forms. - Widely supported across SQL dialects as the short-form slicing function.

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.
`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 from position 2, length 3
SELECT SUBSTR('hello world', 2, 3);  -- 'ell'

-- Extract the final word
SELECT SUBSTR('hello world', 7, 5);  -- 'world'

-- Position beyond end returns empty string
SELECT SUBSTR('hello', 10, 3);  -- ''

-- Extract first 4 characters
SELECT SUBSTR('abcdef', 1, 4);  -- 'abcd'

-- Extract year from an ISO date string column
SELECT SUBSTR(raw_date, 1, 4) AS year_str
FROM ops.etl.staging_invoices;

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

Pitfalls

SUBSTR is 1-based. Passing 0 as the position is a very common off-by-one bug for developers coming from 0-indexed languages.
SUBSTR counts characters, not bytes. Do not use it to slice UTF-8 byte offsets.
Out-of-range positions silently return an empty string. Validate inputs upstream if you need an explicit failure.
Some dialects interpret SUBSTR(str, pos) (two-argument) as 'from pos to end'. DeltaForge supports the two-argument form with that same meaning; pass an explicit length if your downstream targets do not.