[SQL Server] GeoIP, IP2Location등의 비교를 위해서 IP주소를 숫자(bigint)형식으로 변경

MS-SQL / SQL Server

GeoIP, IP2Location등의 비교를 위해서 IP주소를 숫자(bigint)형식으로 변경

declare @result bigint

declare @fdIP varchar(15)

set @fdIP = '122.208.20.106'

DECLARE @fdIP1 bigint, @fdIP2 bigint, @fdIP3 bigint, @fdIP4 bigint

SELECT @fdIP1 = PARSENAME(@fdIP, 4)

SELECT @fdIP2 = PARSENAME(@fdIP, 3)

SELECT @fdIP3 = PARSENAME(@fdIP, 2)

SELECT @fdIP4 = PARSENAME(@fdIP, 1)

SET @result = @fdIP1*(256*256*256)+@fdIP2*(256*256)+@fdIP3*(256)+@fdIP4

 

SELECT @result 

 

 

 

--- 혹은 이렇게 ---------------------------------------------------------------------------

 

SELECT 

IpAddr

, CONVERT(BIGINT,(PARSENAME(IpAddr, 4)))*(256*256*256)

+ CONVERT(BIGINT,(PARSENAME(IpAddr, 3)))*(256*256)

+ CONVERT(BIGINT,(PARSENAME(IpAddr, 2)))*(256)

+ CONVERT(BIGINT,(PARSENAME(IpAddr, 1))) AS IpNumber1)) AS IpNumber

FROM 

(

 SELECT '122.208.20.106' IpAddr

) A