Postgres string to array and check if includes value

Question

I have got a table employer_entity with column tokens. This column is a varchar and it contains many tokens splitted by comma, for instance:

id | tokens
---|------------------
 1 | aaaaa,bbbbb,ccccc

Now I want to build a query that will check if tokens column contains the token aaaaa. Here is what I've got

select *
from employer_entity
where 'aaaaa'
in (string_to_array(employer_entity.tokens, ','))

But it throws an error

Array value must start with "{" or dimension information.

Mureinik · Accepted Answer · 2022-12-17 21:40:10Z

5

You're looking for the any operator, not the in operator:

SELECT *
FROM   employer_entity
WHERE  'aaaaa' = ANY(STRING_TO_ARRAY(employer_entity.tokens, ','))

answered Dec 17, 2022 at 21:40

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

1 Comment

SQL Error [42883]: ERROR: function string_to_array(text[], unknown) does not exist