SPLIT_PARTB

Splits string on the delimiter and rreturns the string at the location of the beginning of the specified field (counting from 1). The VARCHAR arguments are treated as octets rather than UTF-8 characters.

Behavior Type

Immutable

Syntax

SPLIT_PARTB ( string , delimiter , field )

Parameters

string

(VARCHAR) Is the argument string.

 
delimiter

(VARCHAR) Is the given delimiter.

 
field

(INTEGER) is the number of the part to return.

Notes

Use this function with the character form of the subfield.

Examples

The specified integer of 3 returns the third string, or soupçon.

=> SELECT SPLIT_PARTB('straße~@~café~@~soupçon', '~@~', 3);
 SPLIT_PARTB
-------------
 soupçon
(1 row)

The tildes are for readability only. Omitting them returns the same results:

=> SELECT SPLIT_PARTB('straße @ café @ soupçon', '@', 3);
 SPLIT_PARTB
-------------
  soupçon
(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_PARTB('straße @ café @ soupçon', '@', 4);
 SPLIT_PARTB
-------------
(1 row) 
=> SELECT SPLIT_PARTB('straße @ café @ soupçon', '@', 4) IS NULL;
 ?column?
----------
 f
(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