Understanding the IsNumeric() function in T-SQL

Overview

The isNumeric() function tells you whether or not an expression is a valid numeric data type.
It will return 1 when it is numeric and 0 when it is not.
See http://msdn.microsoft.com/en-us/library/ms186272.aspx

Script Example

This can also be altered to deal with other types of functions
USE tempdb;
GO

CREATE TABLE IsNumericDemo (ID smallint identity primary key,
NumberString varchar(32),[IsNumeric] bit )

GO
INSERT IsNumericDemo(NumberString) VALUES ('3')
INSERT IsNumericDemo(NumberString) VALUES ('$')
INSERT IsNumericDemo(NumberString) VALUES ('$100')
INSERT IsNumericDemo(NumberString) VALUES ('$200.01')
INSERT IsNumericDemo(NumberString) VALUES ('-$300')
INSERT IsNumericDemo(NumberString) VALUES ('$-400')
INSERT IsNumericDemo(NumberString) VALUES ('$123,568.00')
INSERT IsNumericDemo(NumberString) VALUES ('$234.568.00')
INSERT IsNumericDemo(NumberString) VALUES ('3.E4')
INSERT IsNumericDemo(NumberString) VALUES ('FFFF')
INSERT IsNumericDemo(NumberString) VALUES ('2^10')
INSERT IsNumericDemo(NumberString) VALUES ('0000001')
INSERT IsNumericDemo(NumberString) VALUES ('1234+')
INSERT IsNumericDemo(NumberString) VALUES ('+1234')
INSERT IsNumericDemo(NumberString) VALUES ('twenty')
INSERT IsNumericDemo(NumberString) VALUES ('(40)')
INSERT IsNumericDemo(NumberString) VALUES ('-40')
GO

 

-- Set the IsNumeric flag

UPDATE IsNumericDemo 
SET [IsNumeric] = ISNUMERIC(NumberString)

 

-- Check results

SELECT id,numberstring,CASE [isnumeric] WHEN 1 THEN 'yes' ELSE 'no' END AS 'isNumeric'
FROM IsNumericDemo

GO

DROP TABLE IsNumericDemo
go