The NET_PUBLIC_SUFFIX function parses the public suffix, such as com, org, or net, from a URL string.
Notes
Public suffixes are ICANN domains registered at publicsuffix.org. The public suffix data on publicsuffix.org also includes private domains. This function ignores private domains.
Syntax
STRING NET_PUBLIC_SUFFIX(STRING <url>)Parameters
url: Required. The URL string to parse. This parameter must be of the STRING type.
For best results, the URL string must conform to the format defined in RFC 3986.
Return value
Returns a value of the STRING type. The function returns NULL if any of the following conditions apply:
The hostname cannot be parsed from the URL string.
The parsed hostname contains contiguous dots that are not at the beginning or end.
The parsed hostname does not contain any public suffix.
Public suffix data may change over time. Therefore, an input that returns a NULL value now might return a non-NULL value in the future.
Related parsing functions
To parse other parts of a URL besides the public suffix, use the following functions:
NET_HOST: Parses the hostname from a URL.
NET_REG_DOMAIN: Parses the registered or registrable domain from a URL (the public suffix plus the preceding label).
Examples
SELECT input
,description
,NET_HOST(input) AS HOST
,NET_PUBLIC_SUFFIX(input) AS SUFFIX
,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://example.web.china", "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://example.web.china | standard URL with internationalized domain name | example.web.china | china | web.china |
| 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 |
+-----------------------------------+-------------------------------------------------------------------------------+-------------------+------------+---------------+Related functions
NET_PUBLIC_SUFFIX is a network function. For more information about network functions, see Network functions.