Best way to replace IN clause with LIKE or something equivalent

Question

I have existing SQL like the following (this is a contrived example):

DECLARE @Var NVARCHAR(256)

SELECT @Var = SomeValue
FROM SomeTable

SELECT *
FROM AnotherTable B
WHERE @Var IN ('One','Two','Three')
   OR (@Var IN ('Four','Five') AND [Some Other Condition])

This works great when @Var is equal to a single value like 'Two'. But now the specs have changed and @Var can now be equal to a comma delimited string like 'Two,Four'. What's the best way to change the above code to support that?

The obvious/brute-force way is to change it to use multiple LIKEs:

SELECT *
FROM AnotherTable B
WHERE (@Var LIKE '%One%' OR @Var LIKE '%Two%' OR @Var LIKE '%Three%')
  OR ((@Var LIKE '%Four%' OR @Var LIKE '%Five%')) AND [Some Other Condition])

But is there a better way?

I was hoping SQL Server had support for regular expressions, but sadly it's very limited and as far as I can tell doesn't support searching for multiple words.

Any ideas?

Answer 1

I am with siggemannen.

separate comma separated values and store in table in sql server

Below uses the function from that answer.

declare @var nvarchar(max) = 'one,two';

SELECT
    *
INTO #tmp_passed_variables
FROM [dbo].[Split](@var, ',') 

select * from #tmp_passed_variables

Results

items
one
two

SELECT
    *
INTO #tmp_AnotherTable
FROM(
    VALUES ('data1'),('data2')
) x (id)

SELECT *
FROM #tmp_AnotherTable B
WHERE (SELECT 1 FROM #tmp_passed_variables var WHERE var.items in('five') ) = 1

SELECT *
FROM #tmp_AnotherTable B
WHERE (SELECT 1 FROM #tmp_passed_variables var WHERE var.items in('one') ) = 1

Query 1 no results

Query 2 has results

id
data1
data2

You should be able to work everything as needed to fit your end needs. You can also use it as a join or to set variable flags, etc...