Problem:

Have you ever encountered an error while trying to convert a VARCHAR value to a numeric data type in SQL Server? You might have received the error message “Error converting data type varchar to numeric” even though the value appeared to be a valid decimal. This can be frustrating, especially when other decimal values from the same source convert without any issues. Additionally, even the ISNUMERIC function fails to recognize these values as numeric.

Solution:

Let’s take a look at an example to understand the issue:

SELECT CAST('1.000000' AS DECIMAL(22,8))

In this example, the value ‘1.000000’ seems like a valid decimal, but when we try to cast it to a DECIMAL data type, we encounter the error. This issue occurs because these rare values, although appearing as numerical characters, fail to convert or cast as decimals and also fail the ISNUMERIC function.

So, how can we handle these error-prone values? Here’s a step-by-step solution:

1. Extract the characters on the left side of the decimal place using the LEFT function:

LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) PreDecimal

2. Extract the characters after the decimal place using the SUBSTRING function:

SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),6) PostDecimal

3. Add the decimal point between the PreDecimal and PostDecimal characters:

LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),6) FormattedVarchar

4. Wrap the CAST function around the PreDecimal, decimal, and PostDecimal to convert it to a DECIMAL data type:

CAST(LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),3) AS DECIMAL(13,6)) CastedNumeric

By following these steps, you can convert these problematic VARCHAR values to numeric data points without encountering any errors.

Here’s an example of how you can implement this solution:

CREATE TABLE VarcharExample (
    ExampleColumn VARCHAR(100)
)

INSERT INTO VarcharExample
VALUES ('20.0000'),
       ('357.500'),
       ('1226.00'),
       ('71.0'),
       ('36987.200000'),
       ('45.16710'),
       ('9645.00')

SELECT LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) PreDecimal,
       SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),6) PostDecimal,
       LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),6) FormattedVarchar,
       CAST(LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),3) AS DECIMAL(13,6)) CastedNumeric
FROM VarcharExample

DROP TABLE VarcharExample

By using this approach, you can quickly and effectively convert these problematic VARCHAR values to their correct numeric format.

Additionally, if you have invalid alpha characters in your dataset, you can strip them using RegEx before applying the CAST function.

Here’s an example:

CREATE TABLE VarcharExample (
    ExampleColumn VARCHAR(100)
)

INSERT INTO VarcharExample
VALUES ('20.0000'),
       ('357.500'),
       ('1226.00'),
       ('71.0'),
       ('36987.200000'),
       ('45.16710'),
       ('9645.00'),
       ('Dog'),
       ('$1.00'),
       ('12E33')

;WITH ClearAlphas AS (
    SELECT ExampleColumn
    FROM VarcharExample
    WHERE ExampleColumn NOT LIKE '%[A-za-z$]%'
), ParseNumerics AS (
    SELECT CAST(LEFT(ExampleColumn, CHARINDEX('.', ExampleColumn) - 1) + '.' + SUBSTRING(ExampleColumn,(CHARINDEX('.',ExampleColumn)+1),3) AS DECIMAL(22,8)) CastedNumeric
    FROM ClearAlphas
)
SELECT *
FROM ParseNumerics

DROP TABLE VarcharExample

In this example, the rows containing “Dog”, “$1.00”, and “12E33” are excluded from the result set, as they contain invalid alpha characters.

Remember, encountering these types of data is rare, but having a quick and effective workaround can save you time and effort when dealing with them. These situations are often found in financial data sources.

Now you have the knowledge to handle the error converting data type VARCHAR to numeric in SQL Server. Happy coding!