字符型IP和数字IP之间的转换

字符型IP和数字IP之间的转换

 

 

create function [dbo].[ip_to_int](@ip varchar(15))

returns bigint

as begin

 

DECLARE @IP_INT BIGINT;

 

SELECT @IP_INT=CONVERT(BIGINT,CONVERT(BINARY(1),CAST(parsename(@ip,1) AS INT))

    + CONVERT(BINARY(1),CAST(parsename(@ip,2) AS INT))

    + CONVERT(BINARY(1),CAST(parsename(@ip,3) AS INT))

    + CONVERT(BINARY(1),CAST(parsename(@ip,4) AS INT)));

 

RETURN(@IP_INT)

END

/*

SELECT DBO.[ip_to_int]('218.83.152.2')

 

 

--------------------

43537370

 

(1 行受影响)

*/

 

create function [dbo].[int_to_ip](@ip_int bigint)

returns varchar(20)

as

begin

declare @ip  varchar(15);

 

select @ip=ltrim(convert(int,substring(CONVERT(binary(4),@ip_int),4,1)))+'.'+

           ltrim(convert(int,substring(CONVERT(binary(4),@ip_int),3,1)))+'.'+

           ltrim(convert(int,substring(CONVERT(binary(4),@ip_int),2,1)))+'.'+

           ltrim(convert(int,substring(CONVERT(binary(4),@ip_int),1,1)));

 

return(@ip)

end

 

 

/*

SELECT DBO.[int_to_ip](43537370)

 

 

--------------------

218.83.152.2

 

(1 行受影响)

 

*/