IsInt and IsDecimal from IsNumeric

In TSQL IsNumeric evaluates data to see if it can be cast as any of ten numeric types of data: int, numeric, bigint, money, smallint, smallmoney, tinyint, float, decimal, real. The problem is that most of the time what we really and practically want to know is, is this number an integer? Or, is this number a decimal? Sadly, SSMS doesn’t come with a built in system function for IsInt or IsDecimal. But the good news is that you can build a user defined function (UDF) that will do the same thing.
I’m going to show you how do write the function (actually I’m going to give it to you already written), and show you how to make use of it once it’s up and running, but first I have to give credit where credit is due. The basic code for evaluating integer and decimal values was posted at StackOverflow. I added a IsNull and rtrim to clean it up even more:
IsNumeric(rtrim(ISNULL(myvarchar,1)) + ‘e0’)
Basically, all you need to do is add scientific notation (e0) to your value when parsing IsNumeric thus eliminating numbers that are already expressed exponentially, as often happens when copying/pasting from Excel. There’s a notation for integer and a notation for decimal.
Here’s my UDF for IsInt:

/*
 Name: IsInt
 Author Tim Bartel
 Created on 5/30/2014
 Purpose: create a function for evaluating if a varchar can be cast as an int.
 Returns boolean.
 0 = No, cannot cast to int
 1 = Yes, can cast to int
 */
 If object_id('dbo.IsInt') IS NOT NULL
 DROP FUNCTION dbo.IsInt
 GO
 CREATE FUNCTION dbo.IsInt (@myvarchar varchar(max))
 RETURNS int
 AS
 BEGIN
 DECLARE @intCheck int;
 SELECT @intCheck=IsNumeric(rtrim(ISNULL(@myvarchar,1)) + '.0e0')
 RETURN @intCheck;
 END
 GO

Here’s my UDF for IsDecimal:

 /* 
 Name: IsDecimal
 Author Tim Bartel
 Created on  5/30/2014
 Purpose: create a function for evaluating if a varchar can be cast as a decimal.
 Returns boolean.  
 0 = No, cannot cast to decimal 
 1 = Yes, can cast to decimal
 */
 If object_id('dbo.IsDecimal') IS NOT NULL
            DROP FUNCTION dbo.IsDecimal
GO
  CREATE FUNCTION dbo.IsDecimal (@myvarchar varchar(max))
  RETURNS int
  AS
  BEGIN
  DECLARE @decimalCheck int;
  SELECT @decimalCheck=IsNumeric(rtrim(ISNULL(@myvarchar,1)) + 'e0')
  RETURN @decimalCheck;
  END
GO  

Remember to preface your function by dbo so SQL knows where to look for the function. Yeah, that’s the bummer side of a user defined function; they are only available in the database where you installed them. If you want them in all your databases, you have to install them in all your databases. I have yet to discover a safe and easy method of installing a function as a system function. Let me know if you find such a way.
Now that you’ve executed the scripts above in your databases, you can do nifty things like find all the data that can’t be cast as an decimal… just by a lick of script like this:

 SELECT height FROM Demographics WHERE dbo.IsDecimal(height)=0

Or find data that can be cast as integer like this:

 SELECT age FROM Demographic WHERE dbo.IsInt(age)=1

How about putting that inline like so:
SELECT dbo.IsInt(age) , dbo.IsDecimal(height) FROM Demographic…
It’s a lot easier to remember and write than:
SELECT IsNumeric(rtrim(ISNULL(age,1)) + ‘e0’), IsNumeric(rtrim(ISNULL(height,1)) + ‘.0e0’) FROM Demographic…
Your welcome 😉

BTW, my UDFs above treat nulls as viable data to be cast as int or decimal. I did this because null cast as int or decimal is null. If you want your UDF to treat nulls as non-int or non-decimal – that is to say, you want nulls to fail the IsInt or IsDecimal test – then change 1 to 0 in the IsNull function like so:

 SELECT @intCheck=IsNumeric(rtrim(ISNULL(@myvarchar,0)) + '.0e0')

SELECT @decimalCheck=IsNumeric(rtrim(ISNULL(@myvarchar,0)) + 'e0')

If you like the function as is and just want to occasionally get a result set where nulls are invalid then wrap the field name inside function IsInt or IsDecimal UDF in an IsNull function and set null equal to a value that will fail… like so:

 SELECT dbo.IsDecimal(IsNull(height,'1.2.3')) FROM Demographics

SELECT dbo.IsInt(IsNull(age,'1.2.3')) FROM Demographic

Want a table with mixed data to test this in? No problem…

-- CREATE DATA FOR TESTING ISINT AND ISDECIMAL
CREATE TABLE Demographics (height varchar(7), age varchar(7))

-- Add mixed decimal/non-decimal, int/non-int data
 insert into Demographics (height, age) VALUES
 ('12.345', '0o1'),
 ('12.3456', 'o02'),
 ('`78', 'oo3'),
 ('''78.9', 'IV_4'),
 ('''78', 'V 5'),
 ('6''04', 'six'),
 ('64..5', '&'),
 ('64+', '*'),
 ('6+\4', 'Nine'),
 ('6+4', '010'),
 ('64..25', '1.10'),
 ('62`', '1..2'),
 ('64=5', '12.345'),
 ('68.5.', '`78'),
 ('68/', '''78.9'),
 ('68o', '''78'),
 ('7.0.5', '6''04'),
 ('70in', '64+'),
 ('5*', '6+\4'),
 (' 64', '6+4'),
 (' 64', '62`'),
 (' 64', '64=5'),
 (' 64', '68.5.'),
 (' 64', '68/'),
 ('64.5432', '7.0.5'),
 ('+64', '70in'),
 ('64,75', '5*'),
 ('64.753', ' 64'),
 ('+66.5', ' 64'),
 (' 64.25', ' 64'),
 (' 64.25', ' 64'),
 ('64.25 ', '64.543'),
 ('64.25 ', '+64'),
 ('`64.5', '64,75'),
 ('`64', '+66.5'),
 ('69.5.', ' 64.25'),
 ('11.11', ' 64.25'),
 ('$', '64.25 '),
 ('+', '64.25 '),
 (',', '`64.5'),
 ('-', '`64'),
 ('.', '69.5.'),
 ('€', '11.11'),
 ('£', '$'),
 ('¤', '+'),
 ('¥', ','),
 ('v', '-'),
 ('5.E4', '.'),
 ('5.0E4', '€'),
 ('5e4', '£'),
 ('5e4.e4', '¤'),
 ('5e4.0e0', '¥'),
 (NULL, 'v'),
 (NULL, '5.E4'),
 (NULL, '5.0E4'),
 (NULL, '5e4'),
 (NULL, '5e4.e4'),
 (NULL, '5e4.0e0'),
 (NULL, '+3'),
 (NULL, '-3'),
 (NULL, ''),
 (NULL, ' '),
 (NULL, NULL)
 -- (63 row(s) affected)