SQL Server  

How To Fix "Error Converting Data Type NVARCHAR to Numeric" in SQL Server

When working with SQL Server, one of the most common data conversion errors developers face is:

Error converting data type nvarchar to numeric

This error occurs when SQL Server attempts to convert an nvarchar value into a numeric data type (INT, DECIMAL, FLOAT, BIGINT, NUMERIC, etc.), but the string contains non-numeric characters such as:

  • alphabets (A–Z)

  • symbols (@, #, $, %, etc.)

  • commas or spaces

  • empty strings

  • mixed values (e.g., "45kg", "12-34")

SQL Server cannot interpret these characters as numbers, so the conversion fails.

This guide explains why the error occurs, how to identify bad data, and how to fix it properly, with real examples.

1. Identify Problematic Data

Before fixing the error, identify which rows contain invalid numeric data.

You can use TRY_CAST or TRY_CONVERT, which return NULL instead of throwing an error when conversion fails.

SELECT YourNvarcharColumn
FROM YourTable
WHERE TRY_CAST(YourNvarcharColumn AS DECIMAL(18, 2)) IS NULL
  AND YourNvarcharColumn IS NOT NULL;  -- Exclude actual NULLs

This query returns all rows that cannot be converted to a numeric value.

Common causes you may find:

  • Values containing commas: "1,250"

  • Currency symbols: "$300"

  • Extra spaces: " 500 "

  • Text mixed with numbers: "45kg"

  • Completely non-numeric: "ABC"

  • Empty strings: ""

2. Clean and Normalize Data

After identifying invalid data, clean it before converting.

A. Remove non-numeric characters (commas, dollar signs, spaces)

SELECT CAST(
        REPLACE(REPLACE(TRIM(YourNvarcharColumn), ',', ''), '$', '')
       AS DECIMAL(18, 2))
FROM YourTable;

B. Handle empty strings or unwanted text safely

Use a CASE expression to convert empty strings or invalid text to NULL or a default value.

SELECT CASE
         WHEN YourNvarcharColumn = '' THEN NULL
         ELSE CAST(YourNvarcharColumn AS DECIMAL(18, 2))
       END
FROM YourTable;

C. Remove alphabetic characters (if needed)

If the column may contain random letters mixed with numbers:

SELECT *
FROM YourTable
WHERE YourNvarcharColumn LIKE '%[^0-9.]%';

This finds values containing characters other than digits or decimal points.

3. Use TRY_CAST or TRY_CONVERT for Safe Conversions

For safer conversions that do not break your query, always prefer:

  • TRY_CAST

  • TRY_CONVERT

They return NULL when conversion is not possible.

SELECT TRY_CAST(YourNvarcharColumn AS DECIMAL(18, 2)) AS ConvertedValue
FROM YourTable;

This prevents SQL Server from throwing conversion errors and helps you handle invalid values gracefully.

4. Recommended Best Practices

A. Validate data at the application layer

Prevent wrong inputs before they reach the database.

B. Use SQL CHECK constraints

Ensure only numeric values are inserted.

ALTER TABLE YourTable
ADD CONSTRAINT CK_OnlyNumeric
CHECK (YourNvarcharColumn NOT LIKE '%[^0-9.]%');

C. Store numeric data in numeric columns

Avoid storing numbers as NVARCHAR unless absolutely necessary.

D. Always clean and validate data during migrations and imports

ETL and manual uploads usually cause such issues.

Final Summary

You can resolve the SQL Server error "Error converting data type nvarchar to numeric" by:

  • Identifying invalid values using TRY_CAST or TRY_CONVERT

  • Cleaning data (removing symbols, spaces, and alphabets)

  • Handling empty strings and special cases using CASE

  • Using safe conversions like TRY_CAST

Once the non-numeric values are fixed, SQL Server will successfully convert the data to a numeric type.

This improves data quality, prevents runtime errors, and keeps your SQL operations reliable and efficient.

Membership not found