CODEWITHSUNDEEP
sqlpostgresql
lupin
lupin

Reputation: 183

sql IN operator with text array

How to find string values in text array using SQL query.

Suppose I have:

id       location
1        {Moscow,New york}
2        {Mumbai}
3        {California,Texas}

I want to find id whose location is Moscow.I used:

select id from table where location in ('Moscow'); but get error:

ERROR:  malformed array literal: "Moscow"
LINE 1: select id from table where location in ('Moscow');
DETAIL:  Array value must start with "{" or dimension information.

I am using Postgres.

Upvotes: 1

Views: 6010

Answers (3)

Sphinx
Sphinx

Reputation: 10729

For DataType=Array, you can use the method=Any.

select id from table where 'Moscow' = Any(location)

As the document which describes DataType=Array:

8.14.5. Searching in Arrays

To search for a value in an array, each value must be checked. This can be done manually, if you know the size of the array.

or use the method = Any:

9.21.3. ANY/SOME (array)

expression operator ANY (array expression) expression operator SOME (array expression) The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the case where the array has zero elements).

Upvotes: 2

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

For Searching in Array DataType you can use the ANY()

SELECT id FROM table WHERE 'Moscow' = ANY(location);

Live Demo

http://sqlfiddle.com/#!17/a6c3a/2

Upvotes: 1

Sumit Mehta
Sumit Mehta

Reputation: 39

select id from demo where location like '%Moscow'%'

Upvotes: 0

Related Questions