Select x value within a text - string_to_array

Question

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, ','))

Can you update your post with the expected output table?

lemon
– lemon

2023-01-09 13:55:01 +00:00
Commented Jan 9, 2023 at 13:55 — lemon
– lemon, Commented Jan 9, 2023 at 13:55

klin · Accepted Answer · 2023-01-09 14:54:01Z

1

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.

edited Jan 9, 2023 at 14:54

answered Jan 9, 2023 at 14:39

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select x value within a text - string_to_array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related