Check against a list of values in the database

Question

Let's say TableA has a field called, FieldA. FieldA consists of a list of values with comma being the delimiter (i.e. 1,2,3,4). #MyVar# contains the value (a single value, NOT a list) I want to search for. Other than doing the followings,

SELECT *
  FROM TableA
 WHERE FieldA LIKE '%,#MyVar#,%'
    OR FieldA LIKE '#MyVar#,%'
    OR FieldA LIKE '%,#MyVar#'

Is there a SQL command/reserved word to perform the equivalent of the SQL statement above?

Answer 1

You can use IN to check an list:

SELECT * FROM TableA WHERE FieldA IN '%,#MyVar#,%'

Answer 2

You have a badly designed, denormalized database. If at all possible, fix the design.

If that's not possible then consider the nature of the MyVar values. If they cannot possibly nest inside each other (for instance, they're all guaranteed to be the same number of characters) you can get away with a single like. If not, your solution is about as good as you'll get.

Answer 3

There isn't any need to include the several ORs or commas. This query should do what you need.

SELECT * 
FROM TableA 
WHERE FieldA LIKE '%#MyVar#%'

If the variable appears anywhere in the field, the row will be returned. You don't have to try to compare it to each of your "list" items in each field.

You're kind of abusing database design by including a list of values in a single field. There isn't any built-in SQL function that deals with this kind of data, that I know of.

Answer 4

As per Pounding A Nail: Old Shoe or Glass Bottle?:

This is quite possibly the worst way you could store this data in a database. No, seriously. They had better ways of doing this over thirty years ago. You have created a horrible problem that is only starting to rear its ugly head. It will only continue to get horribly worse, costing your company more and more money to maintain.

The answer is to make a new table, with a one-to-many relationship between the main ID and the different values. Then it's a much simpler query:

SELECT MainID
FROM NewTable
WHERE FieldA = @Value

This is a much easier solution to code against, not to mention understand. And it will help significantly when you have to join this table to others.

EDIT: From your responses, I understand that doing this would be a serious amount of work. However, you're already running into the issues that are arising from the current setup. Doing this will take time up front, but it will make future development significantly faster in the long run.

Answer 5

No, in a word. In a normalized database you would usually split these list items into a separate table, but there are times where is this is either not possible (user has no ability to alter the database structure) or undesired (budget limitations).

you can either

• do something like durilai suggests (not performant for large amounts of data, but probably the easiest to implement)

• use a trigger to split the entries in your on arrival (i.e. on INSERT) into a subtable (SubTableA) and then do

:-

select * from TableA a where exists (select 1 from SubTableA s on s.a_id = a.id
and list_element like '%#MyVar#%')

Answer 6

Put your delimiters on the left side of the like comparison too and then make sure you include your delimiters on the right side. Like this:

SELECT *
  FROM TableA
 WHERE ',' + FieldA + ',' LIKE '%,#MyVar#,%'