How to test numeric in SQL Server

Sometimes you will run into a database table that is storing numeric data inside of a varchar or nvarchar column.

This is not recommended, but it does happen. 

Normally you would store numeric data in a numeric or decimal column and the database will make sure the data has only numbers.

If the data is in a nvarchar column, then the database won’t care if the data is a number or a date or strings/characters.

In this case you will need to check manually to see if the column only has numeric data.

IsNumeric is a function in SQL Server that you can use.

Here’s a script to show you how to use it.

create table products
(
item nvarchar(100),
price nvarchar(100)
)
go
insert into products
select 'product1','-100'
union
select 'product2','25.42'
union
select 'product3','test'
union
select 'product4','0.01'
go

select item,price,isnumeric(price) numeric_check
from products;

select item,price,isnumeric(price) numeric_check
from products
where isnumeric(price) = 0;

Note that there are a few problems with the IsNumeric Function:

http://www.sql-server-performance.com/forum/threads/problem-with-isnumeric.11181/

http://www.simple-talk.com/blogs/2011/01/13/isnumeric-broken-only-up-to-a-point/

Here are a few other posts you might enjoy:

How to Filter for SQL Null or Empty String | SQL Functions | SQL Where