Error converting data type varchar to numeric

Question

Error converting data type varchar to numeric

KathyC

Valued Member

Points: 68
More actions
August 10, 2016 at 8:07 am

#312418

Hi All,
I am new to SQL so please forgive me for my lack of SQL knowledge as I am thinking this should be very easy but I have no clue how to fix it.
I am trying to build a case statement for column 1. For value = -99, I want to replace with 'NA' and all other values (ex: 2.0, 3.112, or even NULL etc) I want to keep it the same. So my case statement is as follow:
case
when column1 = -1 then 'NA'
else column1
end
I am getting this error "Error converting data type varchar to numeric". Please help.
Thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Every output in a CASE statement should have the same type. When they don't SQL Server will try to do an implicit conversion following data type precedence.

To prevent this error, CAST your numeric column to a string.

CASE

WHEN column1 = -1 THEN 'NA'

ELSE CAST(column1 AS varhcar(15))

END

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

KathyC Valued Member Points: 68 More actions · Answer 2

Luis Cazares (8/10/2016)
Every output in a CASE statement should have the same type. When they don't SQL Server will try to do an implicit conversion following data type precedence.
To prevent this error, CAST your numeric column to a string.
CASE
WHEN column1 = -1 THEN 'NA'
ELSE CAST(column1 AS varhcar(15))
END

Got it! Thanks very much for your help.