Be it relational database management system or any programming language, most common requirement is the split string and extract the particular value of the result. In this article, we will check Redshift split string on delimiter with some examples.
Redshift Split String
Many relational databases such as Netezza, PostgreSQL, etc, supports array functions. You can use those array functions to extract records from split string result. Unfortunately, Amazon Redshift does not support array functions. Redshift does support split_part string function, you can use this function to split string on any delimiter.
The only drawback of the split_part function is that, you should provide the index of the string that you want to extract from the string. The Function will return NULL if index value is out of range.
Redshift SPLIT_PART Syntax
Below is the syntax of Redshift split_part string function.
SPLIT_PART(string, delimiter, part)Where, 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).
Redshift Split String Examples
You can use Redshift split_part function in many situations such as extract first value or the last value from the delimited string.
Find First value from comma separated string or column
Extracting or finding first value from the delimited string is relatively very easy. You just have to specify the part value as 1.
For example, use following method to find first value from the delimiter string or column.
SELECT Split_part(col1, ',', 1)
FROM (SELECT 'aaa,bbb,ccc,ddd,eee' AS col1) AS a;
split_part
------------
aaa
(1 row)Find Last value from comma separated string or column
To find last value, you have to use length or reverse string function to reverse string and then determine the last value of the delimited string.
For example, use below query to get last value from the delimited string.
SELECT Split_part(reverse(col1), ',', 1)
FROM (SELECT 'aaa,bbb,ccc,ddd,eee' AS col1) AS a;
split_part
------------
eee
(1 row)Related Articles
Hope this helps 🙂