CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mameesh
mameesh

Reputation: 3761

CharIndex exact match?

I have a parameter being passed into a usp that is a varchar(max). This parameters is something like the following:

'1,20,3,40,0'
'1,5,30,0,5,9'
'0,50,40,8,9'
'1,10,2,3,4'

I need to figure out if the parameter has a value of just 0 in it anywhere. I was going to use:

CASE WHEN CHARINDEX('0',@DispIDs) > 0 
     THEN convert(bit,1) 
     ELSE convert(bit,0) 
END

But this still returns a true on values like 40, 30, etc. I only want to mark it as true if the param contains the 0. So the last example would return false, the others would return true.

Upvotes: 1

Views: 1563

Answers (1)

Jon Egerton
Jon Egerton

Reputation: 41539

Search for it surrounded with commas, and wrap the initial string with commas to match (to cover 0 at the ends of the string):

CASE WHEN CHARINDEX(',0,',',' + @DispIDs + ',') > 0 THEN convert(bit,1) ELSE convert(bit,0) END

Upvotes: 2

Related Questions