Net functions

GoogleSQL for BigQuery supports the following Net functions.

Function list

NameSummary
NET.HOST Gets the hostname from a URL.
NET.IP_FROM_STRING Converts an IPv4 or IPv6 address from aSTRING value to aBYTES value in network byte order.
NET.IP_NET_MASK Gets a network mask.
NET.IP_TO_STRING Converts an IPv4 or IPv6 address from aBYTES value in network byte order to aSTRING value.
NET.IP_TRUNC Converts aBYTES IPv4 or IPv6 address in network byte order to aBYTES subnet address.
NET.IPV4_FROM_INT64 Converts an IPv4 address from anINT64 value to aBYTES value in network byte order.
NET.IPV4_TO_INT64 Converts an IPv4 address from aBYTES value in network byte order to anINT64 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 theNET.IP_FROM_STRING, but returnsNULL instead of producing an error if the input is invalid.

NET.HOST

NET.HOST(url)

Description

Takes a URL as aSTRING value and returns the host. For best results, URLvalues should comply with the format as defined byRFC 3986. If the URL value doesn't complywith RFC 3986 formatting, this function makes a best effort to parse the inputand return a relevant result. If the function can't parse the input, itreturnsNULL.

Note: The function doesn't perform any normalization.

Return Data Type

STRING

Example

SELECTFORMAT("%T",input)ASinput,description,FORMAT("%T",NET.HOST(input))AShost,FORMAT("%T",NET.PUBLIC_SUFFIX(input))ASsuffix,FORMAT("%T",NET.REG_DOMAIN(input))ASdomainFROM(SELECT""ASinput,"invalid input"ASdescriptionUNIONALLSELECT"http://abc.xyz","standard URL"UNIONALLSELECT"//user:password@a.b:80/path?query","standard URL with relative scheme, port, path and query, but no public suffix"UNIONALLSELECT"https://[::1]:80","standard URL with IPv6 host"UNIONALLSELECT"http://例子.卷筒纸.中国","standard URL with internationalized domain name"UNIONALLSELECT"    www.Example.Co.UK    ","non-standard URL with spaces, upper case letters, and without scheme"UNIONALLSELECT"mailto:?to=&subject=&body=","URI rather than URL--unsupported");
inputdescriptionhostsuffixdomain
""invalid inputNULLNULLNULL
"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"NULLNULL
"https://[::1]:80"standard URL with IPv6 host"[::1]"NULLNULL
"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"NULLNULL

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 foraddr_str:

This function doesn't supportCIDR notation, such as10.1.2.3/32.

If this function receives aNULL input, it returnsNULL. If the input isconsidered invalid, anOUT_OF_RANGE error occurs.

Return Data Type

BYTES

Example

SELECTaddr_str,FORMAT("%T",NET.IP_FROM_STRING(addr_str))ASip_from_stringFROMUNNEST(['48.49.50.51','::1','3031:3233:3435:3637:3839:4041:4243:4445','::ffff:192.0.2.128'])ASaddr_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

NET.IP_NET_MASK(num_output_bytes,prefix_length)

Description

Returns a network mask: a byte sequence with length equal tonum_output_bytes,where the firstprefix_length bits are set to 1 and the other bits are set to0.num_output_bytes andprefix_length are INT64.This function throws an error ifnum_output_bytes isn't 4 (for IPv4) or 16(for IPv6). It also throws an error ifprefix_length is negative or greaterthan8 * num_output_bytes.

Return Data Type

BYTES

Example

SELECTx,y,FORMAT("%T",NET.IP_NET_MASK(x,y))ASip_net_maskFROMUNNEST([STRUCT(4asx,0asy),(4,20),(4,32),(16,0),(16,1),(16,128)]);/*--------------------------------------------------------------------------------+ | x  | y   | ip_net_mask                                                         | +--------------------------------------------------------------------------------+ | 4  | 0   | b"\x00\x00\x00\x00"                                                 | | 4  | 20  | b"\xff\xff\xf0\x00"                                                 | | 4  | 32  | b"\xff\xff\xff\xff"                                                 | | 16 | 0   | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00" | | 16 | 1   | b"\x80\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00" | | 16 | 128 | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | +--------------------------------------------------------------------------------*/

NET.IP_TO_STRING

NET.IP_TO_STRING(addr_bin)

DescriptionConverts an IPv4 or IPv6 address from binary (BYTES) format in network byteorder to text (STRING) format.

If the input is 4 bytes, this function returns an IPv4 address as a STRING. Ifthe input is 16 bytes, it returns an IPv6 address as a STRING.

If this function receives aNULL input, it returnsNULL. If the input hasa length different from 4 or 16, anOUT_OF_RANGE error occurs.

Return Data Type

STRING

Example

SELECTFORMAT("%T",x)ASaddr_bin,NET.IP_TO_STRING(x)ASip_to_stringFROMUNNEST([b"0123",b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01",b"0123456789@ABCDE",b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"])ASx;/*---------------------------------------------------------------------------------------------------------------+ | addr_bin                                                            | ip_to_string                            | +---------------------------------------------------------------------------------------------------------------+ | b"0123"                                                             | 48.49.50.51                             | | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01" | ::1                                     | | b"0123456789@ABCDE"                                                 | 3031:3233:3435:3637:3839:4041:4243:4445 | | b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80" | ::ffff:192.0.2.128                      | +---------------------------------------------------------------------------------------------------------------*/

NET.IP_TRUNC

NET.IP_TRUNC(addr_bin,prefix_length)

DescriptionTakesaddr_bin, an IPv4 or IPv6 address in binary (BYTES) format in networkbyte order, and returns a subnet address in the same format. The result has thesame length asaddr_bin, where the firstprefix_length bits are equal tothose inaddr_bin and the remaining bits are 0.

This function throws an error ifLENGTH(addr_bin) isn't 4 or 16, or ifprefix_len is negative or greater thanLENGTH(addr_bin) * 8.

Return Data Type

BYTES

Example

SELECTFORMAT("%T",x)asaddr_bin,prefix_length,FORMAT("%T",NET.IP_TRUNC(x,prefix_length))ASip_truncFROMUNNEST([STRUCT(b"\xAA\xBB\xCC\xDD"asx,0asprefix_length),(b"\xAA\xBB\xCC\xDD",11),(b"\xAA\xBB\xCC\xDD",12),(b"\xAA\xBB\xCC\xDD",24),(b"\xAA\xBB\xCC\xDD",32),(b'0123456789@ABCDE',80)]);/*-----------------------------------------------------------------------------+ | addr_bin            | prefix_length | ip_trunc                              | +-----------------------------------------------------------------------------+ | b"\xaa\xbb\xcc\xdd" | 0             | b"\x00\x00\x00\x00"                   | | b"\xaa\xbb\xcc\xdd" | 11            | b"\xaa\xa0\x00\x00"                   | | b"\xaa\xbb\xcc\xdd" | 12            | b"\xaa\xb0\x00\x00"                   | | b"\xaa\xbb\xcc\xdd" | 24            | b"\xaa\xbb\xcc\x00"                   | | b"\xaa\xbb\xcc\xdd" | 32            | b"\xaa\xbb\xcc\xdd"                   | | b"0123456789@ABCDE" | 80            | b"0123456789\x00\x00\x00\x00\x00\x00" | +-----------------------------------------------------------------------------*/

NET.IPV4_FROM_INT64

NET.IPV4_FROM_INT64(integer_value)

Description

Converts an IPv4 address from integer format to binary (BYTES) format in networkbyte order. In the integer input, the least significant bit of the IP address isstored in the least significant bit of the integer, regardless of host or clientarchitecture. For example,1 means0.0.0.1, and0x1FF means0.0.1.255.

This function checks that either all the most significant 32 bits are 0, or allthe most significant 33 bits are 1 (sign-extended from a 32-bit integer).In other words, the input should be in the range[-0x80000000, 0xFFFFFFFF];otherwise, this function throws an error.

This function doesn't support IPv6.

Return Data Type

BYTES

Example

SELECTx,x_hex,FORMAT("%T",NET.IPV4_FROM_INT64(x))ASipv4_from_int64FROM(SELECTCAST(x_hexASINT64)x,x_hexFROMUNNEST(["0x0","0xABCDEF","0xFFFFFFFF","-0x1","-0x2"])ASx_hex);/*-----------------------------------------------+ | x          | x_hex      | ipv4_from_int64     | +-----------------------------------------------+ | 0          | 0x0        | b"\x00\x00\x00\x00" | | 11259375   | 0xABCDEF   | b"\x00\xab\xcd\xef" | | 4294967295 | 0xFFFFFFFF | b"\xff\xff\xff\xff" | | -1         | -0x1       | b"\xff\xff\xff\xff" | | -2         | -0x2       | b"\xff\xff\xff\xfe" | +-----------------------------------------------*/

NET.IPV4_TO_INT64

NET.IPV4_TO_INT64(addr_bin)

Description

Converts an IPv4 address from binary (BYTES) format in network byte order tointeger format. In the integer output, the least significant bit of the IPaddress is stored in the least significant bit of the integer, regardless ofhost or client architecture. For example,1 means0.0.0.1, and0x1FF means0.0.1.255. The output is in the range[0, 0xFFFFFFFF].

If the input length isn't 4, this function throws an error.

This function doesn't support IPv6.

Return Data Type

INT64

Example

SELECTFORMAT("%T",x)ASaddr_bin,FORMAT("0x%X",NET.IPV4_TO_INT64(x))ASipv4_to_int64FROMUNNEST([b"\x00\x00\x00\x00",b"\x00\xab\xcd\xef",b"\xff\xff\xff\xff"])ASx;/*-------------------------------------+ | addr_bin            | ipv4_to_int64 | +-------------------------------------+ | b"\x00\x00\x00\x00" | 0x0           | | b"\x00\xab\xcd\xef" | 0xABCDEF      | | b"\xff\xff\xff\xff" | 0xFFFFFFFF    | +-------------------------------------*/

NET.PUBLIC_SUFFIX

NET.PUBLIC_SUFFIX(url)

Description

Takes a URL as aSTRING value and returns the public suffix (such ascom,org, ornet). A public suffix is an ICANN domain registered atpublicsuffix.org. For best results, URL valuesshould comply with the format as defined byRFC 3986. If the URL value doesn't complywith RFC 3986 formatting, this function makes a best effort to parse the inputand return a relevant result.

This function returnsNULL if any of the following is true:

  • It can't parse the host from the input;
  • The parsed host contains adjacent dots in the middle(not leading or trailing);
  • The parsed host doesn't contain any public suffix.

Before looking up the public suffix, this function temporarily normalizes thehost by converting uppercase English letters to lowercase and encoding allnon-ASCII characters withPunycode.The function then returns the public suffix as part of the original host insteadof the normalized host.

Note: The function doesn't performUnicode normalization.Note: The public suffix data atpublicsuffix.org also containsprivate domains. This function ignores the private domains.Note: The public suffix data may change over time. Consequently, input thatproduces aNULL result now may produce a non-NULL value in the future.

Return Data Type

STRING

Example

SELECTFORMAT("%T",input)ASinput,description,FORMAT("%T",NET.HOST(input))AShost,FORMAT("%T",NET.PUBLIC_SUFFIX(input))ASsuffix,FORMAT("%T",NET.REG_DOMAIN(input))ASdomainFROM(SELECT""ASinput,"invalid input"ASdescriptionUNIONALLSELECT"http://abc.xyz","standard URL"UNIONALLSELECT"//user:password@a.b:80/path?query","standard URL with relative scheme, port, path and query, but no public suffix"UNIONALLSELECT"https://[::1]:80","standard URL with IPv6 host"UNIONALLSELECT"http://例子.卷筒纸.中国","standard URL with internationalized domain name"UNIONALLSELECT"    www.Example.Co.UK    ","non-standard URL with spaces, upper case letters, and without scheme"UNIONALLSELECT"mailto:?to=&subject=&body=","URI rather than URL--unsupported");
inputdescriptionhostsuffixdomain
""invalid inputNULLNULLNULL
"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"NULLNULL
"https://[::1]:80"standard URL with IPv6 host"[::1]"NULLNULL
"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"NULLNULL

NET.REG_DOMAIN

NET.REG_DOMAIN(url)

Description

Takes a URL as a string and returns the registered or registrable domain (thepublic suffix plus one preceding label), as astring. For best results, URL values should comply with the format as defined byRFC 3986. If the URL value doesn't complywith RFC 3986 formatting, this function makes a best effort to parse the inputand return a relevant result.

This function returnsNULL if any of the following is true:

  • It can't parse the host from the input;
  • The parsed host contains adjacent dots in the middle(not leading or trailing);
  • The parsed host doesn't contain any public suffix;
  • The parsed host contains only a public suffix without any preceding label.

Before looking up the public suffix, this function temporarily normalizes thehost by converting uppercase English letters to lowercase and encoding allnon-ASCII characters withPunycode. The function thenreturns the registered or registerable domain as part of the original hostinstead of the normalized host.

Note: The function doesn't performUnicode normalization.Note: The public suffix data atpublicsuffix.org also containsprivate domains. This function doesn't treat a private domain as a publicsuffix. For example, ifus.com is a private domain in the public suffix data,NET.REG_DOMAIN("foo.us.com") returnsus.com (the public suffixcom plusthe preceding labelus) rather thanfoo.us.com (the private domainus.complus the preceding labelfoo).Note: The public suffix data may change over time.Consequently, input that produces aNULL result now may produce a non-NULLvalue in the future.

Return Data Type

STRING

Example

SELECTFORMAT("%T",input)ASinput,description,FORMAT("%T",NET.HOST(input))AShost,FORMAT("%T",NET.PUBLIC_SUFFIX(input))ASsuffix,FORMAT("%T",NET.REG_DOMAIN(input))ASdomainFROM(SELECT""ASinput,"invalid input"ASdescriptionUNIONALLSELECT"http://abc.xyz","standard URL"UNIONALLSELECT"//user:password@a.b:80/path?query","standard URL with relative scheme, port, path and query, but no public suffix"UNIONALLSELECT"https://[::1]:80","standard URL with IPv6 host"UNIONALLSELECT"http://例子.卷筒纸.中国","standard URL with internationalized domain name"UNIONALLSELECT"    www.Example.Co.UK    ","non-standard URL with spaces, upper case letters, and without scheme"UNIONALLSELECT"mailto:?to=&subject=&body=","URI rather than URL--unsupported");
inputdescriptionhostsuffixdomain
""invalid inputNULLNULLNULL
"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"NULLNULL
"https://[::1]:80"standard URL with IPv6 host"[::1]"NULLNULL
"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"NULLNULL

NET.SAFE_IP_FROM_STRING

NET.SAFE_IP_FROM_STRING(addr_str)

Description

Similar toNET.IP_FROM_STRING, but returnsNULLinstead of throwing an error if the input is invalid.

Return Data Type

BYTES

Example

SELECTaddr_str,FORMAT("%T",NET.SAFE_IP_FROM_STRING(addr_str))ASsafe_ip_from_stringFROMUNNEST(['48.49.50.51','::1','3031:3233:3435:3637:3839:4041:4243:4445','::ffff:192.0.2.128','48.49.50.51/32','48.49.50','::wxyz'])ASaddr_str;/*---------------------------------------------------------------------------------------------------------------+ | addr_str                                | safe_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" | | 48.49.50.51/32                          | NULL                                                                | | 48.49.50                                | NULL                                                                | | ::wxyz                                  | NULL                                                                | +---------------------------------------------------------------------------------------------------------------*/

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-11-24 UTC.