SQL SERVER : CONVERSION IP EN CHAÎNE DE TEXTE VERS INTEGER ET INVERSE

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