SQL Server Note

10進IPを文字列IPに変換

10進数のIPアドレスをゼロサプレスしたIPアドレス(0.0.0.0)に変換する方法です。

SELECT URL,IP, 
CAST(FLOOR(IP / POWER(256, 3)) AS VARCHAR) + '.' + 
CAST(FLOOR(IP / POWER(256, 2)) - FLOOR(IP / POWER(256, 3)) * 256 AS VARCHAR) + '.' + 
CAST(FLOOR(IP / POWER(256, 1)) - FLOOR(IP / POWER(256, 2)) * 256 AS VARCHAR) + '.' + 
CAST(FLOOR(IP / POWER(256, 0)) - FLOOR(IP / POWER(256, 1)) * 256 AS VARCHAR)
AS CONVIP
FROM SITE
URLIPCONVIP
http://CL-NONE/3232235520192.168.0.0
http://CL-SAITO/3232235878192.168.1.102
http://CL-SHARE/3232235881192.168.1.105
http://CL-UEDA/NULLNULL
http://CL-YOSHIDA/3232235880192.168.1.104
http://SV-FILE/3232235779192.168.1.3
http://SV-MAIL/3232235794192.168.1.18
http://SV-WEB/3232235781192.168.1.5

10進数のIPアドレスをゼロパディングしたIPアドレス(000.000.000.000)に変換する方法です。

SELECT URL,IP, 
RIGHT('00' + CAST(FLOOR(IP / POWER(256, 3)) AS VARCHAR), 3) + '.' + 
RIGHT('00' + CAST(FLOOR(IP / POWER(256, 2)) - FLOOR(IP / POWER(256, 3)) * 256 AS VARCHAR), 3) + '.' + 
RIGHT('00' + CAST(FLOOR(IP / POWER(256, 1)) - FLOOR(IP / POWER(256, 2)) * 256 AS VARCHAR), 3) + '.' + 
RIGHT('00' + CAST(FLOOR(IP / POWER(256, 0)) - FLOOR(IP / POWER(256, 1)) * 256 AS VARCHAR), 3)
AS CONVIP
FROM SITE
URLIPCONVIP
http://CL-NONE/3232235520192.168.000.000
http://CL-SAITO/3232235878192.168.001.102
http://CL-SHARE/3232235881192.168.001.105
http://CL-UEDA/NULLNULL
http://CL-YOSHIDA/3232235880192.168.001.104
http://SV-FILE/3232235779192.168.001.003
http://SV-MAIL/3232235794192.168.001.018
http://SV-WEB/3232235781192.168.001.005
Copyright © 2006 Hikijishi All Rights Reserved.
[] [sql][0.000958919525146484]