CODEWITHSUNDEEP
sqlarrayspostgresql
Sandy
Sandy

Reputation: 2613

PostgreSQL How to query String array


I am trying to write a query to check if an element is within an array of Strings.

Here is my simple select query along with the output

select languages from person limit 3;
{CSS,HTML,Java,JavaScript,Python}
{JavaScript,Python,TensorFlow}
{C++,Python}

How do I write a query to find all people who have "Java" as a listed language they know?
I tried following the syntax but it isn't working.

select languages from person where languages @> ARRAY['Java']::varchar[];

Upvotes: 9

Views: 17372

Answers (3)

hci
hci

Reputation: 39

You can search for more than one pattern replacing '=' operator by the regular expression match operator '~' preceding by a POSIX regular expression, such as:

select languages from person where '[Java,Php]' ~ ANY (string_to_array(languages , ','))

Upvotes: 3

user330315
user330315

Reputation:

You need to use a string constant on the left side, and the ANY operator on the array column:

select languages 
from person 
where 'Java' = any(languages);

This assumes languages is defined as text[] or varchar[] as your sample output indicates

Upvotes: 13

Jophy job
Jophy job

Reputation: 1964

try this 

select languages from person where 'Java' = ANY (string_to_array(languages , ','))

Upvotes: 2

Related Questions