July 23, 2012 at 1:28 pm
I have two inputs “address” and “net” . I may have to convert these two inputs into binary form. And then apply logic “AND” function. That it, only in the case of “1 And 1”, we may get the “1”. The result I am expecting to see is to insert a column “net”. For address, we can convert to a “standard” comparable format by “refilling” those omitted characters.
Principle for address format:
-address use 32 hexadecimal numbers and organized into 8 quarters of 4 hex digits separated by a colon;
-it can omit the leading 0s in any given quarter;
-represent one or more consecutive quarters of all hex 0s with”::” but only for one such occurrence in a given address
[address] [Convert_to_format] [mask] [net]
[NK00:976a:c305:bef8:575c:646d::0:a/127] [NK00:976a:c305:bef8:575c:646d:0000:000a] [127] [NK00:976a:c305:bef8:575c:646d:0000:0000]
[2012:0DB8:AC10:FE01::/64] [2012:0DB8:AC10:FE01:0000:0000:0000:0000][64] [2012:0DB8:AC10:FE01:0000:0000:0000:0000]
[NK00:976a:c305:bef8:575c::0:56/16][NK00:976a:c305:bef8:575c:0000:0000:0056][16][NK00:0000:0000:0000:0000:0000:0000:0000]
July 23, 2012 at 1:35 pm
weston_086 (7/23/2012)
I have two inputs “address” and “net” . I may have to convert these two inputs into binary form. And then apply logic “AND” function. That it, only in the case of “1 And 1”, we may get the “1”. The result I am expecting to see is to insert a column “net”. For address, we can convert to a “standard” comparable format by “refilling” those omitted characters.Principle for address format:
-address use 32 hexadecimal numbers and organized into 8 quarters of 4 hex digits separated by a colon;
-it can omit the leading 0s in any given quarter;
-represent one or more consecutive quarters of all hex 0s with”::” but only for one such occurrence in a given address
[address] [Convert_to_format] [mask] [net]
[NK00:976a:c305:bef8:575c:646d::0:a/127] [NK00:976a:c305:bef8:575c:646d:0000:000a] [127] [NK00:976a:c305:bef8:575c:646d:0000:0000]
[2012:0DB8:AC10:FE01::/64] [2012:0DB8:AC10:FE01:0000:0000:0000:0000][64] [2012:0DB8:AC10:FE01:0000:0000:0000:0000]
[NK00:976a:c305:bef8:575c::0:56/16][NK00:976a:c305:bef8:575c:0000:0000:0056][16][NK00:0000:0000:0000:0000:0000:0000:0000]
HUH???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2012 at 1:47 pm
Sean Lange (7/23/2012)
weston_086 (7/23/2012)
I have two inputs “address” and “net” . I may have to convert these two inputs into binary form. And then apply logic “AND” function. That it, only in the case of “1 And 1”, we may get the “1”. The result I am expecting to see is to insert a column “net”. For address, we can convert to a “standard” comparable format by “refilling” those omitted characters.Principle for address format:
-address use 32 hexadecimal numbers and organized into 8 quarters of 4 hex digits separated by a colon;
-it can omit the leading 0s in any given quarter;
-represent one or more consecutive quarters of all hex 0s with”::” but only for one such occurrence in a given address
[address] [Convert_to_format] [mask] [net]
[NK00:976a:c305:bef8:575c:646d::0:a/127] [NK00:976a:c305:bef8:575c:646d:0000:000a] [127] [NK00:976a:c305:bef8:575c:646d:0000:0000]
[2012:0DB8:AC10:FE01::/64] [2012:0DB8:AC10:FE01:0000:0000:0000:0000][64] [2012:0DB8:AC10:FE01:0000:0000:0000:0000]
[NK00:976a:c305:bef8:575c::0:56/16][NK00:976a:c305:bef8:575c:0000:0000:0056][16][NK00:0000:0000:0000:0000:0000:0000:0000]
HUH???
looks like he is trying to format ipv6 addresses maby??
can you please post sample data, DDL, and expected results in a nice consumable format. please see the first link in my signature if you need help generating the CREATE TABLE scripts or the sample data and results.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 23, 2012 at 1:49 pm
Let me make sure I get the sequence here:
1. Convert Address to HEX (using what? ASCII values? some hashing algorithm?)
2. Convert Net to HEX (same question)
3. Compare HEX of Address to HEX of Net, using bitmap AND function
4. Use results of comparison to generate value for Net
Questions:
1. Is "Address" a mailing address, a RAM address, something else?
2. What is the original data in Net coming from? It seems, per the description, to be its own source, which is kind of circular.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2012 at 2:08 pm
Adress is IPadress and net is output after combining.
July 23, 2012 at 2:14 pm
If you want some help you have to help us. Please provide enough information to give someone a chance at answering. We don't have ddl, sample data, desired output or a clear explanation of what you want to do. This is probably pretty simple but without some effort up front on your part the best you are going to get is a shot in the dark.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2012 at 2:35 pm
weston_086 (7/23/2012)
Adress is IPadress and net is output after combining.
Combining what? Two IPV6 addresses?
First, are they already in HEX format, or are they strings? That determines a lot of what you need to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2012 at 2:44 pm
create table test (adress varchar(100),[Convert_to_format] nvarchar(100),mask varchar(100),Net nvarchar(100))
insert into Test values ('NK00:976a:c305:bef8:575c:646d::0:a/127' ,'',127,'')
,('2012:0DB8:AC10:FE01::/64','',64,''),
('NK00:976a:c305:bef8:575c::0:56/16','',16,'')
and this is result i want.
insert into Test values ('NK00:976a:c305:bef8:575c:646d::0:a/127','NK00:976a:c305:bef8:575c:646d:0000:000a',127,'NK00:976a:c305:bef8:575c:646d:0000:0000'),
('2012:0DB8:AC10:FE01::/64','2012:0DB8:AC10:FE01:0000:0000:0000:0000',64,'2012:0DB8:AC10:FE01:0000:0000:0000:0000'),
('NK00:976a:c305:bef8:575c::0:56/16','NK00:976a:c305:bef8:575c:0000:0000:0056',16,'NK00:0000:0000:0000:0000:0000:0000:0000')
July 23, 2012 at 2:51 pm
If they're strings, something like this is what's needed:
WITH Parser
AS (SELECT *
FROM ( VALUES ( 1, 'fe80::8d2b:d37:178e:f019'), ( 2, '2001:db8:85a3:0:0:8a2e:370:7334') ) AS V (ID, IP)
CROSS APPLY (SELECT
RIGHT('0000'
+ SUBSTRING(IP,
Number + 1,
ISNULL(NULLIF(CHARINDEX(':',
IP, Number + 1)
- 1, -1),
LEN(IP))
- Number), 4) AS Seg,
ROW_NUMBER() OVER (ORDER BY Number) AS Seq
FROM
dbo.Numbers
WHERE
SUBSTRING(IP,
Number, 1) = ':'
OR Number = 0)
AS Parser),
StrToHexToInt
AS (SELECT *
FROM Parser
CROSS APPLY (SELECT SUM(D) AS I
FROM dbo.Numbers
CROSS APPLY (SELECT *
FROM ( VALUES ( 0, '0'), ( 1, '1'), ( 2, '2'), ( 3, '3'),
( 4, '4'), ( 5, '5'), ( 6, '6'), ( 7, '7'), ( 8, '8'),
( 9, '9'), ( 10, 'a'), ( 11, 'b'), ( 12, 'c'),
( 13, 'd'), ( 14, 'e'), ( 15, 'f') ) AS V (D, H)
WHERE SUBSTRING(Seg, Number, 1) = H) AS HexToDec
WHERE Number BETWEEN 1 AND 4) AS Sub1)
SELECT *,
P1.I & P2.I
FROM StrToHexToInt AS P1
INNER JOIN StrToHexToInt AS P2
ON P1.Seq = P2.Seq
WHERE P1.ID = 1
AND P2.ID = 2;
This uses a Numbers table, which is simply a table of sequential integer values. In my case, it goes from 0 to 10-thousand. Useful in parsing things.
First, it parses the IPv6 into its constituent octets. Then it parses those using a Hex-string to Integer function, also using the Numbers table and a TVF of Hex-Int values.
Then it queries that using the bitwise & operator. Final value is decimal representation of bitwise value, as per bitwise & in T-SQL. If you need that converted to binary or hex, you'll need to write that.
You'll need to modify the above to use your table, or input parameters, instead of the hard-coded IPv6 values that I fed it. Just get rid of the first TVF (table-value-function), where it has VALUES, and plug in your actual table and column. Then replace the ID = 1 and ID = 2 pieces with appropriate input parameters. Or turn the whole thing into a proc and put the parameters in the TVF instead of hard-coded strings. Either way will work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 23, 2012 at 3:58 pm
GSquared (7/23/2012)
If they're strings, something like this is what's needed:(snipped)
Or - since 2008, you can simply use CONVERT:
select convert(bigint,CONVERT(varbinary(32),'20120DB8AC10FE01',2))
That said - your best bet in this case might simply be to set up a string parser. /64 means "64 most significant BITS in a 32-digit wide HEX notation", so /64 = first 16 digits in the hex notation, assuming you've formatted it to a 32-digit wide representation.
Frankly - for the return you're going for - leverage CLR. You will have a LOT better time at it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply