Check if a String is all Digits

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Jeff Moden

Date: 8 Dec 2010

Description

A long standing bone of contention with many TSQL developers, especially those whose work encompasses any form of data import, is the wooliness of the ISNUMERIC check.

Its name would suggest that is will only return true if the string presented to it is a numeric. The crunch comes with what is considered to be numeric. A quick check of the MSDN definition of ISNUMERIC() reveals that +/-/e/./$ etc are considered to be a valid component of a numeric value. This is fine for FLOAT, DECIMAL and MONEY datatypes, but with an INTEGER data type, the ISNUMERIC() check is not enough to guarantee a correct answer without resorting to additional character stripping/checks.

There is no ISINTEGER() function, so Jeff Moden has written ~~a quick and dirty~~ an elegant replacement that does a good job. It has been written as a table valued function, but it would be very straightforward to convert to a scalar function. I suspect this was a deliberate omission, as inline functions used against large volumes of data (as this might well be) can be very resource hungry.

Code

DROP FUNCTION dbo.udf_IsAllDigits

GO

CREATE FUNCTION dbo.udf_IsAllDigits

/********************************************************************

Purpose:

This function will return a 1 if the string parameter contains only

numeric digits and will return a 0 in all other cases. Use it in

a FROM clause along with CROSS APPLY when used against a table.

--Jeff Moden

********************************************************************/

--===== Declare the I/O parameters

(@MyString VARCHAR(8000))

RETURNS TABLE AS

RETURN (

SELECT CASE

WHEN @MyString NOT LIKE '%[^0-9]%'

THEN 1

ELSE 0

END AS IsAllDigits

)

/*

SELECT * FROM dbo.udf_IsAllDigits('123456')

SELECT * FROM dbo.udf_IsAllDigits('123.456')

SELECT * FROM dbo.udf_IsAllDigits('$123456')

SELECT * FROM dbo.udf_IsAllDigits('-123456')

*/