Replace 0 with null and Empty

Question

Here is my query.@ScoreCount may contain zero or not.If it contain zero,should consider zero,as well as null and empty character.How to do that?

declare @ScoreCount
set @ScoreCount = '2,5,0'
select * from tblEmployee where ScoreCount in (@ScoreCount)

Answer 1

Before I give a possible solution to your problem, I must reiterate a couple of points others have already made:

1. You can't pass a delimited list in a variable to an IN clause
2. What is your data type for the ScoreCount column in your table? Let's assume it's an int, as that would make sense.

So anyway, and assuming you're creating a stored proc, here are a couple of solutions:

1. The best solution to your problem is to use a table type variable, as in the answer here: How to pass table value parameters to stored procedure from .net code See the most upvoted answer not the accepted one.

This is great if you're calling a proc from .Net, however other environments may not support table type parameters, in which case you can do the following:

/* Example values - assume @ScoreCount will ultimately be passed in from caller */
declare @ScoreCount varchar(200)
set @ScoreCount = '2,5,0'
/* */

/* 1. Treat null and empty values as zero */
if IsNull(@ScoreCount, '') = '' SET @ScoreCount = '0'

/* 2. Ensure @ScoreCount starts and ends with comma */
SET @ScoreCount = ',' + @ScoreCount + ','

select * from tblEmployee where @ScoreCount LIKE '%,' + Convert(varchar, ScoreCount) + ',%'

What this does is compare each individual ScoreCount value from the table, to see if it is LIKE the comma separated list. e.g. for each row it will do:

select * from tblEmployee where ',2,5,0,' LIKE '%,2,%'   -- True, return this row
select * from tblEmployee where ',2,5,0,' LIKE '%,42,%'  -- false, do not return this row 
select * from tblEmployee where ',2,5,0,' LIKE '%,0,%'   -- True, return this row
... etc for each row.

Note that at comment "2" we prepend and append commas to @ScoreCount to ensure only whole numbers are matched, since we are comparing the numbers as strings.

Note also that is not an efficient query, so it would not be fast for large tables!

Answer 2

First of all you need to parse your variable @ScoreCount with int values for example in some temp table and only then make select from tblEmployee using temp table.

Answer 3

First of all your query is asking this:

select * from tblEmployee where ScoreCount in ('2,5,0');

thus finding only records where ScoreCount matches this one string (same as ScoreCount = '2,5,0').

Then it is strange for a ScoreCount to contain strings (you say varchar(200)). So what do you expect to be in that field? Strings like these: '2', '5', '0', '   ', null, 'two', 'more then 4'? To me it seems you are using the wrong datatype, and this is why you have a problem now.

However, in order to find '2', '5', '0', '   ', null, '  2' and ' 5 ' use:

select * from tblEmployee where isnull(nullif(trim(ScoreCount), ''), 0) in (2, 5, 0);