update table cells to only have unique values

Question

I have a database table with a grade_range column, the rows of this column contain duplicate values all separated by a comma (like a list).

grade_range
"Pre-K, Pre-K, Pre-K"
"Pre-K, K-9, K-9"
"Pre-K, K-7, 9-12"
"Pre-K, K-7, K-7"
"Pre-K, K-6"
"Pre-K, K-5"

I want to update these records so that I am only left with the unique values in the rows of this column, like this

grade_range
"Pre-K"
"Pre-K, K-9"
"Pre-K, K-7, 9-12"
"Pre-K, K-7"
"Pre-K, K-6"
"Pre-K, K-5"

Distinct column values can be easily selected in SQL using -

SELECT DISTINCT grade_range FROM dev.school

but, I am not sure how to select/update distinct values inside a row-cell. One way I thought of doing was to manipulate this data in Excel and then updating records using an UPDATE statement based on the same sids

Any suggestion on accomplishing this in SQL?

@a_horse_with_no_name PostgreSQL brother – hky404 Jul 21 '17 at 16:45 — hky404, Jul 21 '17 at 16:45

score 1 · Answer 1 · answered Jul 20 '17 at 23:53

Use this function to convert the comma seperated values to rows then use distince then concatenate them again

ALTER FUNCTION [dbo].[Split]
    (
        @List varchar(max),
        @SplitOn nvarchar(5)
    )  
    RETURNS @RtnValue table 
    (

        ID int identity(1,1),
        Val varchar(max)
    ) 
    AS  
    BEGIN 
        While (Charindex(@SplitOn,@List)>0)
        Begin

            Insert Into @RtnValue (Val)
            Select 
                Val = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

            Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
        End

        Insert Into @RtnValue (Val)
        Select Value = ltrim(rtrim(@List))

        Return
    END

Brain90 · Answer 2 · 2017-08-05T13:49:58.560

1st. Create a function to distinct an array

Open psql. Inside psql type \ef. Replace the content with this function:

CREATE OR REPLACE FUNCTION public.array_unique(arr anyarray)
RETURNS anyarray 
LANGUAGE sql AS 
$function$
  select array( select distinct unnest($1) )
$function$

2nd. Play the game

Assuming each element constantly divided by comma and space. We can split per rows into array format, distinct it with previous function, then convert it back to a string.

SELECT 
   array_to_string(array_unique(regexp_split_to_array(grade, E', ')), ',') as grade_distinct, 
   grade 
FROM 
   your_table;

Result

localhost:5432 user@database=# 
SELECT 
   array_to_string(array_unique(regexp_split_to_array(grade, E', ')), ',') as grade_distinct, 
   grade 
FROM 
   tmp_stack_overflow;

 grade_distinct |        grade        
----------------+---------------------
 Pre-K          | Pre-K, Pre-K, Pre-K
 Pre-K,K-9      | Pre-K, K-9, K-9
 Pre-K,K-7,9-12 | Pre-K, K-7, 9-12
 Pre-K,K-7      | Pre-K, K-7, K-7
 K-6,Pre-K      | Pre-K, K-6
 K-5,Pre-K      | Pre-K, K-5
(6 rows)

array_unique function credit to this post: https://stackoverflow.com/a/41725011/341959 — Brain90, Jul 21 '17 at 04:20

update table cells to only have unique values

2 Answers2