SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

January 10, 2013 by Joey Blue Leave a Comment

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.  IsNumeric SQL Server

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

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

 

 

Isnumeric function where filter

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

Filed Under: SQL Training Tagged With: Decimal, Isnumeric, Numeric, SQL, SQL Server 2012

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2025 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com