why to use isnull(field,'') =2?

Question

I have a field to update which uses isnull(field,'') = 2. But I am getting error as the field has character value in it?

for exaample : field = 'E' It says conversion failed converting varchar value 'E' to datatype int

Answer 1

Your problem is that you are working with two different data types. ISNULL(field,'') implies that you are working with a character field. 2 however is a number. Which do you need?

You can do something like this:

ISNULL(field,'') = '2'

But if field happens to be '02' then the result will be false.

Or if field is actually a number then you could do something like this:

ISNULL(field,0) = 2

But if field is a string data type that you are using to hold numbers and you just happen to have an 'Q' in the field then you are going to get a conversion error.

I'm assuming the first option is best since you are getting the error on 'E'.

---

When you use the code you actually have it is doing an implicit conversion behind the scenes. If you look at the XML execution plan for the following code:

CREATE TABLE #DataTypeTest (Col1 varchar(50))

INSERT INTO #DataTypeTest VALUES ('1'),('2')

SELECT *
FROM #DataTypeTest
WHERE ISNULL(Col1,'') = 2

You'll see this line (talking about the ISNULL)

 <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,isnull([tempdb].[dbo].[#DataTypeTest].[Col1],''),0)=(2)">

You can see how it's actually converting the ISNULL in to an INT data type. This is because you are comparing it to a integer. It doesn't convert the 2 to a string because INT is higher than VARCHAR on the data type precedence list.

Now when it tries to convert the ISNULL formula into an integer it of course fails when the column contains an E.