关于匹配ip的问题
表a如下
user         ip
  u1     100.243.1.1
  u2     20.43.11.2-20.43.11.10
  u3     100.43.1.12-100.43.1.50
。。。

现得到一个ip，如何确认该ip属于哪个用户？

------解决方案--------------------
CREATE FUNCTION dbo.f_IP2Int(
@ip char(15)
)RETURNS bigint
AS
BEGIN
DECLARE @re bigint
SET @re=0
SELECT @re=@re+LEFT(@ip,CHARINDEX( '. ',@ip+ '. ')-1)*ID
,@ip=STUFF(@ip,1,CHARINDEX( '. ',@ip+ '. '), ' ')
FROM(
SELECT ID=CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1)a
RETURN(@re)
END
GO

create table t([user] varchar(10),ip varchar(50))
insert t select 'u1 ', '100.243.1.1 '
union all select 'u2 ', '20.43.11.2-20.43.11.10 '
union all select 'u3 ', '100.43.1.12-100.43.1.50 '


create table tmp([user] varchar(10),ip varchar(50))
insert tmp select [user],ip=case when charindex( '- ',ip)=0 then ip+ '- '+ip
else ip end from t

declare @ip varchar(20)
set @ip= '20.43.11.4 '
select [user] from tmp
where dbo.f_IP2Int(@ip)> =dbo.f_IP2Int(substring(ip,0,charindex( '- ',ip)))
and dbo.f_IP2Int(@ip) <=dbo.f_IP2Int(substring(ip,charindex( '- ',ip)+1,20))

drop table t
drop table tmp
drop function f_IP2Int

user
----------
u2
（所影响的行数为 1 行）

------解决方案--------------------
借助邹老大的函数dbo.f_IP2Int，

declare @a table(user varchar(50),ip varchar(1000))
insert @a
select 'u1 ', '100.243.1.1 '
union all select 'u2 ', '20.43.11.2-20.43.11.10 '
union all select 'u3 ', '100.43.1.12-100.43.1.50 '

declare @ip varchar(1000)
set @ip= '100.243.1.1 '

select user from
(
select user,sip=dbo.f_IP2Int(
case replace(left(ip,charindex( '- ',ip)), '- ', ' ')
when ' ' then ip
else replace(left(ip,charindex( '- ',ip)), '- ', ' ')
end),
eip=dbo.f_IP2Int(stuff(ip,1,charindex( '- ',ip), ' ')) from @a
)a
where dbo.f_IP2Int(@ip) between sip and eip