SQL Server Note

文字列IPを10進IPに変換

ドット区切り(0.0.0.0)のIPアドレスを10進数のIPアドレスに変換する方法です。

SELECT NAME,IP, 
CASE ISNUMERIC(REPLACE(IP,'.','')) WHEN 1 THEN
RIGHT('00' + PARSENAME(IP, 4), 3) * POWER(CONVERT(bigint,256), 3) + 
RIGHT('00' + PARSENAME(IP, 3), 3) * POWER(CONVERT(bigint,256), 2) + 
RIGHT('00' + PARSENAME(IP, 2), 3) * POWER(CONVERT(bigint,256), 1) + 
RIGHT('00' + PARSENAME(IP, 1), 3)
ELSE NULL END
AS CONVIP
FROM PC
NAMEIPCONVIP
CL-BADABC.DEF.GHI.JKLNULL
CL-NONE192.168NULL
CL-SAITO192.168.1.1023232235878
CL-SHARE192.168.1.1053232235881
CL-UEDANULLNULL
CL-YOSHIDA192.168.1.1043232235880
SV-FILE192.168.1.33232235779
SV-MAIL192.168.1.183232235794
SV-WEB192.168.1.053232235781
Copyright © 2006 Hikijishi All Rights Reserved.
[] [sql][0.0011441707611084]