Peter Larsson Blog

Patron Saint of Lost Yaks

Complement of ISNUMERIC function

by Peter Larsson in  , , ,

Today I was involved in an interesting topic about how to check if a text string really is integer or not.
This is what I finally suggested.

CREATE FUNCTION dbo.fnIsINT
(
    @Data NVARCHAR(11)
)
RETURNS INT
AS
BEGIN
    RETURN  CASE
                WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL
                WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL
                WHEN @Data IN('-', '+') THEN NULL
                WHEN CAST(@Data AS BIGINT) NOT BETWEEN -2147483648 AND 2147483647 THEN NULL
                ELSE CAST(@Data AS INT)
            END
END

And the BIGINT alternative

CREATE FUNCTION dbo.fnIsBIGINT
(
    @Data NVARCHAR(20)
)
RETURNS BIGINT
AS
BEGIN
    RETURN  CASE
                WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL
                WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL
                WHEN @Data IN('-', '+') THEN NULL
                WHEN CAST(@Data AS DECIMAL(20, 0)) NOT BETWEEN -9223372036854775808 AND 9223372036854775807 THEN NULL
                ELSE CAST(@Data AS BIGINT)
            END
END

Legacy Comments
John Chapman
2009-08-17		 re: Complement of ISNUMERIC function
Have you thought about using try/catch, eg:

Declare @text varchar(32), @bIntVal bit, @v int

Select @text = '-32'


Begin Try
Select @v = cast(@text as int)
Set @bIntVal = 1
End Try
Begin Catch
Set @bIntVal = 0
End Catch

Select @bIntVal, @v
Robert Carnegie
2009-08-17		 re: Complement of ISNUMERIC function
At the risk of duplicating the discussion elsewhere, I think on its own your function for int will truncate an inline parameter adversely with no indication, i.e. consider

select dbo.fnIsInt('-214748364800000000')

(But only when it's signed or begins with 0.)

I'm not sure how to fix that absolutely. I assume you don't want to throw an error, but that works. Maybe enforce that the input is known type "sysname". ;-)

Also, you're not allowing for instance '1e9' as an integer, but that's being picky, since CAST() also doesn't!
David Korb
2009-08-17		 re: Complement of ISNUMERIC function
Hi John, Currently, we can't use Try..Catch in functions:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=132225
Paul
2009-09-16		 re: Complement of ISNUMERIC function
I tried this function SQL 2008 . its giving me this error:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.
Peso
2009-09-16		 re: Complement of ISNUMERIC function
In the CASE statement above, wrap the column name in a CONVERT statement.

CONVERT(VARCHAR(100), Col1)
Paul
2009-09-16		 re: Complement of ISNUMERIC function
Thanks for quick response! it works fine. How can I use this function to return decimals (5 places). Currently this function only returns Integer. But if its decimal it doesnot return ?what should i edit?