CODEWITHSUNDEEP
sql-server
mrbungle
mrbungle

Reputation: 1931

Case statement not seeing value

No novice here but also no expert. I have the following example:

Declare @Test VARCHAR(max) = 'Value1, Value2'
select case when 'Value1' in (@Test) then 1 else 0 end as Col1

returns 0, shouldn't I expect it to return 1? I need it to return 1.

Upvotes: 0

Views: 45

Answers (3)

Xedni
Xedni

Reputation: 4695

First lets look at what your code does. It's equivalent to saying "if the string literal 'a' is in the set of objects containing the string literal 'b', return 1 else 0"

Replace 'a' with 'Value1' and 'b' with 'Value1, Value2' and you'll see the statements are equivalent. There are two ways you can get at what you want. First, you could change your query to a wildcard search.

select case when @test like '%Value1%' then 1 else 0 end

The second is a bit more complicated, but hold truer to the idea of a relational database. First, you would need to split the test string into a table containing the values 'value1' and 'value2'. At this point, since they're distinct values, and not a single string literal, you can then do an IN lookup. Assuming you have a string split function called dbo.stringSplit

select case when 'Value1' in (select stringValue from dbo.stringSplit(@Test, ',')) then 1 else 0 end

Upvotes: 3

flo
flo

Reputation: 10241

You may want to use the LIKE operator to find out, if your search string is contained or not.

SELECT CASE WHEN @Test like '%Value1%' THEN 1 ELSE 0 END AS COL1

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

This is because @Test is a single string. You may want to split its value to achieve what you want.

SELECT CASE WHEN 'Value1' in ('Value1', 'Value2') THEN 1 ELSE 0 END AS COL1

Upvotes: 1

Related Questions