Skip to main content

SPLIT_PART function

Splits a string by a delimiter and returns the specified part.

Syntax

SPLIT_PART(str, delimiter, part_number)

Arguments

  • str: The string to split.

  • delimiter: The delimiter string to split str by.

  • part_number: The 1-based index of the part to return. Must be a BIGINT (decimal or double values will cause an error).

Returns

A VARCHAR containing the specified part of the split string. Returns an empty string if the part number is out of range.

Limits

  • part_number must be a BIGINT.

  • NULL arguments cause an error.

Special Cases

  • Empty delimiter: Returns the entire string for part 1, empty string for part 2 and beyond.

  • Overlapping matches: Only non-overlapping matches are considered.

Examples

-- Example 1: Split by dot
> SELECT SPLIT_PART('192.0.0.1', '.', 1);
'192'

-- Example 2: Split by space
> SELECT SPLIT_PART('hello world', ' ', 2);
'world'

-- Example 3: Multi-character delimiter
> SELECT SPLIT_PART('hi:)mom:)', ':)', 2);
'mom'

-- Example 4: Empty delimiter returns original string
> SELECT SPLIT_PART('hello world', '', 1);
'hello world'

> SELECT SPLIT_PART('hello world', '', 2);
''

-- Example 5: Empty input
> SELECT SPLIT_PART('', 'foo', 1);
''

-- Example 6: Part number out of range returns empty string
> SELECT SPLIT_PART('a,b', ',', 5);
''

-- Example 7: Overlapping delimiters
> SELECT SPLIT_PART('aaaaa', 'aa', 3);
'a'

-- Example 8: UTF-8 support
> SELECT SPLIT_PART('beyoncé', 'é', 1);
'beyonc'

> SELECT SPLIT_PART('안녕하세요', '하', 2);
'세요'

-- Example 9: Error with decimal part_number
> SELECT SPLIT_PART('hello', 'l', 2.5);
ERROR

See Also