SQL SERVER ip转换的函数

最新推荐文章于 2020-09-18 14:02:20 发布

weixin_33777877

最新推荐文章于 2020-09-18 14:02:20 发布

阅读量317

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/sansom/blog/178249

版权

2019独角兽企业重金招聘Python工程师标准>>>

USE scaner  
GO  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
  
  
-- =============================================   
-- Author:      sansom   
-- Create date: 2013-11-11   
-- Description: 转换ip   
-- =============================================   
CREATE FUNCTION [dbo].[UF_CovertIPToInt]   
(  
    @ip varchar(20)  
)  
RETURNS bigint  
AS  
BEGIN  
    declare @pos int  
    declare @sub1 varchar(10)  
    declare @sub2 varchar(10)  
    declare @sub3 varchar(10)  
    declare @sub4 varchar(10)  
  
    set  @pos = charindex('.',@ip)  
  
    if(@pos<1) return 0  
  
    set @sub1 = substring(@ip,0,@pos)  
  
    --select @sub1   
  
    set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)  
  
    --select @ip   
  
    set  @pos = charindex('.',@ip)  
  
    if(@pos<1) return 0  
  
    set @sub2 = substring(@ip,0,@pos)  
  
    --select @sub2   
  
    set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)  
  
    --select @ip   
  
    set  @pos = charindex('.',@ip)  
  
    if(@pos<1) return 0  
  
    set @sub3 = substring(@ip,0,@pos)  
  
    --select @sub3   
  
    set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)  
  
    if(len(@ip)<1) return 0  
  
    select @sub4 = @ip  
  
    --select @sub4   
      
    return cast(@sub1 as bigint ) * 16777216 + cast(@sub2 as bigint ) * 65536 + cast(@sub3 as bigint )*256 + cast(@sub4 as bigint )  
  
END  


select dbo.UF_CovertIPToInt('192.168.1.100') as ip  



SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
  
  
CREATE FUNCTION [dbo].[UF_ConvertIPToStr]   
(  
    @ip bigint  
)  
RETURNS varchar(20)  
AS  
BEGIN  
    RETURN  cast((@ip/16777216)% 256 as varchar(4)) + '.' + cast( (@ip/65536)% 256 as varchar(4)) +'.'+ cast( (@ip/256)%256 as varchar(4))+ '.'+ cast( (@ip%256)as varchar(4))  
  
END  


select dbo.UF_ConvertIPToStr (3232235790) as ip

转载于:https://my.oschina.net/sansom/blog/178249