Analysis and Solutions for 'Error converting data type nvarchar to numeric' in SQL Server

Problem Overview

In SQL Server database operations, data type conversion is a common requirement, but when attempting to convert nvarchar data to numeric type, developers frequently encounter the "Error converting data type nvarchar to numeric" error. This error typically occurs when source data contains characters that cannot be properly parsed as numbers.

Error Cause Analysis

The core reason for this error lies in the source data containing content that does not conform to numeric format specifications. Even when data appears to consist only of numbers, it may hide the following issues:

• Presence of non-numeric characters (such as letters, special symbols)
• Incorrect use of thousand separators or decimal separators
• Scientific notation representation (e.g., '2.81104e+006')
• Empty strings or NULL values
• Inconsistent number formatting

Application and Limitations of ISNUMERIC Function

The ISNUMERIC function is a commonly used data validation tool in SQL Server, but it has certain limitations:

-- Basic usage of ISNUMERIC
SELECT ISNUMERIC('123.45')  -- Returns 1
SELECT ISNUMERIC('abc')     -- Returns 0
SELECT ISNUMERIC('2.81104e+006')  -- Returns 1, but actual conversion fails

As shown in the example, ISNUMERIC returns 1 for numbers in scientific notation, but direct conversion to decimal type using CONVERT will fail. This occurs because scientific notation requires conversion to float type first, followed by precision conversion.

Solution One: Conditional Conversion

Using CASE statements combined with ISNUMERIC function for conditional conversion can effectively avoid conversion errors:

SELECT 
    COLUMNA AS COLUMNA_s,
    CASE 
        WHEN ISNUMERIC(COLUMNA) = 1 
        THEN CONVERT(DECIMAL(18,2), COLUMNA)
        ELSE 0 
    END AS COLUMNA
INTO st00_TABLEA
FROM dbosu.TABLEA;

This approach ensures conversion only occurs when data is confirmed to be numeric, otherwise returning a default value of 0, thus avoiding runtime errors.

Solution Two: Data Filtering

Another method involves filtering out non-numeric data before conversion:

SELECT 
    COLUMNA AS COLUMNA_s,
    CONVERT(DECIMAL(18,2), COLUMNA) AS COLUMNA
INTO st00_TABLEA
FROM dbosu.TABLEA
WHERE ISNUMERIC(COLUMNA) = 1;

This method processes only records confirmed to be convertible to numbers, but excludes all non-numeric data, which may not be suitable for scenarios requiring retention of all records.

Advanced Processing Techniques

For more complex data cleaning requirements, consider the following approaches:

-- Handling scientific notation
SELECT 
    COLUMNA,
    CASE 
        WHEN COLUMNA LIKE '%e%' THEN CONVERT(DECIMAL(18,2), CONVERT(FLOAT, COLUMNA))
        WHEN ISNUMERIC(COLUMNA) = 1 THEN CONVERT(DECIMAL(18,2), COLUMNA)
        ELSE 0
    END AS ConvertedValue
FROM SourceTable;

Usage of TRY_CONVERT Function

In SQL Server 2012 and later versions, the TRY_CONVERT function provides more elegant error handling:

SELECT 
    COLUMNA AS COLUMNA_s,
    TRY_CONVERT(DECIMAL(18,2), COLUMNA) AS COLUMNA
INTO st00_TABLEA
FROM dbosu.TABLEA;

TRY_CONVERT returns NULL instead of throwing an error when conversion fails, making error handling more concise.

Data Cleaning Recommendations

From a long-term perspective, the best solution is to ensure standardized data storage:

• Select appropriate data types during design phase
• Implement data validation rules
• Conduct regular data quality checks
• Establish standardized data input processes

Conclusion

Addressing nvarchar to numeric type conversion errors requires comprehensive consideration of data quality, business requirements, and SQL Server version features. Through proper use of ISNUMERIC validation, conditional conversion, and data filtering, runtime errors can be effectively avoided, ensuring data processing stability. For modern SQL Server versions, TRY_CONVERT offers a more concise and secure solution.