Technical Article

ISINTEGER function

,

This function is similar to the ISNUMERIC native function, but tests for a valid integer.  ISNUMERIC can return false positive results when testing for a valid integer.  For example this select returns a value of 1 (true):
select isnumeric('3d8')

For 6.x and 7.0, you can easily convert it to a procedure.

-- =============================================
-- Drop Function
-- =============================================
IF EXISTS ( select * from information_schema.routines  
  where SPECIFIC_SCHEMA = 'dbo' and SPECIFIC_NAME = N'fn_ISINTEGER'  and ROUTINE_TYPE = 'FUNCTION ' ) 
DROP FUNCTION dbo.fn_ISINTEGER
go

-- =============================================
-- Create Function
-- =============================================
CREATE FUNCTION dbo.fn_ISINTEGER 
( 
  @strToBeEval varchar(1000) -- enlarge this if needed
)
RETURNS bit

AS
BEGIN
DECLARE @bitReturn bit
IF @strToBeEval LIKE '%[^0-9]%'
SET @bitReturn = 0
ELSE
SET @bitReturn = 1

RETURN @bitReturn
END


GO

-- =============================================
-- Example to execute function
-- =============================================

SELECT dbo.fn_ISINTEGER ('1234567890')
SELECT dbo.fn_ISINTEGER ('123456789^00')

GO

Rate

5 (3)

Share

Share

Rate

5 (3)