Net functions

GoogleSQL for BigQuery supports the following Net functions.

Function list

Name Summary
NET.HOST Gets the hostname from a URL.
NET.IP_FROM_STRING Converts an IPv4 or IPv6 address from a STRING value to a BYTES value in network byte order.
NET.IP_NET_MASK Gets a network mask.
NET.IP_TO_STRING Converts an IPv4 or IPv6 address from a BYTES value in network byte order to a STRING value.
NET.IP_TRUNC Converts a BYTES IPv4 or IPv6 address in network byte order to a BYTES subnet address.
NET.IPV4_FROM_INT64 Converts an IPv4 address from an INT64 value to a BYTES value in network byte order.
NET.IPV4_TO_INT64 Converts an IPv4 address from a BYTES value in network byte order to an INT64 value.
NET.PUBLIC_SUFFIX Gets the public suffix from a URL.
NET.REG_DOMAIN Gets the registered or registrable domain from a URL.
NET.SAFE_IP_FROM_STRING Similar to the NET.IP_FROM_STRING, but returns NULL instead of producing an error if the input is invalid.

NET.HOST

NET.HOST(url)

Description

Takes a URL as a STRING value and returns the host. For best results, URL values should comply with the format as defined by RFC 3986. If the URL value does not comply with RFC 3986 formatting, this function makes a best effort to parse the input and return a relevant result. If the function cannot parse the input, it returns NULL.

Return Data Type

STRING

Example

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Example.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
input description host suffix domain
"" invalid input NULL NULL NULL
"http://abc.xyz" standard URL "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" standard URL with relative scheme, port, path and query, but no public suffix "a.b" NULL NULL
"https://[::1]:80" standard URL with IPv6 host "[::1]" NULL NULL
"http://例子.卷筒纸.中国" standard URL with internationalized domain name "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Example.Co.UK    " non-standard URL with spaces, upper case letters, and without scheme "www.Example.Co.UK" "Co.UK" "Example.Co.UK"
"mailto:?to=&subject=&body=" URI rather than URL--unsupported "mailto" NULL NULL

NET.IP_FROM_STRING

NET.IP_FROM_STRING(addr_str)

Description

Converts an IPv4 or IPv6 address from text (STRING) format to binary (BYTES) format in network byte order.

This function supports the following formats for addr_str:

  • IPv4: Dotted-quad format. For example, 10.1.2.3.
  • IPv6: Colon-separated format. For example, 1234:5678:90ab:cdef:1234:5678:90ab:cdef. For more examples, see the IP Version 6 Addressing Architecture.

This function does not support CIDR notation, such as 10.1.2.3/32.

If this function receives a NULL input, it returns NULL. If the input is considered invalid, an OUT_OF_RANGE error occurs.

Return Data Type

BYTES

Example

SELECT
  addr_str, FORMAT("%T", NET.IP_FROM_STRING(addr_str)) AS ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128'
]) AS addr_str;

/*---------------------------------------------------------------------------------------------------------------*
 | addr_str                                | ip_from_string                                                      |
 +---------------------------------------------------------------------------------------------------------------+
 | 48.49.50.51                             | b"0123"                                                             |
 | ::1                                     | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01" |
 | 3031:3233:3435:3637:3839:4041:4243:4445 | b"0123456789@ABCDE"                                                 |
 | ::ffff:192.0.2.128                      | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80" |
 *---------------------------------------------------------------------------------------------------------------*/

NET.IP_NET_MASK