2009. 1. 13. 12:31
MSSQL에서 IP 관련 형변환 함수 Work/SQL Server2009. 1. 13. 12:31
/*
1 전체 함수 예제
2 문자열IP를 Bigint형 IP로 변환
3 문자열IP를 Binary(4)형 IP로 변환
4 Bigint형 IP를 문자열 IP로 변환
5 Binary(4)형 IP를 문자열 IP로 변환
*/
-- 1 전체 함수 예제
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로변환
-- 2 문자열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
--3 문자열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
--4 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
--5 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
언젠가 써먹을 거 같아서 퍼왔습니다.
출처는 아마도... http://blog.ithero.co.kr/blog_post_159.aspx
