CODEWITHSUNDEEP
sqlsql-server
Irfan Shaikh
Irfan Shaikh

Reputation: 90

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.

Upvotes: 0

Views: 217

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28890

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

Upvotes: 1

Bishoy Frank
Bishoy Frank

Reputation: 114

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

OR

SELECT CASE WHEN ISNUMERIC('1A') =1 THEN '1' ELSE SUBSTRING('1A',2,2) END

Upvotes: 0

Bishoy Frank
Bishoy Frank

Reputation: 114

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

Upvotes: 0

Related Questions