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 CONVIPFROM SITE
| URL | IP | CONVIP |
|---|---|---|
| http://CL-NONE/ | 3232235520 | 192.168.0.0 |
| http://CL-SAITO/ | 3232235878 | 192.168.1.102 |
| http://CL-SHARE/ | 3232235881 | 192.168.1.105 |
| http://CL-UEDA/ | NULL | NULL |
| http://CL-YOSHIDA/ | 3232235880 | 192.168.1.104 |
| http://SV-FILE/ | 3232235779 | 192.168.1.3 |
| http://SV-MAIL/ | 3232235794 | 192.168.1.18 |
| http://SV-WEB/ | 3232235781 | 192.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 CONVIPFROM SITE
| URL | IP | CONVIP |
|---|---|---|
| http://CL-NONE/ | 3232235520 | 192.168.000.000 |
| http://CL-SAITO/ | 3232235878 | 192.168.001.102 |
| http://CL-SHARE/ | 3232235881 | 192.168.001.105 |
| http://CL-UEDA/ | NULL | NULL |
| http://CL-YOSHIDA/ | 3232235880 | 192.168.001.104 |
| http://SV-FILE/ | 3232235779 | 192.168.001.003 |
| http://SV-MAIL/ | 3232235794 | 192.168.001.018 |
| http://SV-WEB/ | 3232235781 | 192.168.001.005 |