SQL Reference

Network Type Functions

The 
﻿ System supports these functions for IP and IPV4 data types.
IP 
The IPV4 data type is castable to IP. The returned value is the IPV6-mapped IPV4 address, which has the form ::ffff:x.x.x.x.
Syntax
SQL
IP(ipv4_col)
IP(ipv4_col)
﻿
Argument
Data Type
Description
ipv4_col
IPV4 
Returns the IPV6-mapped IPV4 address.
Example
SQL
SELECT IP(IPV4('128.1.2.3'));
SELECT IP(IPV4('128.1.2.3'));
﻿
Output
Text
ip(ipv4(('128.1.2.3')))                        
-----------------------------------------------
/128.1.2.3 

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

Fetched 1 row
﻿
IPV4
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
SQL
IPV4(ip_col)
IPV4(ip_col)
﻿
Argument
Data Type
Description
ip_col
IP 
Returns the IPV4 portion of the IPV6 address. If the IPV6 address is not IPV4-compatible, IPV6-mapped, or NAT64, the cast returns an INVALID DATA TYPE CONVERSION error.
Example
SQL
SELECT IPV4(IP('::ffff:128.1.2.3'));
SELECT IPV4(IP('::ffff:128.1.2.3'));
﻿
Output
Text
ipv4(ip(('::ffff:128.1.2.3'))) 
-------------------------------
/128.1.2.3 

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

Fetched 1 row
﻿
IS_IPV4
The IS_IPV4 Boolean 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
Syntax
SQL
IS_IPV4(ip_col)
IS_IPV4(ip_col)
﻿
Argument
Data Type
Description
ip_col
IP 
Denotes whether the database can convert the specified IP value to the IPV4 data type with return values true or false.
Example
SQL
SELECT IS_IPV4(IP('::fffe:128.1.2.3'));
SELECT IS_IPV4(IP('::fffe:128.1.2.3'));
﻿
Output
Text
is_ipv4_convertible(ip(('::fffe:128.1.2.3'))) 
----------------------------------------------
false 

Fetched 1 row
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 ip_col.
Syntax
SQL
SUBNET(ip_col, prefix_size)
SUBNET(ip_col, prefix_size)
﻿
Argument
Data Type
Description
ip_col
IP, IPV4
The IP address from which to calculate the prefix.
prefix_size
Integral
The size of the subnet network prefix. For IPV4 addresses, the size must be in [0, 32], and for IP addresses, the size must be in [0, 128]. If the prefix exceeds these range values, the function returns an INVALID ARGUMENT error.
Example
SQL
SELECT SUBNET(IP('64:ff9b::128.2.3.4'), 98);
SELECT SUBNET(IP('64:ff9b::128.2.3.4'), 98);
﻿
Output
Text
subnet(ip(('64:ff9b::128.2.3.4')), (98))       
-----------------------------------------------
/64:ff9b:0:0:0:0:8000:0 

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

Fetched 1 row
﻿
Related Links
﻿Data Types﻿﻿
﻿

Argument	Data Type	Description
ipv4_col	IPV4	Returns the IPV6-mapped IPV4 address.

Argument	Data Type	Description
ip_col	IP	Returns the IPV4 portion of the IPV6 address. If the IPV6 address is not IPV4-compatible, IPV6-mapped, or NAT64, the cast returns an INVALID DATA TYPE CONVERSION error.

Argument	Data Type	Description
ip_col	IP	Denotes whether the database can convert the specified IP value to the IPV4 data type with return values true or false.

Argument	Data Type	Description
ip_col	IP, IPV4	The IP address from which to calculate the prefix.
prefix_size	Integral	The size of the subnet network prefix. For IPV4 addresses, the size must be in [0, 32], and for IP addresses, the size must be in [0, 128]. If the prefix exceeds these range values, the function returns an INVALID ARGUMENT error.

Updated 14 May 2024

Did this page help you?

Matrix Functions and Operators

Scalar Data Conversion Functions