SQL Unable to Convert Varchar to Numeric - Conversion failed error

Question

I need to convert serial numbers in a database table to show as numeric or int. I have attempted to convert them but it does not seem to like certain values like the following: 1.02253e+007. Getting an error stating:

Msg 245, Level 16, State 1, Line 2

Conversion failed when converting the varchar value '1.02253e+007' to data type int.

Is there something I am leaving out? Any help much appreciated

SELECT  [ID]
  ,[SalesOrder]
  ,[JobNumber]
  ,[StockCode]
  ,SerialNumber
  ,CONVERT(NUMERIC(16, 0), CAST(CASE 
                                  WHEN ISNUMERIC(SerialNumber) = 1 THEN 
                                  SerialNumber 
                                  ELSE 0 
                                END AS FLOAT))

Receiving error: Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value '1.02253e+007' to data type int.

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-02-11T12:04:22

The case is returning an integer, which is your problem. You are doing lots of intermediate conversions that are not necessary. Just do:

(CASE WHEN ISNUMERIC(SerialNumber) = 1
      THEN CONVERT(NUMERIC(16, 0), SerialNumber)
      ELSE 0 
 END)

Or better yet:

COALESCE(TRY_CONVERT(NUMERIC(16, 0), SerialNumber), 0)

If you need to support exponential values (such as 1.02253e+007), then you do need the intermediate conversion to float:

TRY_CONVERT(NUMERIC(16, 0), 
            COALESCE(TRY_CONVERT(float, SerialNumber), 0)
           )

SQL Unable to Convert Varchar to Numeric - Conversion failed error

1 Answers