SPLIT_PART
Splits string on the delimiter and returns the location of the beginning of the given field (counting from one).
Behavior Type
Syntax
SPLIT_PART ( string , delimiter , field )
Parameters
string |
Is the argument string. |
delimiter |
Is the given delimiter. |
field |
(INTEGER) is the 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: No results.
=> SELECT SPLIT_PART('123|456|789', '|', 4); SPLIT_PART ------------ (1 row)
The previous result is not null, it is an empty string.
=> SELECT SPLIT_PART('123|456|789', '|', 4) IS NULL; ?column? ---------- f (1 row)
If SPLIT_PART had returned NULL, LENGTH would have returned null.
=> SELECT LENGTH (SPLIT_PART('123|456|789', '|', 4)); LENGTH -------- 0 (1 row)