Select x value within a text - string_to

I have a column in table1 that contains names separated with commas, like a,b,c

names	result
a,d,e
a,c,e,f
c,d,f,g

Another column with a single name in table2, like a or b or c

line	name	origin
1	a	US
2	b	UK
3	c	UK
4	d	AUS
5	e	CAN
6	f	UK
7	g	UK

And I want to update table1.result if any names from table1.names are in table2.name & origin = UK.

Tried this, but getting error;

update table1 as t1
set result = 
(select name from table2 where origin='UK') = any(string_to_array(t1.names, ','))

CodePudding user response：

Use exists(...) if the result you want is boolean:

update table1 as t1
set result = exists(
    select name 
    from table2 
    where origin = 'UK'
    and name = any(string_to_array(t1.names, ','))
    );

Test it in db<>fiddle.

If you want to get the names, use string_agg():

update table1 as t1
set result = (
    select string_agg(name, ',') 
    from table2 
    where origin = 'UK'
    and name = any(string_to_array(t1.names, ','))
    );

Db<>fiddle.