SUBSTR()

All functions > STRING > SUBSTR()

Returns a substring from a string starting at a specified position.

Signatures

Returns: Extracted substring

SUBSTR(string: VARCHAR, start: BIGINT, [length: BIGINT]) → VARCHAR

Notes

• Start position is 1-indexed
• Negative start counts backward from the end of the string
• If length is omitted, the slice runs through the end of the string
• If length is zero, the result is ''; if the start lies past the string, the result is ''
• If length exceeds the remaining characters, the result is the remainder (no error)
• If STRING, START, or LENGTH is NULL the result is NULL (use typed NULLs; bare NULL fails inference)

Aliases

• SUBSTRING

Examples

SELECT
    f1 := SUBSTR('Hello World', 1, 5), -- First 5 characters
    f2 := SUBSTR('Hello World', 7, 5), -- Starting from position 7
    f3 := SUBSTR('Hello World', 7), -- From position 7 to end
    f4 := SUBSTR('Hello World', -5, 5), -- Negative start position
    f5 := SUBSTR('Hello World', 1, 100), -- Length exceeds string
    f6 := SUBSTR('ABCDEF', 3, 2), -- Middle substring
    f7 := SUBSTR('Hello', 1, 0), -- Zero length
    f8 := SUBSTR(NULL(VARCHAR), 1, 3) -- NULL yields NULL
;