SQL查询IP段
declare @ip as varchar(15)
set @ip = '192.168.0.0'
select * from tb where
right('00'+parsename(@ip,4),3) + '.' + right('00'+parsename(@ip,3),3) + '.' + right('00'+parsename(@ip,2),3) + '.' + right('00'+parsename(@ip,1),3)
between
right('00'+parsename(startip,4),3) + '.' + right('00'+parsename(startip,3),3) + '.' + right('00'+parsename(startip,2),3) + '.' + right('00'+parsename(startip,1),3)
and
right('00'+parsename(endip,4),3) + '.' + right('00'+parsename(endip,3),3) + '.' + right('00'+parsename(endip,2),3) + '.' + right('00'+parsename(endip,1),3)
=================IP地址与十进制的互换==========================
--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.f_IPtoInt(@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
select dbo.f_IPtoInt('192.168.20.179') ip_int
/*
ip_int
--------------------
3232240819
(所影响的行数为 1 行)
*/
drop function dbo.f_IPtoInt
--2. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.f_InttoIP(@IP bigint)RETURNS varchar(15)
AS
BEGIN
DECLARE @re varchar(15)
SET @re = ''
SELECT @re = @re + '.' + CAST(@IP/ID as varchar) , @IP = @IP % ID
FROM
(
SELECT ID = CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1
) a
RETURN(STUFF(@re , 1 , 1 , ''))
END
GO
SELECT dbo.f_InttoIP(3232240819) ip_varchar
/*
ip_varchar
---------------
192.168.20.179
(所影响的行数为 1 行)
*/
DROP FUNCTION dbo.f_InttoIP