April 10, 2013 at 10:56 am
Hello,
I am trying to convert an IP address from regular format (24.55.187.203) to binary 32-bit format (00011000001101111011101111001011).
Can someone please help me with SQL script? I have over 35 million ip addresses that I need to convert?
Thanks
April 10, 2013 at 11:36 am
I hate it because I can't figure out how to setbase it into a single query but this will work for you.
declare @ip varchar(200)
declare @address varchar(200)
set @address = '24.55.187.203'
set @ip = parsename(@address,1) --24.55.187.203
DECLARE @BinaryVARCHAR(200)
SET @Binary = ''
WHILE @IP <> 0
BEGIN
SET @Binary = SUBSTRING('0123456789', (@IP % 2) + 1, 1) + @Binary
SET @IP = @IP / 2
END
SET @IP = parsename(@address,2)
WHILE @IP <> 0
BEGIN
SET @Binary = SUBSTRING('0123456789', (@IP % 2) + 1, 1) + @Binary
SET @IP = @IP / 2
END
SET @IP = parsename(@address,3)
WHILE @IP <> 0
BEGIN
SET @Binary = SUBSTRING('0123456789', (@IP % 2) + 1, 1) + @Binary
SET @IP = @IP / 2
END
SET @IP = parsename(@address,4)
WHILE @IP <> 0
BEGIN
SET @Binary = SUBSTRING('0123456789', (@IP % 2) + 1, 1) + @Binary
SET @IP = @IP / 2
END
SELECT right('000000000000000000000000000000' + cast(@Binary as varchar),30)
April 10, 2013 at 12:39 pm
saved in my snippets:
declare @ipAddress varchar(20)
SET @ipAddress='010.000.123.094'
SELECT
CONVERT(varchar(3),convert(int,parsename(@ipAddress,4))) + '.' +
CONVERT(varchar(3),convert(int,parsename(@ipAddress,3))) + '.' +
CONVERT(varchar(3),convert(int,parsename(@ipAddress,2))) + '.' +
CONVERT(varchar(3),convert(int,parsename(@ipAddress,1)))
SELECT
convert(int,parsename(@ipAddress,4)) * 256 * 256 * 256 +
convert(int,parsename(@ipAddress,3)) * 256 * 256 +
convert(int,parsename(@ipAddress,2)) * 256 +
convert(int,parsename(@ipAddress,1))
declare @ipInt int
SET @ipInt =167803742
SELECT
CONVERT(varchar(4), (@ipInt & 0x00000000FF000000) / 256 / 256 / 256) + '.' +
CONVERT(varchar(4), (@ipInt & 0x0000000000FF0000) / 256 / 256) + '.' +
CONVERT(varchar(4), (@ipInt & 0x000000000000FF00) / 256) + '.' +
CONVERT(varchar(4), (@ipInt & 0x00000000000000FF))
Lowell
April 10, 2013 at 12:49 pm
Lowell?
Was there a piece missing where you convert it to a 32-bit format binary? ie. 001001001110010110001100...etc?
I stored your snippet for when I want to convert from ip to int and back again (thanks) but I'm not sure how that helps the OP with their question?
April 10, 2013 at 12:59 pm
whoops sorry;
here's an example of converting that int value into a binary map;
while i have an example of converting an into to varchar as a binary mapping, my example of converting it back doesn't seem to work as expected.
DECLARE @IntVal int;
SET @IntVal = 167803742;
WITH CTE (IntVal, BinVal, FinalBin) AS
(SELECT @IntVal IntVal, @IntVal % 2 BinVal, CONVERT(varchar(MAX),@IntVal % 2) FinalBin
UNION ALL
SELECT IntVal / 2, (IntVal / 2) % 2, CONVERT(varchar(MAX),(IntVal / 2) % 2) + FinalBin FinalBin
FROM CTE
WHERE IntVal / 2 > 0)
SELECT right('0000000000000000000000000000000' + FinalBin ,31)
FROM CTE
WHERE IntVal =
(SELECT MIN(IntVal)
FROM CTE);
Lowell
April 10, 2013 at 1:04 pm
here's what i have saved that looks like a stepping point to getting the string converted; it does not produce the final result, but something not quite finished:
--change it back:
;with myStrings (val)
AS (
SELECT '0001010000000000111101101011110' UNION ALL
SELECT '10101' UNION ALL
SELECT '1010100' UNION ALL
SELECT '010111111' )
,
FormattedStrings AS (
SELECT right('0000000000000000000000000000000' + val ,31) As sval
FROM myStrings),
MiniTally AS (
SELECT TOP 31 row_number() OVER (order by name) As N from sys.columns order by name )
select
sval,
MiniTally.N,
CONVERT(int,SUBSTRING(REVERSE(sval),MiniTally.N,1)) As bval,
POWER(2,N-1) As thePower,
CASE
WHEN (CONVERT(int,SUBSTRING(REVERSE(sval),MiniTally.N,1)) & 1) = 0
THEN 'False'
ELSE 'True'
END As [True?]
FROM FormattedStrings
CROSS JOIN MiniTally
order by sval,MiniTally.N
Lowell
April 10, 2013 at 1:47 pm
This is a case where CLR has marked improvements over the TSQL methodologies. I wrote a IPtoBigint conversion udf in C# and it was 10 times faster than the TSQL implementation. Makes a big difference when you are dealing with millions of IP addresses. Its also more efficient in terms of storage and indexing.
The probability of survival is inversely proportional to the angle of arrival.
April 10, 2013 at 2:07 pm
Lowell:
Thanks! I never thought of using a recursive CTE but that seems like the ticket to me if we stick with a straight SQL solution.
sturner:
I have to agree that when dealing with large amounts of string manipulation a CLR often outperforms TSQL...the only drawback being that some, like me :-D, have no idea how to write in languages OTHER than TSQL (unless you count COBOL and the old BASIC..lol). It's an area in which I'm lacking and someday hope to rectify that.
April 10, 2013 at 2:12 pm
Erin Ramsay (4/10/2013)
Lowell:Thanks! I never thought of using a recursive CTE but that seems like the ticket to me if we stick with a straight SQL solution.
sturner:
I have to agree that when dealing with large amounts of string manipulation a CLR often outperforms TSQL...the only drawback being that some, like me :-D, have no idea how to write in languages OTHER than TSQL (unless you count COBOL and the old BASIC..lol). It's an area in which I'm lacking and someday hope to rectify that.
Nothing wrong with knowing COBOL, it paid my bills for 11 years.
April 10, 2013 at 3:30 pm
I don't think it's really that complex.
I suggest a single lookup/"decode" string, with eight bytes per ip code; I'd also use a leading 7 "filler" 🙂 bytes, just to make the required offset adjustment during the substring clearer (to me, anyway). So, something like this:
'_______000000000000000100000010...11111111' --total of 7 + 8*256 bytes long
Specifically, in SQL terms:
--generalized code
SELECT
ip_address,
SUBSTRING(@decode_string, PARSENAME(ip_address, 4) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 3) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 2) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 1) * 8 + 8, 8)
FROM ...
WHERE ...
--specific LAZY example: I only put ips 0-3 in the string, but it's enough to get the gist :-D
declare @decode_string varchar(2055)
set @decode_string = '_______00000000000000010000001000000011'
SELECT
ip_address,
PARSENAME(ip_address, 4),PARSENAME(ip_address, 3),PARSENAME(ip_address, 2),PARSENAME(ip_address, 1),
SUBSTRING(@decode_string, PARSENAME(ip_address, 4) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 3) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 2) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 1) * 8 + 8, 8) AS decoded_values
FROM (
SELECT '0.1.2.3' AS ip_address UNION ALL
SELECT '3.2.1.0'
) AS derived
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 10, 2013 at 5:34 pm
I'm now correcting my original post to give an alternative solution that runs 6 times slower (aprox) but I'm including the test I did to confirm it.
--CREATE TABLE BinTest(
--ip_addressvarchar(15))
--INSERT INTO BinTest
--SELECT CAST( ABS(CHECKSUM(NEWID())) % 255 AS varchar(3)) + '.' +
--CAST( ABS(CHECKSUM(NEWID())) % 255 AS varchar(3)) + '.' +
--CAST( ABS(CHECKSUM(NEWID())) % 255 AS varchar(3)) + '.' +
--CAST( ABS(CHECKSUM(NEWID())) % 255 AS varchar(3))
--FROM dbo.Tally
--WHERE N < 1000001;
SET STATISTICS TIME ON;
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
PRINT 'Statement 1';
WITH CTE AS(
SELECT PARSENAME( ip_address, 4) part1,
PARSENAME( ip_address, 3) part2,
PARSENAME( ip_address, 2) part3,
PARSENAME( ip_address, 1) part4
FROM BinTest)
SELECT CAST( (part1 & 128) / 128 AS char(1)) + CAST( (part1 & 64) / 64 AS char(1)) +
CAST( (part1 & 32) / 32 AS char(1)) + CAST( (part1 & 16) / 16 AS char(1)) +
CAST( (part1 & 8) / 8 AS char(1)) + CAST( (part1 & 4) / 4 AS char(1)) +
CAST( (part1 & 2) / 2 AS char(1)) + CAST( (part1 & 1) / 1 AS char(1)) +
CAST( (part2 & 128) / 128 AS char(1)) + CAST( (part2 & 64) / 64 AS char(1)) +
CAST( (part2 & 32) / 32 AS char(1)) + CAST( (part2 & 16) / 16 AS char(1)) +
CAST( (part2 & 8) / 8 AS char(1)) + CAST( (part2 & 4) / 4 AS char(1)) +
CAST( (part2 & 2) / 2 AS char(1)) + CAST( (part2 & 1) / 1 AS char(1)) +
CAST( (part3 & 128) / 128 AS char(1)) + CAST( (part3 & 64) / 64 AS char(1)) +
CAST( (part3 & 32) / 32 AS char(1)) + CAST( (part3 & 16) / 16 AS char(1)) +
CAST( (part3 & 8) / 8 AS char(1)) + CAST( (part3 & 4) / 4 AS char(1)) +
CAST( (part3 & 2) / 2 AS char(1)) + CAST( (part3 & 1) / 1 AS char(1)) +
CAST( (part4 & 128) / 128 AS char(1)) + CAST( (part4 & 64) / 64 AS char(1)) +
CAST( (part4 & 32) / 32 AS char(1)) + CAST( (part4 & 16) / 16 AS char(1)) +
CAST( (part4 & 8) / 8 AS char(1)) + CAST( (part4 & 4) / 4 AS char(1)) +
CAST( (part4 & 2) / 2 AS char(1)) + CAST( (part4 & 1) / 1 AS char(1)) AS ip_address_bin
INTO #Test1
FROM CTE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
declare @decode_string varchar(2055)
set @decode_string = '_______'
SELECT @decode_string = @decode_string +
CAST( (N & 128) / 128 AS char(1)) + CAST( (N & 64) / 64 AS char(1)) +
CAST( (N & 32) / 32 AS char(1)) + CAST( (N & 16) / 16 AS char(1)) +
CAST( (N & 8) / 8 AS char(1)) + CAST( (N & 4) / 4 AS char(1)) +
CAST( (N & 2) / 2 AS char(1)) + CAST( (N & 1) / 1 AS char(1))
FROM (SELECT N = N-1 FROM dbo.Tally) Tally0
WHERE N < 256
ORDER BY N
PRINT 'Statement 2';
SELECT
SUBSTRING(@decode_string, PARSENAME(ip_address, 4) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 3) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 2) * 8 + 8, 8) +
SUBSTRING(@decode_string, PARSENAME(ip_address, 1) * 8 + 8, 8) AS decoded_values
INTO #Test2
FROM BinTest
SET STATISTICS TIME OFF
DROP TABLE #Test1
DROP TABLE #Test2
Results:
My (ugly) option
CPU time = 24243 ms, elapsed time = 24580 ms.
Scott's option
CPU time = 187 ms, elapsed time = 437 ms. --decode string creation
CPU time = 3900 ms, elapsed time = 3921 ms. --actual query
April 11, 2013 at 9:02 am
The @decode_string needs a slight adjustment, moving the decode value for "255" to the end of the string.
As there's really no need to recompute the string every time, let's just code it as a literal; and since char rather than varchar might give the slightest performance benefit, let's do that too:
DECLARE @decode_string char(2055)
SET @decode_string = '...'
Edit: Removed @decode_string value, as it did not look correct when I examined it more closely.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 11, 2013 at 9:20 am
Here's T-SQL code to generate the full @decode_string:
DECLARE @decode_string char(2055)
;WITH
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [10s].digit * 10 + [1s].digit AS tally
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
),
decodeBytes AS (
SELECT 00 AS byte_value, '0000' AS decode_value UNION ALL
SELECT 01 AS byte_value, '0001' UNION ALL
SELECT 02 AS byte_value, '0010' UNION ALL
SELECT 03 AS byte_value, '0011' UNION ALL
SELECT 04 AS byte_value, '0100' UNION ALL
SELECT 05 AS byte_value, '0101' UNION ALL
SELECT 06 AS byte_value, '0110' UNION ALL
SELECT 07 AS byte_value, '0111' UNION ALL
SELECT 08 AS byte_value, '1000' UNION ALL
SELECT 09 AS byte_value, '1001' UNION ALL
SELECT 10 AS byte_value, '1010' UNION ALL
SELECT 11 AS byte_value, '1011' UNION ALL
SELECT 12 AS byte_value, '1100' UNION ALL
SELECT 13 AS byte_value, '1101' UNION ALL
SELECT 14 AS byte_value, '1110' UNION ALL
SELECT 15 AS byte_value, '1111'
)
SELECT @decode_string = REPLICATE('_', 7) + (
SELECT
decode1.decode_value + decode2.decode_value
FROM cteTally byte1
CROSS JOIN cteTally byte2
INNER JOIN decodeBytes decode1 ON
decode1.byte_value = byte1.tally
INNER JOIN decodeBytes decode2 ON
decode2.byte_value = byte2.tally
WHERE
byte1.tally BETWEEN 0 AND 15 AND
byte2.tally BETWEEN 0 AND 15
ORDER BY
1
FOR XML PATH('')
)
SELECT @decode_string, LEN(@decode_string)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply