SPLIT_PART
Splits string on the delimiter and returns the string at the location of the beginning of the specified field (counting from 1).
Behavior Type
Syntax
SPLIT_PART ( string , delimiter , field )
Parameters
string |
Argument string |
delimiter |
Delimiter |
field |
(INTEGER) Number of the part to return |
Notes
Use this with the character form of the subfield.
Examples
The specified integer of 2 returns the second string, or def
.
=> SELECT SPLIT_PART('abc~@~def~@~ghi', '~@~', 2); SPLIT_PART ------------ def (1 row)
In the next example, specify 3, which returns the third string, or 789
.
=> SELECT SPLIT_PART('123~|~456~|~789', '~|~', 3); SPLIT_PART ------------ 789 (1 row)
The tildes are for readability only. Omitting them returns the same results:
=> SELECT SPLIT_PART('123|456|789', '|', 3); SPLIT_PART ------------ 789 (1 row)
See what happens if you specify an integer that exceeds the number of strings: The result is not null, it is an empty string.
=> SELECT SPLIT_PART('123|456|789', '|', 4); SPLIT_PART ------------ (1 row)
=> SELECT SPLIT_PART('123|456|789', '|', 4) IS NULL; ?column? ---------- f (1 row)
If SPLIT_PART had returned NULL, LENGTH would have returned 0.
=> SELECT LENGTH (SPLIT_PART('123|456|789', '|', 4)); LENGTH -------- 0 (1 row)
If the locale of your database is BINARY, SPLIT_PART calls SPLIT_PARTB:
=> SHOW LOCALE; name | setting --------+-------------------------------------- locale | en_US@collation=binary (LEN_KBINARY) (1 row) => SELECT SPLIT_PART('123456789', '5', 1); split_partb ------------- 1234 (1 row)
=> SET LOCALE TO 'en_US@collation=standard'; INFO 2567: Canonical locale: 'en_US@collation=standard' Standard collation: 'LEN' English (United States, collation=standard) SET
=> SELECT SPLIT_PART('123456789', '5', 1); split_part ------------ 1234 (1 row)