I have the following stored procedure that converts IPs to 32-bit binary in TSQL. It works but is EXTREMELY slow (has only converted 8,400 in 40 minutes) - probably because it uses cursors. Does anyone have suggestions and/or a different approach to improve performance?
Here's an example: 1.1.79.129 is converted to 00000001.00000001.01001111.10000001
Thanks
CREATE PROCEDURE [dbo].[sp_UpdateTableConvertIPToBinary]
AS
SET NOCOUNT ON
declare @IP nvarchar(255)
declare IPList cursor for
/*
Get IP address from CIDR Block where Binary has not been assigned
*/
select left(IP,charindex('/',IP)-1) as Block from MyDB.dbo.MyTable
WHERE IP IS NOT NULL AND [Binary] IS NULL
ORDER BY Block
OPEN IPList
FETCH NEXT FROM IPList
INTO @IP
WHILE @@FETCH_STATUS = 0
BEGIN
begin
declare @Octet as varchar(15)
declare @Div as integer
declare @Output as varchar(100)
declare @n as integer
declare @OriginalIP varchar(15)
select @OriginalIP = @IP
select @Div = '128'
select @Output = ''
select @n = 0
WHILE @n < 4
begin
/*
Set @Octet = Class to the left of the first '.' in @IP
If no '.' in @IP, set @Octet = entire @IP (this will happen for the last Class)
*/
IF CHARINDEX('.',@IP) > 0
begin
select @Octet = left(@IP,charindex('.',@IP)-1)
end
else
begin
select @Octet = @IP
end
/*
If @Octet >= 128, append 1 to @Output and subtract 128 from @Octet
If not, append 0 to @Output
*/
if cast(@Octet as int) >= @Div
begin
select @Output = @Output + '1'
select @Octet = cast(@Octet as int) - @Div
end
else
begin
select @Output = @Output + '0'
end
/*
If @Octet >= 64, append 1 to @Output and subtract 64 from @Octet
If not, append 0 to @Output
*/
if cast(@Octet as int) >= (@Div/2)
begin
select @Output = @Output + '1'
select @Octet = cast(@Octet as int) - (@Div/2)
end
else
begin
select @Output = @Output + '0'
end
/*
If @Octet >= 32, append 1 to @Output and subtract 32 from @Octet
If not, append 0 to @Output
*/
if cast(@Octet as int) >= (@Div/4)
begin
select @Output = @Output + '1'
select @Octet = cast(@Octet as int) - (@Div/4)
end
else
begin
select @Output = @Output + '0'
end
/*
If @Octet >= 16, append 1 to @Output and subtract 16 from @Octet
If not, append 0 to @Output
*/
if cast(@Octet as int) >= (@Div/8)
begin
select @Output = @Output + '1'
select @Octet = cast(@Octet as int) - (@Div/8)
end
else
begin
select @Output = @Output + '0'
end
/*
If @Octet >= 8, append 1 to @Output and subtract 8 from @Octet
If not, append 0 to @Output
*/
if cast(@Octet as int) >= (@Div/16)
begin
select @Output = @Output + '1'
select @Octet = cast(@Octet as int) - (@Div/16)
end
else
begin
select @Output = @Output + '0'
end
/*
If @Octet >= 4, append 1 to @Output and subtract 4 from @Octet
If not, append 0 to @Output
*/
if cast(@Octet as int) >= (@Div/32)
begin
select @Output = @Output + '1'
select @Octet = cast(@Octet as int) - (@Div/32)
end
else
begin
select @Output = @Output + '0'
end
/*
If @Octet >= 2, append 1 to @Output and subtract 2 from @Octet
If not, append 0 to @Output
*/
if cast(@Octet as int) >= (@Div/64)
begin
select @Output = @Output + '1'
select @Octet = cast(@Octet as int) - (@Div/64)
end
else
begin
select @Output = @Output + '0'
end
/*
If @Octet >= 1, append 1 to @Output and subtract 1 from @Octet
If not, append 0 to @Output
*/
if cast(@Octet as int) >= (@Div/128)
begin
select @Output = @Output + '1'
select @Octet = cast(@Octet as int) - (@Div/128)
end
else
begin
select @Output = @Output + '0'
end
/*
if @n < 3, append . to @Output
*/
if @n < 3
begin
select @Output = @Output + '.'
end
/*
Remove the Octet just converted to Binary from @IP and increment the counter
*/
select @IP = right(@IP,len(@IP) - charindex('.',@IP))
select @n = @n+1
end
/*
Update table, set Binary = @Output
*/
UPDATE MyDB.dbo.MyTable Set Binary = @Output WHERE left(IP,charindex('/',IP)-1) = @OriginalIP
end
FETCH NEXT FROM IPList
INTO @IP
END
CLOSE IPList
DEALLOCATE IPList
Well it looks like IPV4, so I am going to go on that assumption. You are also converting to some really long binary text representation, I recommend that you just use BINARY(4) instead, and my answer will assume that. You can convert this your 131-character text representation if you want, but I don't know why you would want to.
I posted an answer to a similar general question some years ago here: https://stackoverflow.com/a/1385701/109122
That basically showed this function:
CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
DECLARE @bin AS BINARY(4)
SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
RETURN @bin
END
go
Today however, I would recommend making it an inline Table-Value Function instead for performance.
This is how you could do that:
CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE
AS RETURN (
SELECT CAST(
CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
AS BINARY(4)) As bin
)
go
And this is how you could use that to perform your conversion and update without a cursor:
;
WITH cte As
(
SELECT *
FROM MyDB.dbo.MyTable
OUTER APPLY dbo.itvfBinaryIPv4(left(IP,charindex('/',IP)-1))
)
UPDATE cte
Set Binary = bin
WHERE IP IS NOT NULL
AND [Binary] IS NULL
This should be very fast.