Vector 7.0 | IP Network Address Functions

SQL Language Guide > SQL Language Guide > Elements of SQL Statements > SQL Functions > IP Network Address Functions

Was this helpful?

IP Network Address Functions

IP network address functions operate on an IPV4 or IPV6 data type or string representing an IPv4 or IPv6 address and return a string in dotted quad or colon-hex format or an IPv4 or IPv6 binary.

INET_NTOP

INET_NTOP(expr)

Operand type: IPV4 or IPV6

Result type: Dotted quad string or colon-hex string, depending on input type

Returns a dotted quad string if the input type is IPV4 or is IPV6 and an IPV4-mapped address.

Returns an IPv6 address in rfc5952 style (zero suppressed, shortened, lowercase) if the input type is IPV6 and is not an IPv4-mapped address.

SELECT INET_NTOP(INET_PTON('172.16.254.1'))

returns

'172.16.254.1'

INET_PTON

INET_PTON(expr)

IPV4(expr)

Operand type: A string representing an IPv4 network address or an IPv4-mapped IPv6 address

Result type: IPv4 binary

Converts a string in standard IPv4 dotted-quad notation to its IPv4 binary equivalent.

The expression can also be an IPv4-mapped IPv6 address in the form:

"::ffff:xxx:xxx" or "::ffff:n.n.n.n"

and zero-expanded or uppercased variants of the same. An error occurs if the input is an IPv6 style string that does not represent an IPv4-mapped address.

SELECT INET_PTON('172.16.254.1');

returns the following (using hexadeximal representation):

AC10FE01

INET6_NTOP

INET6_NTOP(expr)

Operand type: IPV4 or IPV6

Result type: Colon-hex string

Returns an IPv6 address in rfc5952 style (zero suppressed, shortened, lowercase)

SELECT INET6_NTOP(INET6_PTON('172.16.254.1'))

returns

'::ffff:ac10:fe01'

INET6_PTON

INET6_PTON(expr)

IPV6(expr)

Operand type: A string representing an IPv4 or IPv6 network address

Result type: IPv6 binary

Converts a string in standard hex-colon notation to an IPv6 binary equivalent. Also converts an IPv4 address in either ordinary dotted-quad or "::ffff:n.n.n.n" mixed quad notation to an IPv4-mapped address ::ffff:n.n.n.n.

The input can be leading-zero-suppressed. Use one "::" to replace the longest sequence of all-zero fields and use lowercase letters. The function also accepts uppercase hexits as input.

SELECT INET6_PTON('2001:0DB8:AC10:FE01:0000:0000:0000:0000')

returns the following (using hexadecimal representation):

20010DB8AC10FE010000000000000000

UUID ([expr]) Function

With no parameters, the UUID() function creates a 128-bit universal unique identifier (UUID).

> createdb uuiddb

> sql uuiddb

* CREATE TABLE uuidtable (u1 UUID);

* INSERT INTO uuidtable VALUES ('f219d260-cb36-4fc9-b3ec-c961db90129b');

* select * from uuidtable;

// Verify the length in byte format

* SELECT LENGTH(u1) FROM uuidtable;

//Length of u1 will be 36 bytes representing the length of the ASCII representation

With a parameter, UUID (expr) will convert that parameter to a UUID if possible. Character string data types must follow the 36-byte ASCII HEX format including the hyphens: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx or the 32-byte ASCII HEX strings that omit all hyphens.

Examples for {lower, upper, mixed}x{36-byte, 32-byte}:

abad1dea-dead-c001-cafe-f01dab1ec0de

abad1deadeadc001cafef01dab1ec0de

DA7ABA5E-900D-BADD-00DD-B119C0DEB10B

DA7ABA5E900DBADD00DDB119C0DEB10B

Ca11Ab1e-C0dE-4b1D-BaAd-b199Ca55e77E

Ca11Ab1eC0dE4b1DBaAdb199Ca55e77E

Last modified date: 12/19/2024