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
Leave a Question, Comment, or Reply. All are welcome!