MSSQL 模仿 MYSQL 的IPV4與BIGINT類型互轉函數

/*********************************************/

/*********************************************/

CREATE FUNCTION [dbo].[INET_ATON](   

@ip NVARCHAR(15)   

)RETURNS BIGINT   

AS   

BEGIN   

DECLARE @iip BIGINT   

SET @iip = 0    

SELECT @[email protected]+LEFT(@ip, CHARINDEX('.',@ip+'.')-1 )*id, 

  @ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')  

FROM(   

SELECT id = CONVERT(BIGINT, 1*256*256*256)   

UNION ALL SELECT 1*256*256   

UNION ALL SELECT 1*256   

UNION ALL SELECT 1

) AS T

RETURN (@iip) 

END

---------------------------------------------------------------------------------------

/*********************************************/

/*********************************************/

CREATE FUNCTION [dbo].[INET_NTOA](

@iip BIGINT   

)

RETURNS NVARCHAR(15)   

As   

BEGIN   

DECLARE @ip NVARCHAR(15)   

SET @ip = ''   

SELECT 

@ip = @ip +'.'+ CONVERT(varchar(3), @iip/ID), @iip = @iip%ID

FROM(   

SELECT ID = CONVERT(BIGINT, 1*256*256*256)   

UNION ALL SELECT 1*256*256   

UNION ALL SELECT 1*256   

UNION ALL SELECT 1 

) AS T   

RETURN(STUFF(@ip,1,1,''))   

END

---------------------------------------------------------------------------------------

;WITH cte AS

(

SELECT dbo.INET_ATON('192.168.1.2') AS iip

)

SELECT dbo.INET_NTOA(iip) AS ip, iip FROM cte