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