Monday, June 22, 2015

Case Statement Fails Converting Varchar to Number Even Using isnumeric

Short Description:  A column containing only numbers and empty strings fails to convert using isnumeric as a condition in a case statement.  It returns Msg 8114, "Error converting data type varchar to numeric."

Example:
This statement should only attempt to convert strings that can be converted to numbers
select
   case
 when isnumeric(StringValue) = 1 then (convert(numeric, StringValue))
 else StringValue
 end as ConvertStringValue
from myTable
However it fails with the error above.

Solution:
Isnumeric appears to evaluate an empty string as numeric, but convert fails attempting the actual conversion.  To make the case statement work, add a condition to detect the empty string and do something with it (in this case make it null).

select
   case
 when StringValue = '' then null
 when isnumeric(StringValue) = 1 then (convert(numeric, StringValue))
 else StringValue
 end as ConvertStringValue
from myTable

No comments:

Post a Comment