MSSQL ip转化查询

--测试数据
create table #IPs(
strIP varchar(15) Null,
binIP binary(4) null
)

insert into #IPs values('0.0.0.0',NULL)
insert into #IPs values('255.255.255.255',NULL)
insert into #IPs values('127.0.0.1',NULL)
insert into #IPs values('192.168.43.192',NULL)
insert into #IPs values('192.168.1.101',NULL)
insert into #IPs values('65.54.239.80',NULL)
insert into #IPs values(NULL,0xB92AEAD3)
insert into #IPs values(NULL,0x2D4B2E53)
insert into #IPs values(NULL,0x31031B0B)
insert into #IPs values(NULL,0x7C2D5F2F)
insert into #IPs values(NULL,0x473E5D31)
insert into #IPs values(NULL,0x90D7D66B)
SELECT strIP,binIP,
cast(cast(substring(binIP,1,1)as int) as varchar(3)) + '.' +
cast(cast(substring(binIP,2,1)as int) as varchar(3)) + '.' +
cast(cast(substring(binIP,3,1)as int) as varchar(3)) + '.' +
cast(cast(substring(binIP,4,1)as int) as varchar(3)),
cast(cast(parsename(strIP,4)as int)as binary(1))+
cast(cast(parsename(strIP,3)as int)as binary(1))+
cast(cast(parsename(strIP,2)as int)as binary(1))+
cast(cast(parsename(strIP,1)as int)as binary(1)),
cast(PARSENAME(strIP,1) as bigint)+
cast(parsename(strIP,2)as bigint)*256 +
cast(parsename(strIP,3)as bigint)*65536 +
cast(parsename(strIP,4)as bigint)*16777216
---int类型也可以，但浪费空间且不直观
from #IPs
drop table #IPs
go