Error Converting Data Type VARCHAR to Numeric: Causes and Fixes

Error Converting Data Type VARCHAR to Numeric: Causes and Fixes

Seeing “error converting data type varchar to numeric” in SQL Server? This guide explains every cause and walks you through the fixes with clear, working examples.

You’re running a query, everything looks fine, and then SQL Server throws this at you: error converting data type varchar to numeric. The query might have worked yesterday. The table looks normal. Nothing seems obviously wrong. But the database won’t budge.

This error is one of the most common data type issues in SQL Server, and it almost always comes down to one thing: your data has something in a text column that can’t cleanly convert to a number. This post covers every cause, how to track down the bad data, and how to fix it so your query runs clean.

What Is This Error and Why Does SQL Server Throw It?

SQL Server is strict about data types. When you try to convert or cast a VARCHAR value into a NUMERIC, DECIMAL, INT, FLOAT, or similar numeric type, it checks whether the string actually represents a valid number. If it finds something it can’t convert, it stops and throws the error.

The full error message usually looks like this:

Error converting data type varchar to numeric.

Or in some contexts:

Msg 8114, Level 16, State 5
Error converting data type varchar to numeric.

The error code 8114 is the one to remember. If you see it, you know SQL Server hit a string it couldn’t parse as a number.

The Most Common Causes

1. Non-Numeric Characters in the Column

The most frequent culprit. Your VARCHAR column looks like it contains numbers, but somewhere in the data there’s a value like:

• "N/A"
• "unknown"
• " " (a space or empty string)
• "$1,200" (currency formatting)
• "1,500.00" (comma as thousands separator)
• "3.5%" (percent sign)
• NULL passed incorrectly

Any of these will cause the conversion to fail. The column might have 100,000 rows of clean numbers and just one row with "N/A", but that one row is enough to kill the whole query.

2. Implicit Conversion in a JOIN or WHERE Clause

Sometimes you’re not explicitly converting anything. The error fires because SQL Server tries an implicit conversion behind the scenes.

For example:

SELECT * FROM orders WHERE order_amount = '1500';

If order_amount is NUMERIC and '1500' is a string literal, SQL Server converts the string. That usually works fine. But if you flip it:

SELECT * FROM products WHERE product_code = 12345;

And product_code is VARCHAR with values like "A123", SQL Server tries to convert product_code to a number to match, and fails when it hits a non-numeric code.

3. CASE Statements With Mixed Return Types

This one is subtle. In a CASE expression, SQL Server determines the output data type based on all the possible return values. If one branch returns a number and another returns a string, SQL Server tries to reconcile them:

SELECT CASE 
    WHEN status = 'active' THEN amount
    WHEN status = 'closed' THEN 'N/A'
END AS result
FROM accounts;

Here amount might be NUMERIC and 'N/A' is a string. SQL Server picks a type and tries to convert everything to it, which can trigger the error.

4. Data Imported From External Sources

CSV imports, Excel uploads, API feeds, and flat file loads often introduce dirty data. A column labeled “price” in a spreadsheet might contain formatting, units, or placeholder text that made sense to a human but breaks a numeric conversion in SQL.

This is especially common in data analytics workflows where raw data from multiple sources gets loaded into staging tables before transformation.

How to Find the Rows Causing the Error

Before you fix the query, find the bad data. SQL Server won’t tell you which row triggered it, so you have to hunt.

Use TRY_CAST or TRY_CONVERT

These functions attempt the conversion and return NULL instead of throwing an error if it fails. That makes them perfect for identifying problem rows:

SELECT column_value
FROM your_table
WHERE TRY_CAST(column_value AS NUMERIC(18, 2)) IS NULL
  AND column_value IS NOT NULL;

This returns every row where the value exists but can’t be converted to numeric. Those are your bad rows.

TRY_CONVERT works the same way:

SELECT column_value
FROM your_table
WHERE TRY_CONVERT(NUMERIC(18, 2), column_value) IS NULL
  AND column_value IS NOT NULL;

Both functions are available in SQL Server 2012 and later.

Check for Common Patterns

If you want to narrow it down further, look for specific patterns:

-- Find rows with letters
SELECT column_value FROM your_table
WHERE column_value LIKE '%[^0-9.-]%';

-- Find empty strings
SELECT column_value FROM your_table
WHERE column_value = '';

-- Find values with currency symbols or commas
SELECT column_value FROM your_table
WHERE column_value LIKE '%$%' OR column_value LIKE '%,%';

How to Fix the Error

Once you know what’s in the data, you have a few options depending on what you’re trying to accomplish.

Option 1: Use TRY_CAST or TRY_CONVERT in Your Query

If you need to handle bad values on the fly and keep the query running, replace your direct cast with a safe version:

SELECT TRY_CAST(price_text AS NUMERIC(18, 2)) AS price
FROM products;

Rows that can’t convert return NULL. That’s usually acceptable when you’re building a report or aggregation, though you should log or flag those rows separately.

Option 2: Clean the Data Before Converting

If you know the format issues, strip them before casting:

-- Remove commas and dollar signs, then cast
SELECT CAST(REPLACE(REPLACE(price_text, ',', ''), '$', '') AS NUMERIC(18, 2)) AS price
FROM products;

Chain REPLACE calls for each character you need to remove. This works well for predictable formatting issues like currency symbols or thousand separators.

Option 3: Use a CASE Statement to Handle Bad Values

If you want explicit control over what happens to unconvertible values:

SELECT 
    CASE 
        WHEN TRY_CAST(price_text AS NUMERIC(18, 2)) IS NULL THEN 0
        ELSE CAST(price_text AS NUMERIC(18, 2))
    END AS price
FROM products;

This substitutes 0 for any non-numeric value. Adjust the fallback to whatever makes sense for your data: NULL, -1, or a default value.

Option 4: Fix the Data at the Source

If the column regularly receives bad data, the real fix is upstream. That means:

• Validating input before it enters the database
• Adding a CHECK constraint on the table if you control the schema
• Fixing the ETL or import process that’s allowing dirty values in

A one-time query fix handles the symptom. Fixing the source prevents the error from coming back.

Option 5: Change the Column Data Type

If the column is supposed to hold numbers and always has, consider changing its type from VARCHAR to NUMERIC or DECIMAL at the schema level. First clean the existing data, then alter the column:

-- After cleaning, change the type
ALTER TABLE products
ALTER COLUMN price NUMERIC(18, 2);

This enforces data integrity going forward. Any attempt to insert a non-numeric value will fail at the database level rather than silently storing bad data.

Handling This Error in Older SQL Server Versions

TRY_CAST and TRY_CONVERT require SQL Server 2012 or later. If you’re on an older version, you need a workaround:

-- SQL Server 2008 and earlier: use ISNUMERIC
SELECT column_value
FROM your_table
WHERE ISNUMERIC(column_value) = 0 AND column_value IS NOT NULL;

A word of caution: ISNUMERIC has quirks. It returns 1 for values like "$", "1e5", or "1,2" which may or may not convert cleanly to your target numeric type. It’s a rough filter, not a precise one. Use it to find candidates, but verify the results.

For production code on older SQL Server, the safest pattern before TRY_CAST existed was wrapping the conversion in a scalar function with error handling, or pre-filtering rows with ISNUMERIC before passing them to a conversion step.

Preventing This Error in ETL and Data Pipelines

If you work with data imports or pipelines, this error will show up repeatedly unless you build validation into the process. A few habits that help:

• Load raw data into staging tables as VARCHAR first, validate, then transform into typed tables
• Write a validation step that runs TRY_CAST checks before the transformation query
• Log rows that fail validation to a separate error table instead of letting them block the whole load
• Document expected formats for every column that will be converted

Teams doing data mining and analytics at scale deal with this constantly. Building validation into the pipeline from day one saves a lot of reactive debugging later.

This also connects to broader quality assurance practices in software development, where catching data issues early is always cheaper than fixing them after they’ve propagated through a system.

Key Takeaways

• The “error converting data type varchar to numeric” error means SQL Server found a string it couldn’t parse as a number
• The most common cause is dirty data: non-numeric characters, empty strings, or formatted values like "$1,200"
• Use TRY_CAST or TRY_CONVERT to find bad rows and handle them without crashing your query
• Clean formatting issues with REPLACE before casting when the format is predictable
• For a permanent fix, address the data at the source or change the column type once the data is clean
• On SQL Server 2008 and earlier, use ISNUMERIC as a rough filter, but be aware of its limitations

Once you know what to look for, this error goes from frustrating to fixable in a few minutes. The key is finding the bad row first, then deciding whether to clean it, skip it, or fix the upstream process that let it in.