SQL - How to check if item is in a list in a record

sql postgresql-8.4 denormalized
Evik James picture Evik James · Jun 15, 2011 · Viewed 12.7k times · Source

I have have a column name MyRoles that contains a list of items (integers) stored in a field named UserRoles. I want to write a query that checks to see if a specific item is in the list. The list will look like this: "1,2,3"

I can't use WHERE MyRoles

What should the query look like?

This is similar to what I am thinking: 

SELECT *
FROM MyTable
WHERE MyRoles CONTAINS ('1')

The fact that no answer was easy to implement and would take me farther down an ugly road really makes clear that a normalized database is always the best bet.

Answer

Oded picture Oded · Jun 15, 2011

You can use LIKE:

SELECT *
FROM MyTable
WHERE MyRoles LIKE ('%1%')

This is likely to perform very badly (as an index will be fairly useless for such a search). And of course, will also match on 10 even if 1 doesn't exist in the query. You can extend the approach:

SELECT *
FROM MyTable
WHERE MyRoles = '1'
  OR MyRoles LIKE '1,%'
  OR MyRoles LIKE '%,1,%'

A better solution would be to normalize your database and not have multi-valued fields. Use a many-to-many table with single role ids and item ids per row. This is much easier to query.

Some databases will have better facilities for such a query, but these would be extensions and not standard SQL - you didn't mention a specific RDBMS.

Related questions

how to exclude null values in array_agg like in string_agg using postgres?

If I use array_agg to collect names, I get my names separated by commas, but in case there is a null value, that null is also taken as a name in the aggregate. For example : SELECT g.id, array_…

Read More
PostgreSQL: between with datetime

I use PostgreSQL 8.4.11 and find strange error. When I query: SELECT "documents_document"."given_on" FROM "documents_document" WHERE (EXTRACT('month' FROM "documents_document"."given_on") = 1 AND "documents_document"."given_on" BETWEEN '1-01-01 00:00:00' and '1-12-31 23:59:59.999999…

Read More
Incrementing a number in a loop in plpgsql

I couldn't find this immediately from the examples. I want to increment a variable in a loop, in a function. For instance: DECLARE iterator float4; BEGIN iterator = 1; while iterator < 999 ..... iterator ++; END; How would this be done? I was looking …

Read More