Irfan Shaikh Irfan Shaikh - 6 months ago 29
SQL Question

Sql ISNUMERIC()

SELECT CASE WHEN ISNUMERIC('1a') =1 THEN 1 ELSE 'A' END


i'am getting this error !!


Conversion failed when converting the varchar value 'A' to data type int.

Answer

you are a victim of data type precedence.Taken from BOL:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type

So in your case ,CASE is an expression and when two or more data types are combined in this expression,it returns data type of highest precedence..in your case it is INT..So change your query to below

SELECT CASE WHEN ISNUMERIC('1a') =1 THEN cast(1 as varchar) ELSE 'A' END