How to find non-numeric values in SQL Server

Have you ever encountered an error like this when trying to convert a varchar column to a numeric column in SQL Server?

Error converting data type varchar to numeric

This error occurs when the varchar column contains some values that are not valid for the numeric data type, such as alphabets, symbols, spaces, commas, etc.

In this article, I will show you how to find these non-numeric values using some simple SQL queries.

Finding non-numeric values

There are two main ways to find non-numeric values in a varchar column in SQL Server. One way is to use the ISNUMERIC() function, which returns 1 for numeric values and 0 for non-numeric values. Another way is to use the LIKE operator with a pattern that matches any character that is not a digit.

For example, suppose we have a table called Payments with a column called Amount that contains some varchar values. Some of them are numeric, some of them are not. Here is a sample of the data:

Amount
100.00
50.00
75.00
80
90.00
AB
ABCDE#
.ABC

To find the non-numeric values using the ISNUMERIC() function, we can use this query:

Select Amount
from Payments
where ISNUMERIC(Amount) <> 1

This will return the following rows:

Amount
AB
ABCDE#
.ABC

To find the non-numeric values using the LIKE operator, we can use this query:

Select Amount
from Payments
where Amount like '%[^0-9]%'

This will return the same rows as before:

Amount
AB
ABCDE#
.ABC

Conclusion

In this article, I have shown you how to find non-numeric values in a varchar column in SQL Server using some simple SQL queries. I hope this helps you understand the cause of the error Error converting data type varchar to numeric and prepare your data for conversion.

If you have any questions or feedback, please let me know in the comments below.

Happy coding!