split_part in Redshift to get last value after parsing

Question

I am interested in getting the last item from the split_part function result.

If the field1 has:

abc : def : ghi : jkl
abc : def : ghi : jkl : lmn

split_part(reverse(field1), ':', 1) did not work because it returned:

lkj
nml

I wanted it to return:

jkl
lmn

Answer 1

I think you're looking for something like this:

reverse(split_part(reverse(field1), ':', 1));

You can also consider creating a function:

CREATE OR REPLACE FUNCTION reverse_split_part(varchar(65535), varchar(1), int)
RETURNS varchar(65535)
AS
$$
SELECT
    reverse(split_part(reverse($1), $2, $3));
$$ LANGUAGE SQL IMMUTABLE;

And using it like this:

admin@dev# select reverse_split_part(field1, ':', 1) from tbl;
reverse_split_part 
--------------------
  jkl
  lmn
(2 rows)