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

Immutable

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)

See Also