文字列IPを10進IPに変換
ドット区切り（0.0.0.0）のIPアドレスを10進数のIPアドレスに変換する方法です。
SELECT NAME,IP,
CASE ISNUMERIC(REPLACE(IP,'.','')) WHEN 1 THENRIGHT('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 CONVIPFROM PC
| NAME | IP | CONVIP |
|---|---|---|
| CL-BAD | ABC.DEF.GHI.JKL | NULL |
| CL-NONE | 192.168 | NULL |
| CL-SAITO | 192.168.1.102 | 3232235878 |
| CL-SHARE | 192.168.1.105 | 3232235881 |
| CL-UEDA | NULL | NULL |
| CL-YOSHIDA | 192.168.1.104 | 3232235880 |
| SV-FILE | 192.168.1.3 | 3232235779 |
| SV-MAIL | 192.168.1.18 | 3232235794 |
| SV-WEB | 192.168.1.05 | 3232235781 |