SQL Server error 8114: 'Error converting data type varchar to numeric'

Error converting data type varchar to numeric. (error 8114) means at least one text value could not be read as a number by CAST or CONVERT. Find the bad rows with TRY_CONVERT(...) IS NULL, then either clean them or use TRY_CONVERT in the query so they come back as NULL instead of stopping it.

TRY_CONVERT(decimal(10,2), Price) returns the number when the text is a number and NULL when it is not, so one bad row no longer fails the whole query.

The error

SELECT Item, CAST(Price AS decimal(10,2)) AS Price
FROM dbo.ImportedPrices;

Item Price
---- -----
Notebook 12.50
Pen 3.20
Msg 8114, Level 16, State 5, Server MACHINE\LOCALDB#EAD42A01, Line 2
Error converting data type varchar to numeric.

Why it happens

The prices came from an import and were stored as varchar. Two rows held 12.50 and 3.20; the third, a stapler, held n/a. Converting that text to decimal has no answer, so SQL Server stopped the statement at that row. Note that sqlcmd had already printed the first two rows: the error arrives part-way through the result, and an application reading rows as they come can show a partial list before it fails.

Text columns that hold numbers are the usual source: spreadsheet imports, staging tables, and columns that were made varchar "to be flexible". The message does not say which row failed, which is why the first step is to find it.

The fix

-- the fix: TRY_CONVERT returns NULL for the bad row
SELECT Item, TRY_CONVERT(decimal(10,2), Price) AS Price
FROM dbo.ImportedPrices
ORDER BY Id;
GO
-- find the bad rows
SELECT Id, Item, Price
FROM dbo.ImportedPrices
WHERE TRY_CONVERT(decimal(10,2), Price) IS NULL;

The first query returned all three items, with NULL as the stapler's price. The second returned one row, 3 Stapler n/a. Which to use depends on the job. For a report, TRY_CONVERT keeps the query running and shows the gap honestly. For data you will keep, fix the bad values at the source, then change the column to a numeric type so the database refuses new bad values on insert rather than on every later read.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e8114.sql -W on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. One table with a varchar(20) price column and three fictional rows. The server's computer name in the message is replaced by MACHINE; nothing else changed.

Frequently asked

What does 'Error converting data type varchar to numeric' mean?

SQL Server error 8114 means CAST or CONVERT met a text value that is not a valid number, such as n/a, and stopped the statement at that row.

How do I find the rows that cause error 8114?

Select the rows where TRY_CONVERT(decimal(10,2), YourColumn) IS NULL. TRY_CONVERT returns NULL for every value that cannot be converted, so those rows are the culprits.

What is the difference between CAST and TRY_CONVERT in SQL Server?

CAST raises an error when a value cannot be converted. TRY_CONVERT returns NULL for that value instead, so the rest of the query still runs.

More decoded errors in the Fixes category, including divide by zero, which also stops a query part-way through its rows.