Cool Tips‎ > ‎Data Type‎ > ‎

Data Type - 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')

*/


ċ
udf_IsAllDigits.sql
(1k)
Andy Hughes,
Jun 19, 2012, 11:37 AM
Comments