几个在MS SQL Server处理IP的函数/存储过程

几个在MS SQL Server处理IP的函数/存储过程

以下函数都未验证IP字符串的合法性，需要的话可以通过VBScript的接口在Sql Server中实现。当然，最好的办法是把这些程序写到网页脚本中去（比如ASP、PHP等），如果还想节省服务器资源的话，应该用javascript或其他客户端脚本里边去。

下面这些东西仅仅是好玩而已，如果想提高效率，还可以把下面除法换成位运算来执行。

1、 IP转长整数

/*使用方法

select dbo.Int2IP(84215046)
select dbo.IP2Int('250.250.250.250')

返回0,则非法
*/

CREATE FUNCTION IP2Int(@IP varchar(15))
   RETURNS bigint
AS
BEGIN
   DECLARE @Num bigint;
   DECLARE @checker bigint;
   DECLARE @IP_temp varchar(16);
   SET @Num = 0;
   SET @IP_temp = @IP+'.';
   WHILE charindex('.',@IP_temp) > 0
   BEGIN
      SET @Num = 256 * @Num;
      SET @checker = CAST (LEFT(@IP_temp,CHARINDEX('.',@IP_temp)-1) AS bigint);
      IF @checker >255 RETURN 0;
      SET @Num = @Num + @checker;
      SET @IP_temp = RIGHT(@IP_temp,LEN(@IP_temp)-CHARINDEX('.',@IP_temp));
   END
   RETURN @Num;
END

2、 长整数转IP

/*使用方法

select dbo.Int2IP(84215046)
select dbo.IP2Int('250.250.250.250')

*/
CREATE FUNCTION Int2IP(@IPNum bigint)
   RETURNS varchar(15)
AS
BEGIN

   DECLARE @IPPartA varchar(3) ;
   DECLARE @IPPartB varchar(3) ;
   DECLARE @IPPartC varchar(3) ;
   DECLARE @IPPartD varchar(3) ;
   DECLARE @Num bigint;
   SET @Num = @IPNum;

   SET @IPPartD = cast ((@Num % 256) AS varchar(3));
   SET @Num = @Num / 256;
   SET @IPPartC = cast ((@Num % 256) AS varchar(3));
   SET @Num = @Num / 256;
   SET @IPPartB = cast ((@Num % 256) AS varchar(3));
   SET @Num = @Num / 256;
   SET @IPPartA = cast (@Num AS varchar(3));

   RETURN @IPPartA+'.'+@IPPartB+'.'+@IPPartC+'.'+@IPPartD;
END

 

3、   IP数字分割

/*使用方法

DECLARE      @IPPartA tinyint ;
DECLARE      @IPPartB tinyint ;
DECLARE      @IPPartC tinyint ;
DECLARE      @IPPartD tinyint ;
EXEC dbo.splitIP 4210752250, @IPPartA OUTPUT, @IPPartB OUTPUT, @IPPartC OUTPUT, @IPPartD OUTPUT
PRINT @IPPartA
PRINT @IPPartB
PRINT @IPPartC
PRINT @IPPartD

*/
CREATE PROC splitIP @IPNUM bigint,
            @IPPartA tinyint OUTPUT,
            @IPPartB tinyint OUTPUT,
            @IPPartC tinyint OUTPUT,
            @IPPartD tinyint OUTPUT
AS
BEGIN
   IF (@IPNum>0 AND @IPNum<4294967296)
   BEGIN
      DECLARE @Num bigint;
      SET @Num = @IPNum;
     
      SET @IPPartD = cast ((@Num % 256) AS tinyint);
      SET @Num = @Num / 256;
      SET @IPPartC = cast ((@Num % 256) AS tinyint);
      SET @Num = @Num / 256;
      SET @IPPartB = cast ((@Num % 256) AS tinyint);
      SET @Num = @Num / 256;
      SET @IPPartA = cast (@Num AS tinyint);
   END
   ELSE
   BEGIN
      SET @IPPartA = 0;
      SET @IPPartB = 0;
      SET @IPPartC = 0;
      SET @IPPartD = 0;
   END
END
GO