[MS-SQL] IP 스트링 변환하기

**
플젝하다보면, IP Address를 bigint로 저장하고 실제 UI 에서는 스트링으로 보여주는 경우가 많습니다.
아래의 함수 2개를 이용하여 이래저래 변환하면 편리합니다.

-- 1. bigint 로 저장된 IP를 스트링으로 변환하는 함수
CREATE FUNCTION [dbo].[usf_convert_int_to_ip]
(
        @int_ip_ BIGINT
)
        RETURNS VARCHAR(15)
AS
BEGIN

        DECLARE        @str_ip        VARCHAR(15)
        DECLARE        @bin         VARBINARY(4)

        SELECT @bin         = CAST(@int_ip_ AS VARBINARY(4))
        SELECT @str_ip         = CAST(CONVERT(INT,SUBSTRING(@bin,1,1)) AS VARCHAR(3)) + '.'
                                        + CAST(CONVERT(INT,SUBSTRING(@bin,2,1)) AS VARCHAR(3)) + '.'
                                        + CAST(CONVERT(INT,SUBSTRING(@bin,3,1)) AS VARCHAR(3)) + '.'
                                        + CAST(CONVERT(INT,SUBSTRING(@bin,4,1)) AS VARCHAR(3))

        RETURN @str_ip

END

-- 2. IP 스트링을 bigint로 변환하는 함수
CREATE FUNCTION [dbo].[afn_ConvertIPtoInt]
(
        @str_ip_ nvarchar(4000)
)
        returns bigint
as
begin
         declare @str_ip1 nvarchar(1000)
         declare @str_ip2 nvarchar(1000)
         declare @str_ip3 nvarchar(1000)
         declare @str_ip4 nvarchar(1000)
         declare @int_ip  bigint
        
         -- separate ip value
         select @str_ip1 = substring(@str_ip_, 1, charindex('.', @str_ip_)-1)
         select @str_ip2 = substring(@str_ip_, len(@str_ip1)+2,
                   charindex('.', right(@str_ip_, len(@str_ip_)-len(@str_ip1)-1))-1)
         select @str_ip3 = substring(@str_ip_, len(@str_ip1)+len(@str_ip2)+3,
                   charindex('.', right(@str_ip_, len(@str_ip_)-len(@str_ip1)-len(@str_ip2)-2))-1)
         select @str_ip4 = substring(@str_ip_, len(@str_ip1)+len(@str_ip2)+len(@str_ip3)+4,
                   len(@str_ip_)-len(@str_ip1)-len(@str_ip2)-len(@str_ip3)-3)
        
         -- calculate ip value
         select @int_ip = convert(bigint, @str_ip4) +
                 convert(bigint, @str_ip3)*power(16, 2) +
                 convert(bigint, @str_ip2)*power(16, 4) +
                 convert(bigint, @str_ip1)*power(16, 6)
         return @int_ip 
        
end

- 2010,.06.23 이전 홈피 작성글 이관

**
어느날 IP 관련 함수 필요해서 기존 만들어진 것을 사용하려다 보니, 훨씬 깔끔한 방법으로 만들어 둔 분이 있군요. 여튼 아래 함수가 훨씬 깔끔하고 좋네요.
- 2011.05.18 Joshua95

[퍼온글] 출처: http://splee75.tistory.com/7
-----------------------------------------

/*

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

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