CODEWITHSUNDEEP
sql
user721
user721

Reputation: 311

CHARINDEX on SQL WHERE clause

I have a column in a table with sample data as follows:

ServiceTypeIDs
1,9
4

In my Where clause I use the following to search on it:

   WHERE
( @ServiceTypes is null or       
   charindex(','+cast(SEP.ServiceTypeIDs as varchar(255))+',',      
   ','+@ServiceTypes+',') > 0)))

If my param @ServieTypes equals '1,9', then I get results, but when it's @ServiceTypes = '1' or just '9', then I don't recieve anything back. I'm stuck and tried the above. 

exact column looks like below

7
8
2
5
4
7
7
7
7
7
7
1
7
null
6,7
7
6
1
7
null
7
7
7
7
7
8
7
6
1
1
1
6
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
7
1
7
7
1
7
8
7
1
1
2
1
null
7
7
7
7
7
7
7
7
7
7
7
7
7
8
6
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
1
7
7
1
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
1
1
1
1
1,2
1
1
1,2
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1,2
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
6
7
7
1
8
7
7
7
7
7
7
7
7
7
7
7
2
7
null
null
1
1
1
1
1
7
7
7
1,2
6
7
1
2
1
1
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
5
5
1
1
1
7
7
7
1
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
7
3
1
9
9
9
9
null
9
1,2
9
9
9
8,9
1,9
1,9
1,9
2,9
1,9
1,9
1,9
1,8
1,9
1,9
9
9
9
9
5
4
3
1
null
1,2,3
6
9
9
9
8,9
9
9
9
1,9

Upvotes: 0

Views: 1906

Answers (1)

bfavaretto
bfavaretto

Reputation: 71939

Your arguments to CHARINDEX are reversed. Use this instead:

CHARINDEX(','+@ServiceTypes+',', ','+CAST(SEP.ServiceTypeIDs AS VARCHAR(255))+',') > 0

It should be noted that your actual problem is a poor database design. You should never store lists as strings like that, use a separate table instead, with one row per association.

Upvotes: 1

Related Questions