Because I really want to keep up the rhythm of posting at least one code slice a week, here’s a snippet I wrote a long time ago. In our business, we often want to compare ranges of IP addresses, and so we need to convert them from an quad-based notation (x.x.x.x) to an integer basis. This particular method uses the system function in SQL Server called PARSENAME(), which simply splits a period-delimited varchar into it’s seperate components.
Below is how you flip from quad to integer and back; enjoy!
DECLARE @SrcIP varchar(15),
@SrcIPNbr bigint
SET @SrcIP = ‘190.10.10.1’
SET @SrcIPNbr = (CONVERT(bigint, PARSENAME(@SrcIP,4))*POWER(256,3))
+ (CONVERT(bigint, PARSENAME(@SrcIP,3))*POWER(256,2))
+ (CONVERT(bigint, PARSENAME(@SrcIP,2)*POWER(256,1)))
+ CONVERT(bigint, PARSENAME(@SrcIP,1))
SELECT @SrcIPNbr
SELECT CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,3))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,2))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,1))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,0))%256)
Hi Stuart,
I think this is shorter and more efficient:
CONVERT(varchar(3),(@SrcIPNbr&0xFF000000)/0xFF0000)+’.’+
CONVERT(varchar(3),(@SrcIPNbr&0xFF0000)/0xFF00)+’.’+
CONVERT(varchar(3),(@SrcIPNbr&0xFF00)/0xFF)+’.’+
CONVERT(varchar(3),(@SrcIPNbr&0xFF))
Hi Zar,
It’s definitely shorter; I’m not accustomed to thinking in hex, though 🙂
Thanks for the suggestion.
SET @SrcIPNbr = (CONVERT(bigint, isnull(PARSENAME(@SrcIP,4), 0))*POWER(256,3))
+ (CONVERT(bigint, isnull(PARSENAME(@SrcIP,3), 0))*POWER(256,2))
+ (CONVERT(bigint, isnull(PARSENAME(@SrcIP,2), 0)*POWER(256,1)))
+ CONVERT(bigint, isnull(PARSENAME(@SrcIP,1), 0))
Juat one bit change added with ISNULL fn.
CREATE FUNCTION [dbo].[ConvertIPtoInt32]
(
@IP nvarchar(15)
)
RETURNS BIGINT
AS
BEGIN
RETURN (
CONVERT(bigint, isnull(PARSENAME(@IP,4), 0)) * POWER(256,3)
) + (
CONVERT(bigint, isnull(PARSENAME(@IP,3), 0)) * POWER(256,2)
) + (
CONVERT(bigint, isnull(PARSENAME(@IP,2), 0)) * POWER(256,1)
) + (
CONVERT(bigint, isnull(PARSENAME(@IP,1), 0))
)
END