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:
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.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
