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;

![Isnumeric function results](../../assets/blog/2013/01/image.png)

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