declare @t table(ip varchar(20))
insert @t
select '192.168.1.23' union all
select '192.168.1.213' union all
select '192.168.221.23' union all
select '192.168.1.255' union all
select '192.8.1.23' union all
select '62.8.1.3' SELECT
replicate('0',3-len(parsename(ip,4))) + parsename(ip,4) + '.' +
replicate('0',3-len(parsename(ip,3))) + parsename(ip,3) + '.' +
replicate('0',3-len(parsename(ip,2))) + parsename(ip,2) + '.' +
replicate('0',3-len(parsename(ip,1))) + parsename(ip,1)
FROM @t
insert @t
select '192.168.1.23' union all
select '192.168.1.213' union all
select '192.168.221.23' union all
select '192.168.1.255' union all
select '192.8.1.23' union all
select '62.8.1.3' SELECT
replicate('0',3-len(parsename(ip,4))) + parsename(ip,4) + '.' +
replicate('0',3-len(parsename(ip,3))) + parsename(ip,3) + '.' +
replicate('0',3-len(parsename(ip,2))) + parsename(ip,2) + '.' +
replicate('0',3-len(parsename(ip,1))) + parsename(ip,1)
FROM @t
-----------------------------------------------------------------------------------
declare @StarTIP varchar(20)
declare @EndIP varchar(20)
set @StartIP = '192.168.1.9'
set @EndIP = '192.168.1.234'select * from @t where
cast(replace(ip,'.','') as bigint) between cast(replace(@StartIP,'.','') as bigint) and cast(replace(@EndIP,'.','') as bigint)
-------------------------------------------
create function FormatIP(@ip varchar(15))
returns varchar(15)
as
begin
--declare @fip varchar(15)
return right('00' + parsename(@ip,4),3) + '.' +
right('00' + parsename(@ip,3),3) + '.' +
right('00' + parsename(@ip,2),3) + '.' +
right('00' + parsename(@ip,1),3)
endselect dbo.FormatIp('192.168.0.1')