Contents

1
2 IP to Binary
3 覓語IP襯 Bigint IP襦 覲
4 覓語IP襯 Binary(4) IP襦 覲
5 Bigint IP襯 覓語 IP襦 覲
6 Binary(4) IP襯 覓語 IP襦 覲
7 蠍壱


IP4 binary襦 覲 蟆 リ概螳 譴願, 焔レ レり, 一一 襴.

1 #

select 
	dbo.ufn_IpToBinary('122.167.217.0') 	-- 覓語IP襯朱企襴(Hex)襦覲
,	dbo.ufn_IntToIp(2057820416)		-- BigintIP襯朱語IP襦覲
,	dbo.ufn_IpToInt('122.167.217.0')	-- 覓語IP襯BigintIP襦覲
,	dbo.ufn_BinaryToIP(0x7AA7D900)		-- 覦企襴(Hex)襯朱語IP襦覲

2 IP to Binary #

CASE 
	WHEN ISNUMERIC(REPLACE(IP, '.', '')) = 1 THEN
		CASE 
			WHEN 
				CONVERT(INT,PARSENAME(IP,4)) BETWEEN 0 AND 255
			AND CONVERT(INT,PARSENAME(IP,3))  BETWEEN 0 AND 255
			AND CONVERT(INT,PARSENAME(IP,2))  BETWEEN 0 AND 255
			AND CONVERT(INT,PARSENAME(IP,1))  BETWEEN 0 AND 255  
			THEN 
				CONVERT(BINARY(1), CAST(PARSENAME(IP, 4) AS TINYINT)) +
				CONVERT(BINARY(1), CAST(PARSENAME(IP, 3) AS TINYINT)) +
				CONVERT(BINARY(1), CAST(PARSENAME(IP, 2) AS TINYINT)) +
				CONVERT(BINARY(1), CAST(PARSENAME(IP, 1) AS TINYINT)) 
		END  
END AS IP

3 覓語IP襯 Bigint IP襦 覲 #

create function [dbo].[ufn_IpToInt] (@str_ip varchar(15))
returns bigint
as
begin
	return(
		cast(
			cast(cast(parsename(@str_ip, 4) as tinyint) as binary(1)) + 
			cast(cast(parsename(@str_ip, 3) as tinyint) as binary(1)) + 
			cast(cast(parsename(@str_ip, 2) as tinyint) as binary(1)) + 
			cast(cast(parsename(@str_ip, 1) as tinyint) as binary(1))
		as bigint)
	)
end
go

4 覓語IP襯 Binary(4) IP襦 覲 #

create function [dbo].[ufn_IpToBinary] (@str_ip varchar(15))
returns binary(4)
as
begin
	return (
		CONVERT(binary(1), CAST(PARSENAME(@str_ip, 4) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@str_ip, 3) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@str_ip, 2) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@str_ip, 1) AS tinyint)) 
	)
end
go

5 Bigint IP襯 覓語 IP襦 覲 #

create function [dbo].[ufn_IntToIp] (@int_ip bigint)
returns varchar(15)
as
begin
	--  4294967040 converts to 255.255.255.0
	return(
		cast(cast(substring(cast(@int_ip as binary(4)), 1, 1) as tinyint) as varchar) + '.' +
		cast(cast(substring(cast(@int_ip as binary(4)), 2, 1) as tinyint) as varchar) + '.' +
		cast(cast(substring(cast(@int_ip as binary(4)), 3, 1) as tinyint) as varchar) + '.' +
		cast(cast(substring(cast(@int_ip as binary(4)), 4, 1) as tinyint) as varchar)
	)
end
go

6 Binary(4) IP襯 覓語 IP襦 覲 #

create function [dbo].[ufn_BinaryToIP] (@bin_ip binary(4))
returns varchar(15)
as
begin
	return (
		convert(varchar(3), convert(tinyint, substring(@bin_ip, 1, 1))) + '.' + 
		convert(varchar(3), convert(tinyint, substring(@bin_ip, 2, 1))) + '.' + 
		convert(varchar(3), convert(tinyint, substring(@bin_ip, 3, 1))) + '.' + 
		convert(varchar(3), convert(tinyint, substring(@bin_ip, 4, 1)))
	)
end
go

7 蠍壱 #

declare @ip varchar(15)
set @ip = '121.153.53.57'
--121.168.96.235

select
	ip_binary
,	convert(varchar, convert(smallint, substring(ip_binary, 1, 1))) + '.' +
	convert(varchar, convert(smallint, substring(ip_binary, 2, 1))) + '.' +
	convert(varchar, convert(smallint, substring(ip_binary, 3, 1))) + '.' +
	convert(varchar, convert(smallint, substring(ip_binary, 4, 1))) ip_string
from(
	select
		CONVERT(binary(1), CAST(PARSENAME(@ip, 4) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@ip, 3) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@ip, 2) AS tinyint)) +
		CONVERT(binary(1), CAST(PARSENAME(@ip, 1) AS tinyint)) ip_binary
) t

CASE 
	WHEN ISNUMERIC(REPLACE(IP, '.', '')) = 1 THEN
		CASE 
			WHEN 
				CONVERT(INT,PARSENAME(IP,4)) BETWEEN 0 AND 255
			AND CONVERT(INT,PARSENAME(IP,3))  BETWEEN 0 AND 255
			AND CONVERT(INT,PARSENAME(IP,2))  BETWEEN 0 AND 255
			AND CONVERT(INT,PARSENAME(IP,1))  BETWEEN 0 AND 255  
			THEN 
				CONVERT(BINARY(1), CAST(PARSENAME(IP, 4) AS TINYINT)) +
				CONVERT(BINARY(1), CAST(PARSENAME(IP, 3) AS TINYINT)) +
				CONVERT(BINARY(1), CAST(PARSENAME(IP, 2) AS TINYINT)) +
				CONVERT(BINARY(1), CAST(PARSENAME(IP, 1) AS TINYINT)) 
		END  
END AS IP