SQL Server 2005 ip地址点分十进制与长整形表示法相互转换

SQL Server 2005 ip地址点分十进制与长整形表示法相互转换

在SQL Server 2005 数据库设计时，为了查询效率，常常把点分十进制表示的ip地址设计为bigint类型。存储的时候，怎么把点分十进制转换为bigint，请参考下面的sql自定义函数：

1. USE [temp]  
2. GO  
3. /****** 对象:  UserDefinedFunction [dbo].[UF_CovertIPToInt]    脚本日期: 08/06/2012 16:55:22 ******/  
4. SET ANSI_NULLS ON  
5. GO  
6. SET QUOTED_IDENTIFIER ON  
7. GO  
8.   
9.   
10. -- =============================================   
11. -- Author:      ----   
12. -- Create date: 2009-05-11   
13. -- Description: 转换ip   
14. -- =============================================   
15. CREATE FUNCTION [dbo].[UF_CovertIPToInt]   
16. (  
17.     @ip varchar(20)  
18. )  
19. RETURNS bigint  
20. AS  
21. BEGIN  
22.     declare @pos int  
23.     declare @sub1 varchar(10)  
24.     declare @sub2 varchar(10)  
25.     declare @sub3 varchar(10)  
26.     declare @sub4 varchar(10)  
27.   
28.     set  @pos = charindex('.',@ip)  
29.   
30.     if(@pos<1) return 0  
31.   
32.     set @sub1 = substring(@ip,0,@pos)  
33.   
34.     --select @sub1   
35.   
36.     set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)  
37.   
38.     --select @ip   
39.   
40.     set  @pos = charindex('.',@ip)  
41.   
42.     if(@pos<1) return 0  
43.   
44.     set @sub2 = substring(@ip,0,@pos)  
45.   
46.     --select @sub2   
47.   
48.     set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)  
49.   
50.     --select @ip   
51.   
52.     set  @pos = charindex('.',@ip)  
53.   
54.     if(@pos<1) return 0  
55.   
56.     set @sub3 = substring(@ip,0,@pos)  
57.   
58.     --select @sub3   
59.   
60.     set @ip = substring(@ip, @pos+1 ,len(@ip) - @pos + 1)  
61.   
62.     if(len(@ip)<1) return 0  
63.   
64.     select @sub4 = @ip  
65.   
66.     --select @sub4   
67.       
68.     return cast(@sub1 as bigint ) * 16777216 + cast(@sub2 as bigint ) * 65536 + cast(@sub3 as bigint )*256 + cast(@sub4 as bigint )  
69.   
70. END  

有了上面的函数，我可以方便地进行转换，例如：

1. select dbo.UF_CovertIPToInt('192.168.1.100') as ip  

显示的时候，为了方便阅读，常常需要把bigint转换为点分十进制，请参考下面的自定义函数：

1. USE [temp]  
2. GO  
3.   
4. SET ANSI_NULLS ON  
5. GO  
6. SET QUOTED_IDENTIFIER ON  
7. GO  
8.   
9.   
10. CREATE FUNCTION [dbo].[UF_ConvertIPToStr]   
11. (  
12.     @ip bigint  
13. )  
14. RETURNS varchar(20)  
15. AS  
16. BEGIN  
17.     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))  
18.   
19. END  

可以通过下面的方式调用该函数：

1. select dbo.UF_ConvertIPToStr (3232235790) as ip  

当然，对于mysql数据，就没必要这么麻烦了。完全可以使用内置的函数：inet_aton 和inet_ntoa

1. select inet_aton('192.168.1.12');  
2. select inet_ntoa(3232235788);