数字转IP地址函数.docxVIP

--数字转IP地址函数：IF?OBJECT_ID(dbo.fn_IP2Str)0????DROP?FUNCTION?dbo.fn_IP2StrGOCREATE?FUNCTION?[dbo].[fn_IP2Str]?(????@InIP?BIGINT)RETURNS?NVARCHAR(15)ASBEGIN????IF?@InIP?IS?NULL???????RETURN?0.0.0.0????DECLARE?@ip?BIGINT????SET?@ip?=?@InIP????SET?@ip?=?@ip?+?0x100000000????RETURN???????CAST(((@ip??0xff000000)?/?0x1000000)?AS?NVARCHAR(3))?+?.?+???????CAST(((@ip??0xff0000)?/?0x10000)?AS?NVARCHAR(3))?+?.?+???????CAST(((@ip??0xff00)?/?0x100)?AS?NVARCHAR(3))?+?.?+???????CAST((@ip??0xff)?AS?NVARCHAR(3))ENDGO?--调用示例：SELECT?dbo.fn_IP2Str(4294967295)?/*---------------255.255.255.255(1?行受影响)*/?SELECT?dbo.fn_IP2Str(0)/*---------------0.0.0.0(1?行受影响)*/?--附：以下转自邹建--ip地址与数字相互转换的sql函数IF?EXISTS?(???????SELECT?*???????FROM?dbo.sysobjects???????WHERE?id?=?OBJECT_ID(N[dbo].[f_IP2Int])?????????AND?xtype???IN?(NFN,?NIF,?NTF)???)????DROP?FUNCTION?[dbo].[f_IP2Int]??GO??/*--字符型IP?地址转换成数字IP????--邹建?2004.08(引用请保留此信息)--*/??/*--调用示例??select dbo.f_IP2Int(255.255.255.255)????select dbo.f_IP2Int(12.168.0.1)??--*/??CREATE?FUNCTION?f_IP2Int(????@ip?CHAR(15))RETURNS?BIGINTASBEGIN????DECLARE?@re?BIGINT??????SET?@re?=?0??????SELECT?@re?=?@re+LEFT(@ip,?CHARINDEX(.,?@ip+.)-1)*ID,?@ip?=?STUFF(@ip,?1,?CHARINDEX(.,?@ip+.),?)????FROM?(?????????????SELECT?ID?=?CASTAS?BIGINT)?????????????UNION?ALL???SELECT?65536?????????????UNION?ALL???SELECT?256?????????????UNION?ALL???SELECT?1?????????)A????RETURN(@re)ENDGO?IF?EXISTS?(???????SELECT?*???????FROM?dbo.sysobjects???????WHERE?id?=?OBJECT_ID(N[dbo].[f_Int2IP])?????????AND?xtype???IN?(NFN,?NIF,?NTF)???)????DROP?FUNCTION?[dbo].[f_Int2IP]??GO??/*--数字?IP???转换成格式化?IP???地址??--邹建?2004.08(引用请保留此信息)--*/??/*--调用示例??select dbo.f_Int2IP(4294967295)????select dbo.f_Int2IP(212336641)??--*/??CREATE?FUNCTION?f_Int2IP(????@IP?BIGINT)RETURNS?VARCHAR(15)ASBEGIN????DECLARE?@re?VARCHAR(16)??????SET?@re?=???????SELECT?@re?=?@re+.+CAST(@IP/ID?AS?VARCHAR),?@IP?=?@IP%ID????FROM?(?????????????SELECT?ID?=?CASTAS?BIGINT)?????????????UNION?ALL???SELECT?65536?????????????UNION?