Fix 'Msg 8114, Level 16, State 5' SQL Server Error Fast

Quick answer: find the column with mixed data

Use TRY_CAST or ISNUMERIC on suspected columns to locate non-numeric values. Then clean the source data or adjust the query to handle them.

Why you're seeing this error

This happens all the time when you're importing or querying a table where a column defined as VARCHAR actually contains numbers, but some rows have letters, symbols, or spaces. SQL Server tries to convert that string to a numeric type (INT, DECIMAL, FLOAT) and fails on the first bad value. I've seen this on SQL Server 2016, 2017, 2019, and 2022. It's especially nasty when it hits in a stored procedure or an ETL (Extract, Transform, Load) job running overnight. You get a phone call at 3 AM and the log shows state 5, which means the conversion error came from a CAST or CONVERT in your query.

Step-by-step fix

1. Identify the exact column. Look at the query that failed. If it's a SELECT or INSERT, find every column that's being compared or inserted into a numeric column. Common culprits: CustomerID, ZipCode, PhoneNumber stored as VARCHAR but used in calculations.
2. Run a validation query. Use this template to scan each suspect column:

   SELECT column_name
   FROM your_table
   WHERE TRY_CONVERT(DECIMAL(18,2), column_name) IS NULL
     AND column_name IS NOT NULL;

   Replace DECIMAL(18,2) with the target data type you're trying to convert to. This will return all rows that would cause the error.
3. Fix the data. If you find bad rows, you have three options:
   • Update the source table to clean the values (e.g., remove non-numeric characters).
   • Add a WHERE clause to exclude bad rows from the query.
   • Use TRY_CAST or TRY_CONVERT to return NULL instead of failing.
4. Example of a safe conversion:

   SELECT TRY_CAST(YourColumn AS INT) AS CleanValue
   FROM YourTable;

5. Test the fix. Run the original query again. If it still fails, you missed a column. Go back to step 1.

When your main fix doesn't work

Sometimes the bad data is hiding in a table you can't modify — say, a vendor import. In that case, I'd create a view that wraps the table with TRY_CAST on every risky column. Then point your query at the view. It's a band-aid, not a cure, but it keeps things running.

Another scenario: the error comes from a LIKE or IN clause. SQL Server might try to convert the whole column before the comparison. Rewrite the condition to avoid implicit conversion. For example, instead of WHERE IntColumn = '123', use WHERE IntColumn = 123 (no quotes).

How to prevent this error

The real fix is to never store numbers in VARCHAR columns. I know legacy systems force your hand. If you can't change the schema, add a check constraint that validates the format:

ALTER TABLE YourTable
ADD CONSTRAINT CK_ColumnIsNumeric
CHECK (ISNUMERIC(ColumnName) = 1);

Also, always use explicit CAST or CONVERT in your queries. Don't let SQL Server guess. If you're joining a VARCHAR column to an INT column, convert one side explicitly. It makes the error easier to debug because you know exactly where the conversion happens.

Real-world trigger that bit me

I once spent an hour on this error in SQL Server 2017 because a CRM table stored customer IDs as VARCHAR, but one manager typed '00123A' into a field. The ETL job tried to insert into a DECIMAL column. TRY_CONVERT caught it instantly. Trust me, run the validation query before you touch production data. It'll save you the headache.