Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Complement of ISNUMERIC function

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

Print | posted on Friday, August 14, 2009 12:18 AM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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
8/17/2009 1:06 AM | John Chapman
Gravatar

# 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!
8/17/2009 3:19 PM | Robert Carnegie
Gravatar

# 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

8/17/2009 7:23 PM | David Korb
Gravatar

# 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.
9/16/2009 11:21 PM | Paul
Gravatar

# re: Complement of ISNUMERIC function

In the CASE statement above, wrap the column name in a CONVERT statement.

CONVERT(VARCHAR(100), Col1)
9/16/2009 11:26 PM | Peso
Gravatar

# 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?
9/16/2009 11:44 PM | Paul
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET