SPLIT_PART

Splits string on the delimiter and returns the location of the beginning of the given field (counting from one).

Behavior Type

Immutable

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)