A common scenario when developing an application is allowing the user to select multiple options and then using the selected options as a filter against a database table. One approach that works nicely in TSQL is to use the CHARINDEX function to filter the data, which was faster than using an “IN” clause, especially if a sub-select was use.
As an example, let’s say you wanted to return all the customers based on countries selected from a list. I’ll hardcode a delimited list in this case, but it can be easily built dynamically if needed or simply passed as a parameter to a stored procedure.
SELECT *
FROM dbo.Customers
WHERE CHARINDEX(Country, ‘Germany, Mexico, Spain’) > 0
Here is a minor change to use a variable for the list of countries.
DECLARE @countries VARCHAR(64)
SET @countries = ‘Germany, Mexico, Spain’
SELECT *
FROM dbo.Customers
WHERE CHARINDEX(Country, @countries) > 0
