Friday, 7 June 2013

Whats wrong with IsNumeric() Or Alternative of isnumeric

SQL's ISNUMERIC() function has a problem. It can falsely interpret non-numeric letters and symbols (such as D, E ,',' ), and even tabs (CHAR(9)) as numeric.like 12,90

CREATE FUNCTION dbo.isReallyNumeric
(
    @num VARCHAR(64)
)
RETURNS BIT
BEGIN
    IF LEFT(@num, 1) = '-'
        SET @num = SUBSTRING(@num, 2, LEN(@num))

    DECLARE @pos TINYINT

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

    RETURN CASE
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0
        AND @num NOT IN ('.', '-', '+', '^')
        AND LEN(@num)>0
        AND @num NOT LIKE '%-%'
        AND
        (
            ((@pos = LEN(@num)+1)
            OR @pos = CHARINDEX('.', @num))
        )
    THEN
        1
    ELSE
    0
    END
END
go
----------------------------------
CREATE FUNCTION dbo.isReallyInteger
(
    @num VARCHAR(64)
)
RETURNS BIT
BEGIN
    IF LEFT(@num, 1) = '-'
        SET @num = SUBSTRING(@num, 2, LEN(@num))

    RETURN CASE
    WHEN PATINDEX('%[^0-9-]%', @num) = 0
        AND CHARINDEX('-', @num) <= 1
        AND @num NOT IN ('.', '-', '+', '^')
        AND LEN(@num)>0
        AND @num NOT LIKE '%-%'
    THEN
        1
    ELSE
        0
    END
END
GO

No comments:

Post a Comment

Get all non-clustered indexes

DECLARE cIX CURSOR FOR     SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID         FROM Sys.Indexes SI             ...