TSQL Finding Data in a Stuffed comma separated string

Question

I have a query that is not pretty by any means. Its pretty dynamic and is the way it is due to its use case on my application. In short, it allows a user to choose fields they want to see on a report, along with defining the logic (WHERE Clause) and finally which order they want to sort in.

To make this easier, I created a view of all the data that was possible to be included in a report. This way, its just selecting the fields the user wants and querying the view.

I have run into an instance now where instead of just single data points, I have some 1:many situations. An example is a list of impacted countries. In my view, it is a comma separated list United States, United Kingdom, Etc. This data is just stuffed and comma separated in the views creation.

The Question

When creating this, I had the intentions of just using an IN statement on the data. I then realized that IN is only a table function and cant be used on a string of data.

Is there a way to take a value and compare it against a comma separated string of data?

I was trying to create an SQL Fiddle but the site keeps crashing on me.

CREATE TABLE Test (country varchar(100), account INT);
INSERT INTO Test ('United States, United Kingdom', '123');
INSERT INTO Test ('United States', '123567');
INSERT INTO Test ('United States, China, Japan', '123567');

-- Trying to find all the rows where `United States` is in the country column.

I have tried using LIKE %country% however this doesn't function the same as IN as I get data back that I don't need if part of the country is in another country name.

Is there any type of function that can be created that acts as an IN statement where I can test a value against a comma separated string?

Answer 1

You can do something like this:

SELECT * FROM Test
WHERE ' ' + country + ',' LIKE '% United States,%'

This will avoid returning countries that containing another country name in them.

As mentioned by @BenJaspers , Ireland and Nothern Ireland will be an exception so you can do this:

SELECT * FROM Test
WHERE ',' + country + ',' LIKE '%, United States,%'
   OR ',' + country + ',' LIKE '%,United States,%'