Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

SPLIT_PART Function

Splits a string on the specified delimiter and returns the part at the specified position.

Syntax

Copy
SPLIT_PART(string, delimiter, part)

Arguments

string

The string to be split. The string can be CHAR or VARCHAR.

delimiter

The delimiter string.

If delimiter is a literal, enclose it in single quotes.

part

Position of the portion to return (counting from 1). Must be an integer greater than 0. If part is larger than the number of string portions, SPLIT_PART returns an empty string.

Return Type

A CHAR or VARCHAR string, the same as the string parameter.

Examples

The following example splits the time stamp field LISTTIME into year, month, and date components.

Copy
select listtime, split_part(listtime,'-',1) as year, split_part(listtime,'-',2) as month, split_part(split_part(listtime,'-',3),' ',1) as date from listing limit 5; listtime | year | month | date ---------------------+------+-------+------ 2008-03-05 12:25:29 | 2008 | 03 | 05 2008-09-09 08:03:36 | 2008 | 09 | 09 2008-09-26 05:43:12 | 2008 | 09 | 26 2008-10-04 02:00:30 | 2008 | 10 | 04 2008-01-06 08:33:11 | 2008 | 01 | 06 (5 rows)

The following example selects the LISTTIME time stamp field and splits it on the '-' character to get the month (the second part of the LISTTIME string), then counts the number of entries for each month:

Copy
select split_part(listtime,'-',2) as month, count(*) from listing group by split_part(listtime,'-',2) order by 1, 2; month | count -------+------- 01 | 18543 02 | 16620 03 | 17594 04 | 16822 05 | 17618 06 | 17158 07 | 17626 08 | 17881 09 | 17378 10 | 17756 11 | 12912 12 | 4589 (12 rows)