IP address functions

• 1: INET_ATON
• 2: INET_NTOA
• 3: V6_ATON
• 4: V6_NTOA
• 5: V6_SUBNETA
• 6: V6_SUBNETN
• 7: V6_TYPE

1 - INET_ATON

Converts a string that contains a dotted-quad representation of an IPv4 network address to an INTEGER. It trims any surrounding white space from the string. This function returns NULL if the string is NULL or contains anything other than a quad dotted IPv4 address.

Behavior type

Syntax

INET_ATON ( expression )

Arguments

expression

the string to convert.

Examples

=> SELECT INET_ATON('209.207.224.40');
 inet_aton
------------
 3520061480
(1 row)

=> SELECT INET_ATON('1.2.3.4');
 inet_aton
-----------
  16909060
(1 row)

=> SELECT TO_HEX(INET_ATON('1.2.3.4'));
 to_hex
---------
 1020304
(1 row)

See also

• INET_NTOA
• V6_ATON
• V6_NTOA
• V6_SUBNETA
• V6_SUBNETN
• V6_TYPE

2 - INET_NTOA

Converts an INTEGER value into a VARCHAR dotted-quad representation of an IPv4 network address. INET_NTOA returns NULL if the integer value is NULL, negative, or is greater than 2³² (4294967295).

Behavior type

Syntax

INET_NTOA ( expression )

Arguments

expression

The integer network address to convert.

Examples

=> SELECT INET_NTOA(16909060);
 inet_ntoa
-----------
 1.2.3.4
(1 row)

=> SELECT INET_NTOA(03021962);
 inet_ntoa
-------------
 0.46.28.138
(1 row)

See also

• INET_ATON
• V6_ATON
• V6_NTOA
• V6_SUBNETA
• V6_SUBNETN
• V6_TYPE

3 - V6_ATON

Converts a string containing a colon-delimited IPv6 network address into a VARBINARY string. Any spaces around the IPv6 address are trimmed. This function returns NULL if the input value is NULL or it cannot be parsed as an IPv6 address. This function relies on the Linux function inet_pton.

Behavior type

Syntax

V6_ATON ( expression )

Arguments

expression

(VARCHAR) the string containing an IPv6 address to convert.

Examples

=> SELECT V6_ATON('2001:DB8::8:800:200C:417A');
                       v6_aton
------------------------------------------------------
  \001\015\270\000\000\000\000\000\010\010\000 \014Az
(1 row)

=> SELECT V6_ATON('1.2.3.4');
              v6_aton
------------------------------------------------------------------
 \000\000\000\000\000\000\000\000\000\000\377\377\001\002\003\004
(1 row)
SELECT TO_HEX(V6_ATON('2001:DB8::8:800:200C:417A'));
              to_hex
----------------------------------
 20010db80000000000080800200c417a
(1 row)

=> SELECT V6_ATON('::1.2.3.4');
              v6_aton
------------------------------------------------------------------
 \000\000\000\000\000\000\000\000\000\000\000\000\001\002\003\004
(1 row)

See also

• V6_NTOA
• V6_SUBNETA
• V6_SUBNETN
• V6_TYPE
• INET_ATON
• INET_NTOA

4 - V6_NTOA

Converts an IPv6 address represented as varbinary to a character string.

Behavior type

Syntax

V6_NTOA ( expression )

Arguments

expression

(VARBINARY) is the binary string to convert.

Notes

The following syntax converts an IPv6 address represented as VARBINARY B to a string A.

V6_NTOA right-pads B to 16 bytes with zeros, if necessary, and calls the Linux function inet_ntop.

=> V6_NTOA(VARBINARY B) -> VARCHAR A

If B is NULL or longer than 16 bytes, the result is NULL.

Vertica automatically converts the form '::ffff:1.2.3.4' to '1.2.3.4'.

Examples

=> SELECT V6_NTOA(' \001\015\270\000\000\000\000\000\010\010\000 \014Az');
          v6_ntoa
---------------------------
 2001:db8::8:800:200c:417a
(1 row)

=> SELECT V6_NTOA(V6_ATON('1.2.3.4'));
 v6_ntoa
---------
 1.2.3.4
(1 row)

=> SELECT V6_NTOA(V6_ATON('::1.2.3.4'));
  v6_ntoa
-----------
 ::1.2.3.4
(1 row)

See also

• V6_ATON

5 - V6_SUBNETA

Returns a VARCHAR containing a subnet address in CIDR (Classless Inter-Domain Routing) format from a binary or alphanumeric IPv6 address. Returns NULL if either parameter is NULL, the address cannot be parsed as an IPv6 address, or the subnet value is outside the range of 0 to 128.

Behavior type

Syntax

V6_SUBNETA ( address, subnet)

Arguments

address

VARBINARY or VARCHAR containing the IPv6 address.

subnet

The size of the subnet in bits as an INTEGER. This value must be greater than zero and less than or equal to 128.

Examples

=> SELECT V6_SUBNETA(V6_ATON('2001:db8::8:800:200c:417a'), 28);
  v6_subneta
---------------
 2001:db0::/28
(1 row)

See also

• V6_ATON
• V6_NTOA
• V6_SUBNETN
• V6_TYPE
• INET_ATON
• INET_NTOA

6 - V6_SUBNETN

Calculates a subnet address in CIDR (Classless Inter-Domain Routing) format from a varbinary or alphanumeric IPv6 address.

Behavior type

Syntax

V6_SUBNETN ( address, subnet-size)

Arguments

address

The IPv6 address as a VARBINARY or VARCHAR. The format you pass in determines the date type of the output. If you pass in a VARBINARY address, V6_SUBNETN returns a VARBINARY value. If you pass in a VARCHAR value, it returns a VARCHAR.

subnet-size

The size of the subnet as an INTEGER.

Notes

The following syntax masks a BINARY IPv6 address B so that the N left-most bits of S form a subnet address, while the remaining right-most bits are cleared.

V6_SUBNETN right-pads B to 16 bytes with zeros, if necessary and masks B, preserving its N-bit subnet prefix.

=> V6_SUBNETN(VARBINARY B, INT8 N) -> VARBINARY(16) S

If B is NULL or longer than 16 bytes, or if N is not between 0 and 128 inclusive, the result is NULL.

S = [B]/N in Classless Inter-Domain Routing notation (CIDR notation).

The following syntax masks an alphanumeric IPv6 address A so that the N leftmost bits form a subnet address, while the remaining rightmost bits are cleared.

=> V6_SUBNETN(VARCHAR A, INT8 N) -> V6_SUBNETN(V6_ATON(A), N) -> VARBINARY(16) S

Examples

This example returns VARBINARY, after using V6_ATON to convert the VARCHAR string to VARBINARY:

=> SELECT V6_SUBNETN(V6_ATON('2001:db8::8:800:200c:417a'), 28);
                           v6_subnetn
---------------------------------------------------------------
  \001\015\260\000\000\000\000\000\000\000\000\000\000\000\000

See also

• V6_ATON
• V6_NTOA
• V6_TYPE
• V6_SUBNETA
• INET_ATON
• INET_NTOA

7 - V6_TYPE

Returns an INTEGER value that classifies the type of the network address passed to it as defined in IETF RFC 4291 section 2.4. For example, If you pass this function the string 127.0.0.1, it returns 2 which indicates the address is a loopback address. This function accepts both IPv4 and IPv6 addresses.

Behavior type

Syntax

V6_TYPE ( address)

Arguments

address

A VARBINARY or VARCHAR containing an IPv6 or IPv4 address to describe.

Returns

The values returned by this function are:

The return value is based on the following table of IP address ranges:

This function returns NULL if you pass it a NULL value or an invalid address.

Examples

=> SELECT V6_TYPE(V6_ATON('192.168.2.10'));
 v6_type
---------
       1
(1 row)

=> SELECT V6_TYPE(V6_ATON('2001:db8::8:800:200c:417a'));
 v6_type
---------
       0
(1 row)

See also

• V6_ATON
• V6_NTOA
• INET_ATON
• INET_NTOA
• V6_SUBNETN
• V6_SUBNETA

• IP Version 6 Addressing Architecture

• IPv4 Global Unicast Address Assignments