0

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

1
  • Are you asking why you are getting the error message or is your question more to do with the ISNULL statement?
    – Shaneis
    Commented May 19, 2016 at 15:24

1 Answer 1

4

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.

2
  • 2
    You should add that using ISNULL, even WHERE ISNULL(field,'') = '2' that won't throw error, is practically useless. WHERE field = '2' would be simpler and have the same effect. Commented May 19, 2016 at 16:29
  • @ypercubeᵀᴹ Very true. I didn't think to include that. I was focused on the data type confusion. Commented May 19, 2016 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.