[SQL SERVER]使用TSQL判斷數值型態
SQL2012以後版本多了Try_XXX函數比較方便，
但如果是SQL2012以前的版本可以使用下列函數進行判斷
create function [dbo].[UF_IsInt](
@inputstr varchar(1000)
)
returns char(1)
WITH RETURNS NULL ON NULL INPUT,
SCHEMABINDING
as
begin
declare @result char(1)
select @result=
CASE
WHEN @inputstr LIKE '%[.%$&*\Ø+]%' THEN 'N'
WHEN @inputstr LIKE '[^0-9]%' and @inputstr LIKE '[^-0-9]%'
THEN 'N'
WHEN ISNUMERIC(@inputstr) = 0 THEN 'N'
WHEN CAST(@inputstr AS NUMERIC(38, 0))
NOT BETWEEN -2147483648. AND 2147483647. THEN 'N'
ELSE 'Y'
END
return isnull(@result,'N')
end
GO
