Fwd: data type for ip addresses

Reply, instead of Reply-All, didn't go to list... ---------- Forwarded message ---------- From: Jonathan Leffler <jleffler.iiug@gmail.com> Date: Fri, Jun 19, 2009 at 11:29 Subject: Re: data type for ip addresses To: steven_nospam at Yahoo! Canada <steven_nospam@yahoo.ca> On Fri, Jun 19, 2009 at 08:54, steven_nospam at Yahoo! Canada <steven_nospam@yahoo.ca> wrote: > > On Jun 19, 3:59 am, Holger de Wall <hol...@dewall-net.de> wrote: > > how can I create a data type that can store ip addresses (ipv4 and ipv6)? > > We use IDS 10.00 UC6 and 11.50 UC3. > > > I would probably store it in a field CHAR(15) and just have > "xxx.xxx.xxx.xxx" stored inside. That's OK  for IPv4; not so good for IPv6. Or, rather, the length is not long enough for IPv6. IPv6 addresses are 128-bits - that's up to 32 hex digits, with separators every 4 hex digits, and some compression available for strings of zeroes. XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX That needs VARCHAR(39), I think - and probably VARCHAR rather than CHAR, though it isn't the biggest waste of space in this world. > > However, you could also decide to store it as four fields of SMALLINT. > If I am not mistaken, using four SMALLINT fields would store each > octet as a number and only use 4 bytes, whereas the CHAR field would > use 15 bytes and you would have to do conversions from the CHAR to an > integer for each octet to do any validation of values. (ie. must be in > range 1-255). Definite IPv4-centrism... > > I guess the question is how many rows are we talking about (to see a > significant savings in row size) and will you want to do anything to > the field in that data? -- Jonathan Leffler                   #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Rodney Dangerfield  - "What a dog I got, his favorite bone is in my arm." -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. P. J. O'Rourke - "If government were a product, selling it would be illegal." - http://www.brainyquote.com/quotes/authors/p/p_j_orourke.html