- 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
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