SPLIT_PART function - Amazon Redshift

SPLIT_PART function

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

Syntax

SPLIT_PART(string, delimiter, position)

Arguments

string

A string column, expression, or string literal to be split. The string can be CHAR or VARCHAR.

delimiter

The delimiter string indicating sections of the input string.

If delimiter is a literal, enclose it in single quotation marks.

position

Position of the portion of string to return (counting from 1). Must be an integer greater than 0. If position is larger than the number of string portions, SPLIT_PART returns an empty string. If delimiter is not found in string, then the returned value contains the contents of the specified part, which might be the entire string or an empty value.

Return type

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

Examples

The following example splits a string literal into parts using the $ delimiter and returns the second part.

select split_part('abc$def$ghi','$',2) split_part ---------- def

The following example splits a string literal into parts using the $ delimiter. It returns an empty string because part 4 is not found.

select split_part('abc$def$ghi','$',4) split_part ----------

The following example splits a string literal into parts using the # delimiter. It returns the entire string, which is the first part, because the delimiter is not found.

select split_part('abc$def$ghi','#',1) split_part ------------ abc$def$ghi

The following example splits the timestamp field LISTTIME into year, month, and day components.

select listtime, split_part(listtime,'-',1) as year, split_part(listtime,'-',2) as month, split_part(split_part(listtime,'-',3),' ',1) as day from listing limit 5; listtime | year | month | day ---------------------+------+-------+------ 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

The following example selects the LISTTIME timestamp 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:

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