SUBSTRING_INDEX

Returns a substring of an expression before the specified number of delimiters occurs.

Syntax

SUBSTRING_INDEX(expression varchar, delimiter varchar, count integer) → varchar

• expression: Base expression to extract substring from.

• delimiter: The string to search for.

• count: An INTEGER expression to count the delimiters.

Examples

SUBSTRING_INDEX example

SELECT SUBSTRING_INDEX('spice.ai', '.', 2)
-- EXPR$0
-- spice

SUBSTRING_INDEX example

SELECT SUBSTRING_INDEX('spice.ai', '.', -2)
-- EXPR$0
-- spice.ai

Usage Notes

This function performs a case-sensitive match when searching for the delimiter. The count expression can be a positive or negative number. If positive, this function returns the characters from the left of the expression up to the count of occurrences of the delimiter. If negative, this function returns the characters from the right of the expression up to the count of occurrences of the delimiter.