sql query - proper way to get id from a string?

I have a customer table, which has a field "categories". It's a string holding IDs divided by commas like 1,11,14,21.

Let's say I want to query all customers which have categoryID 1 - what's the proper way to query it?

The problem is that ID 1 could be at the beginning, middle or the end of the string, or even be th开发者_JS百科e only ID so I'd have to cover all cases like:

WHERE categories LIKE '1' 
   OR categories LIKE '1%,' 
   OR categories LIKE '%,1,%' 
   OR categories like '%,1'

Is there a more elegant (and probably much faster) way to do this?

You should instead have a CustomerCategories table which has CustomerID, CategoryID columns, and then have one entry per customers category. This is far easier to query - and is very extremely highly the norm for relational databases.

Storing arrays of IDs in strings is non-relational and as you've found is a nightmare to query.

You should strangle the person who designed the DB to work like that. Never can I think of an instance where you should have delimited data in a field.

On that note, I think this will be a suitable workaround for you.

WHERE ','+[categories]+',' LIKE '%,1,%'