Remove Duplicates From Comma Separated String (amazon Redshift)

I am using Amazon Redshift. I have a column in that string is stored as comma separated like Private, Private, Private, Private, Private, Private, United Healthcare. I want to remo

Solution 1:

Here is a User-Defined Function (UDF) for Amazon Redshift:

CREATEFUNCTION f_uniquify (s text)
  RETURNS text
IMMUTABLE
AS $$
  -- Split string by comma-space, remove duplicates, convert back to comma-separatedreturn', '.join(set(s.split(', ')))
$$ LANGUAGE plpythonu;

Testing it with:

selectf_uniquify('Private, Private, Private, Private, Private, Private, United Healthcare');

Returns:

United Healthcare, Private

If the order of return values is important, then it would need some more specific code.

Solution 2:

Try this way,

SELECTarray_agg(DISTINCT insurances) 
FROM (SELECT  regexp_split_to_table('Private, Private, Private, Private, Private, Private, United Healthcare'
              , ',\s+') AS insurances) x;

Alternative way

SELECTDISTINCTUNNEST(regexp_split_to_array('Private, Private, Private, Private, Private, Private, United Healthcare', ',\s+')) AS insurances;

Checking http://docs.aws.amazon.com/redshift/latest/dg/String_functions_header.html both will fail with redshift, none of those converts text to text[]

Solution 3:

Alternative Option is to try Python UDF. Simple Python function dedupes the string and return correct version.