create ipaddress table

Question

create ipaddress table

Staggerlee

SSCertifiable

Points: 5272
More actions
December 12, 2011 at 4:49 am

#249584

Hi all,
trying to learn a tsql dev stuff so decided to look at changing our ip document from a word file to a database.
How do you prefer to hold ip address in sql? if i do a vchar (15) its not going to order by correctly is it? but putting them into 3 rows seems kind of confusing to me!
Any help would be great
Thanks
s

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

This article discusses the options[/url]

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Staggerlee SSCertifiable Points: 5272 More actions · Answer 2

thanks.. taking a look now.. i did a search but clearly didnt look at articles 🙁

Also found this:

SELECT * FROM [ipaddress]

ORDER BY CAST(PARSENAME([IPAddress], 4) AS INT)--,

CAST(PARSENAME([IPAddress], 3) AS INT),

CAST(PARSENAME([IPAddress], 2) AS INT),

CAST(PARSENAME([IPAddress], 1) AS INT)

which also sorts if you have them all on a single column. (time to look up cast and parsename!)

Thanks again

S

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

stebennettsjb (12/12/2011)
Hi all,
trying to learn a tsql dev stuff so decided to look at changing our ip document from a word file to a database.
How do you prefer to hold ip address in sql? if i do a vchar (15) its not going to order by correctly is it? but putting them into 3 rows seems kind of confusing to me!
Any help would be great
Thanks
s

Based on that and similar articles plus some personal experience, I'll typically use 4 TinyInt columns along with a calculated persisted column to reassemble the IP4 addresses. IP6 is a whole 'nuther story. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Staggerlee SSCertifiable Points: 5272 More actions · Answer 4

thanks guys... gone the 4 col way and so far so good 🙂

okbangas SSChampion Points: 11773 More actions · Answer 5

Jeff Moden (12/12/2011)
IP6 is a whole 'nuther story. 😉

Especially IPv4-mapped IPv6 addresses, like ::ffff:192.0.2.128 🙂

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

sturner SSC-Insane Points: 22411 More actions · Answer 6

For some tables I store it as a bigint (convert to bigint via CLR function in insert). Very efficient and works well for huge tables or ones that might be indexed on the IP address. It is also simple to convert back into char format for human consumption.

The probability of survival is inversely proportional to the angle of arrival.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

sturner (12/14/2011)
For some tables I store it as a bigint (convert to bigint via CLR function in insert). Very efficient and works well for huge tables or ones that might be indexed on the IP address. It is also simple to convert back into char format for human consumption.

What do you use for code to "mask" the first 3 octets for a given value on such a bigint?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sturner SSC-Insane Points: 22411 More actions · Answer 8

Actually have not needed to do any specific masking operations on the IP addresses as we are looking up the region (city state, country) and organization for fraud detection purposes. masking off various octets is more useful for actual routing or other things.

Having said all that, masking off octects in a bigint is no different than masking off bits in any other integer.

ie: to mask off the the upper three octets:

set @IP = (@IP & 0xFFFFFF00)

or to mask off the lower three bits:

set @IP = (@P & 0x00000007)

Nice and easy to understand.

The probability of survival is inversely proportional to the angle of arrival.