How to remove duplicates in hive string?

问题:

I have column(string) comma separated with duplicate values. I want to remove duplicates:
e.g.

column_name
-----------------
gun,gun,man,gun,man
shuttle,enemy,enemy,run
hit,chase

I want result like:

column_name
----------------
gun,man
shuttle,enemy,run
hit,chase

I am using hive database.Please help.

回答1:

Option 1: keep last occurrence

This will keep the last occurrence of every word.
E.g. 'hello,world,hello,world,hello' will result in 'world,hello'

select  regexp_replace
        (
            column_name
           ,'(?<=^|,)(?<word>.*?),(?=.*(?<=,)\\k<word>(?=,|$))'
           ,''
        )

from    mytable
;

---

+-------------------+
| gun,man           |
| shuttle,enemy,run |
| hit,chase         |
+-------------------+

Option 2: keep first occurrence

This will keep the first occurrence of every word.
E.g. 'hello,world,hello,world,hello' will result in 'hello,world'

select  reverse            
        (
            regexp_replace
            (
                reverse(column_name)
               ,'(?<=^|,)(?<word>.*?),(?=.*(?<=,)\\k<word>(?=,|$))'
               ,''
            )
        )

from    mytable
;

Option 3: sorted

E.g. 'Cherry,Apple,Cherry,Cherry,Cherry,Banana,Apple' will result in 'Apple,Banana,Cherry'

select  regexp_replace
        (
            concat_ws(',',sort_array(split(column_name,',')))
           ,'(?<=^|,)(?<word>.*?)(,\\k<word>(?=,|$))+'
           ,'${word}'
        )

from    mytable
;