Search code examples
sqlsql-server-2012varcharalter-table

SQL Error - varchar to numeric

I imported a flat file into an SQL database and it created all of the fields as varchar(50). I was able to change the data type of most fields, but hit an error in the weight field. All of the data in the weight field is less than 6 characters in total length and is either a whole integer or a decimal. I have tried both:

UPDATE MAWB
SET Weight = CAST(Weight AS decimal(12,2))

and:

ALTER TABLE MAWB
ALTER COLUMN [Weight] decimal(10,2)

I always get the error: Error converting data type varchar to numeric.

I've checked that all of the fields are considered numeric using:

SELECT COUNT(*)
FROM MAWB
WHERE ISNUMERIC(Weight) = 0

I've also tried LTRIM and RTRIM just to be safe, but still get the error when I try to change the field. Does anyone know what else might be causing this error?

Solution

  • You can find the offending rows with this query:

    SELECT *
FROM MAWB
WHERE TRY_CONVERT(decimal(12,2),Weight) IS NULL
AND Weight IS NOT NULL