### [SQL][Script]利用使用者定義函數解決 IP 比對的問題

[SQL][Script]利用使用者定義函數解決 IP 比對的問題

`CREATE FUNCTION [dbo].[IPAddressConvertInt]( @IPAddress varchar(15) ) RETURNS BIGINT`
`AS`
`BEGIN`
`   RETURN ( `
`   CAST(PARSENAME(@IPAddress,1) AS BIGINT ) +`
`   CAST(PARSENAME(@IPAddress,2) AS BIGINT ) * Power(256,1)+ `
`   CAST(PARSENAME(@IPAddress,3) AS BIGINT ) * Power(256,2)+`
`   CAST(PARSENAME(@IPAddress,4) AS BIGINT ) * Power(256,3) )`
`END`

`CREATE FUNCTION [dbo].[IsIPAddressInRange]`
`(`
`    @IPAddress varchar(15), @StartRange varchar(15), @EndRange varchar(15)`
`)`
`RETURNS INT`
`AS`
`BEGIN`
`    RETURN `
`    CASE `
`      WHEN  dbo.IPAddressConvertInt(@IPAddress) BETWEEN dbo.IPAddressConvertInt(@StartRange) AND dbo.IPAddressConvertInt(@EndRange) THEN 1`
`      ELSE 0`
`    END        `
`END`

`CREATE FUNCTION [dbo].[IsIPAddressInCIDR]`
`(`
`    @IPAddress varchar(15), @IPRange varchar(20)`
`)`
`RETURNS INT`
`AS`
`BEGIN`
`    DECLARE @SlashPos INT = CHARINDEX('/', @IPRange);`
`    DECLARE @Network  VARCHAR(12) = SUBSTRING(@IPRange, 1, @SlashPos - 1);`
`    DECLARE @PrefixBits INT = CAST(SUBSTRING(@IPRange, @SlashPos + 1, 20) AS INT);`
` `
`    DECLARE @Mask BIGINT = POWER(CAST(2 AS BIGINT), 32) - POWER(CAST(2 AS BIGINT), 32 - @PrefixBits);`
` `
`    RETURN `
`    CASE `
`      WHEN  dbo.IPAddressConvertInt(@IPAddress) & @Mask = dbo.IPAddressConvertInt(@Network) & @Mask THEN 1`
`      ELSE 0`
`    END        `
`END`

`SELECT dbo.IPAddressConvertInt( '192.168.3.233' )`
`SELECT dbo.IsIPAddressInRange( '192.168.3.233', '192.168.2.1' , '192.168.4.254' )`
`SELECT dbo.IsIPAddressInCIDR  ( '192.168.3.233', '192.168.2.0/23' )`