CREATE FUNCTION dbo.ConvertIntToIp ( @AddrInt int )
RETURNS varchar(15)
BEGIN
RETURN CAST(CAST(CAST(( @AddrInt & 0xFF000000 ) / 0x01000000 AS binary(1)) as tinyint) as varchar(3))
+ '.'
+ CAST(CAST(CAST(( @AddrInt & 0xFF0000 ) / 0x010000 AS binary(1)) as tinyint) as varchar(3))
+ '.'
+ CAST(CAST(CAST(( @AddrInt & 0xFF00 ) / 0x0100 AS binary(1)) as tinyint) as varchar(3))
+ '.'
+ CAST(CAST(CAST(( @AddrInt & 0xFF ) AS binary(1)) as tinyint) as varchar(3))
END
GO
CREATE FUNCTION dbo.ConvertIpToInt ( @AddrIp varchar(15) )
RETURNS int
BEGIN
DECLARE @first smallint
DECLARE @second smallint
DECLARE @third smallint
DECLARE @fourth smallint
DECLARE @result int
DECLARE @test varchar(4)
DECLARE @cur_loc tinyint
DECLARE @last_loc tinyint
-- Premier Bloc
SET @cur_loc = CHARINDEX('.', @AddrIp)
SET @test = LEFT(LEFT(@AddrIp, @cur_loc - 1), 3)
IF PATINDEX('%[^0-9]%', @test) = 0
BEGIN
SET @first = CAST(@test AS smallint)
-- Second Bloc
SET @last_loc = @cur_loc + 1
SET @cur_loc = CHARINDEX('.', @AddrIp, @last_loc)
SET @test = LEFT(SUBSTRING(@AddrIp, @last_loc,
@cur_loc - @last_loc), 3)
IF PATINDEX('%[^0-9]%', @test) = 0
BEGIN
SET @second = CAST(@test AS smallint)
-- Troisième Bloc
SET @last_loc = @cur_loc + 1
SET @cur_loc = CHARINDEX('.', @AddrIp, @last_loc)
SET @test = LEFT(SUBSTRING(@AddrIp, @last_loc,
@cur_loc - @last_loc), 3)
IF PATINDEX('%[^0-9]%', @test) = 0
BEGIN
SET @third = CAST(@test AS smallint)
-- Quatrième Bloc
SET @last_loc = @cur_loc + 1
SET @test = LEFT(SUBSTRING(@AddrIp, @last_loc,
LEN(@AddrIp)
- @last_loc + 1), 3)
IF PATINDEX('%[^0-9]%', @test) = 0
BEGIN
SET @fourth = CAST(@test AS smallint)
END
END
END
END
-- Résultat
IF @first <= 255
AND @second <= 255
AND @third <= 255
AND @fourth <= 255
BEGIN
IF @first <= 127
SET @result = CAST(@first as int) * 0x1000000
+ CAST(@second as int) * 0x10000 + CAST(@third as int)
* 0x100 + CAST(@fourth as int)
ELSE
SET @result = ( CAST(( @first & 0x7F ) as int) * 0x1000000
+ CAST(@second as int) * 0x10000
+ CAST(@third as int) * 0x100
+ CAST(@fourth as int) ) | 0x80000000
END
RETURN @result
END
GO