What datatype use for store IPV6 values in database

For storing IPV4 value we are using datatype int, IPV4 have 32 bit length. IPV6 have 128 bit length, and in this case if use in the same manner as IPV4 need use datatype numeric(39)or BINARY(16)!?. May be anyone have solved this issue. Thanks in advance

sa-12

asked 22 Mar '12, 10:24

HBrener
426●23●25●35
accept rate: 0%

flat view

2 Answers:

active answers oldest answers newest answers popular answers

3	I think the simplest way would to store them as a string in a varchar(39) column. permanent link answered 22 Mar '12, 13:02 Graeme Perrow 9.6k●3●79●124 accept rate: 54% oooo, a literal believer of the Canonical Text! :) (22 Mar '12, 14:37) Breck Carter flat view

I think it depends on how you will be using the IP addresses. BINARY(16) is probably most efficient for storage and comparisons, but VARCHAR(39) would be best for display.

FYI unique identifiers are also 128 bit values. For those, SQL Anywhere stores UNIQUEIDENTIFIER values internally as BINARY(16), and also provides functions for converting to and from string representations.

permanent link

answered 23 Mar '12, 08:47

Graham Hurst
2.7k●1●18●43
accept rate: 29%

edited 23 Mar '12, 14:02

You mean we can store the IPv6 as a UNIQUEIDENTIFIER ;-)

(23 Mar '12, 09:47) Martin

Replies hidden

Additional question - in case using type BINARY(16), how load/insert data for this column. If load data from text file(using LOAD TABLE statement with HEXADECIMAL is ON) the value could be presentated in file as 0xnnnnnnnnnnnnnnn?

(23 Mar '12, 13:22) HBrener

Replies hidden

I suppose, but I can't see a benefit over BINARY(16), since the extras for UNIQUEIDENTIFIER don't apply to IPv6 addresses.

(23 Mar '12, 14:04) Graham Hurst

That appears to work...

CREATE TABLE x (
   h BINARY ( 16 ) );
BEGIN
DECLARE s VARCHAR ( 100 );
SET s = '0x123456789ABCDEF1';
LOAD TABLE x USING VALUE s;
SELECT * FROM x;
END;
h
0x123456789abcdef1

(23 Mar '12, 14:19) Breck Carter

The challenge will be parsing the wide variety of text representations down into the simple numeric representation: http://tools.ietf.org/html/rfc5952#section-1

That's why I like Graham's suggestion of VARCHAR ( 39 ).

(23 Mar '12, 14:29) Breck Carter

I think you meant Graeme. :)

Yes, VARCHAR( 39 ) would be easiest for loading the multitude of text formats and human readability. But if you want to do comparisons (eg. use it as a primary key), save storage space, or don't need to use SQL to parse all valid text formats, then BINARY(16) might be preferred.

(23 Mar '12, 18:56) Graham Hurst

Good idea, if feature version of SA, especially SA web edition/fuji will have built-in functions supporting text to binary and binary to text transformation of ipv6 address according to http://tools.ietf.org/html/rfc5952#section-4 recommendations.

(24 Mar '12, 05:44) AlexeyK77

Ahhh, yes but you BOTH mentioned VARCHAR(39) and I didn't scroll up far enough.

...sounds like fun writing the conversion functions :)

(24 Mar '12, 09:11) Breck Carter

showing 3 of 8 show all flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12 ×438

question asked: 22 Mar '12, 10:24

question was seen: 3,469 times

last updated: 24 Mar '12, 09:11

SAP SQL Anywhere

What datatype use for store IPV6 values in database

Follow this question

Related questions