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 기타 소스

select 
	dbo.ufn_IpToBinary('122.167.217.0') 	-- 문자형IP를바이너리(Hex)로변환
,	dbo.ufn_IntToIp(2057820416)		-- Bigint형IP를문자형IP로변환
,	dbo.ufn_IpToInt('122.167.217.0')	-- 문자형IP를Bigint형IP로변환
,	dbo.ufn_BinaryToIP(0x7AA7D900)		-- 바이너리(Hex)를문자형IP로변환

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

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

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

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

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

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