Skip to navigation
SQL Reference

Network Type Functions

View as Markdown

The Ocient® System supports these functions for IP and IPV4 data types.

IP

Casts the IPV4 data type to the IP data type. The returned value is the IPv6-mapped IPv4 address.

Syntax

IP(ipv4_col)
ArgumentData TypeDescription
ipv4_colCHAR or IPV4IP address in the IPV4 data type to cast.

Example

Cast the IPv4 address 128.1.2.3 to the IPv6-mapped IPv4 address.

SELECT IP(IPV4('128.1.2.3'));

Output

ip(ipv4(('128.1.2.3')))
-----------------------------------------------
128.1.2.3
Fetched 1 row

IPV4

Returns the IPv4 portion of the IPv6 address.

You can only cast the IP data type to IPv4 if the IPv6 value has an IPv4 semantic equivalent. The database can convert these IPv6 address formats to IPv4:

  • IPv4-compatible IPv6 ::x.x.x.x
  • IPv6-mapped IPv4 ::ffff:x.x.x.x
  • NAT64-prefixed 64:ff9b::x.x.x.x

Syntax

IPV4(ip_col)
ArgumentData TypeDescription
ip_colIPIPv6-mapped IP address in the IP data type to cast.

If the IPv6 address is not IPv4-compatible, IPv6-mapped, or NAT64-prefixed, the cast returns an INVALID DATA TYPE CONVERSION error.

Example

Cast the IPv6-mapped IP address ::ffff:128.1.2.3 to the IPV4 data type. Return the IPv4 portion of the address.

SELECT IPV4(IP('::ffff:128.1.2.3'));

Output

ipv4(ip(('::ffff:128.1.2.3')))
-------------------------------
128.1.2.3
Fetched 1 row

IS_IPV4

The IS_IPV4 function tests whether the database can convert the IP value to the IPV4 data type. The database can convert these IPv6 address formats to IPv4:

  • IPv4-compatible IPv6 ::x.x.x.x
  • IPv6-mapped IPv4 ::ffff:x.x.x.x
  • NAT64-prefixed 64:ff9b::x.x.x.x

The function returns a Boolean value (true or false) that indicates whether the conversion works from the specified IP address to the IPv4 address.

Syntax

IS_IPV4(ip_col)
ArgumentData TypeDescription
ip_colIPSpecified IP address for conversion to an IPv4 address.

Example

Determine whether the ::fffe:128.1.2.3 string is a valid IPv4 address. Use the IP function to cast the string as an IP address.

SELECT IS_IPV4(IP('::fffe:128.1.2.3'));

Output

is_ipv4_convertible(ip(('::fffe:128.1.2.3')))
----------------------------------------------
false
Fetched 1 row

SUBNET

The SUBNET function computes the prefix from an IP or IPV4 value and the size of the prefix. The function returns a value of the same type as the ip_col argument.

Syntax

SUBNET(ip_col, prefix_size)
ArgumentData TypeDescription
ip_colIP or IPV4The IP address for the prefix calculation.
prefix_sizeIntegerThe size of the subnet network prefix. For IPV4 addresses, the size must be in the range [0, 32], and for IP addresses, the size must be in the range [0, 128]. If the prefix exceeds these range values, the function returns an INVALID ARGUMENT error.

Example

Calculate the prefix for the NAT64-prefixed IP address 64:ff9b::128.2.3.4 with prefix size 98. Use the IP function to cast the string as an IP address.

SELECT SUBNET(IP('64:ff9b::128.2.3.4'), 98);

Output

subnet(ip(('64:ff9b::128.2.3.4')), (98))
-----------------------------------------------
64:ff9b:0:0:0:0:8000:0
Fetched 1 row

Data Types