if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProvanceIpIsValid]') and (OBJECTPROPERTY(id, N'IsScalarFunction') = 1 OR OBJECTPROPERTY(id, N'IsTableFunction') = 1))
drop function [dbo].[ProvanceIpIsValid]
GO
CREATE FUNCTION [dbo].[ProvanceIpIsValid]
(
@IP VARCHAR(8000)
)
RETURNS BIT
AS
BEGIN
/*
This function returns 1 is the @IP is a valid Ip address based on the comments below.
*/
RETURN IIF(@IP LIKE '%_.%_.%_.%_' -- 3 periods and no empty octets
AND @IP NOT LIKE '%.%.%.%.%' -- not 4 periods or more
AND @IP NOT LIKE '%[^0-9.]%' -- no characters other than digits and periods
AND @IP NOT LIKE '%[0-9][0-9][0-9][0-9]%' -- not more than 3 digits per octet
AND @IP NOT LIKE '%[3-9][0-9][0-9]%' -- NOT 300 - 999
AND @IP NOT LIKE '%2[6-9][0-9]%' -- NOT 260 - 299
AND @IP NOT LIKE '%25[6-9]%' -- NOT 256 - 259
, 1, 0)
END
GO
Home