The NET_REG_DOMAIN function parses the registrable domain name from a URL string. A registrable domain name is a public suffix plus the label that precedes it.
Usage notes
The public suffix data on publicsuffix.org also contains private domains. This function does not treat private domains as public suffixes.
For example, if private.com is a private domain in the public suffix data,
NET_REG_DOMAIN("test.private.com")returnsprivate.com(the public suffix com plus the preceding label private), nottest.private.com(the private domain private.com plus the preceding label `test`).
Syntax
STRING NET_REG_DOMAIN(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 are met:
The hostname cannot be parsed from the input.
The parsed hostname contains contiguous dots in the middle, not at the beginning or end.
The parsed hostname does not contain a public suffix.
The parsed hostname contains only a public suffix with no preceding label.
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 parts of a URL other than the domain name, use the following functions:
NET_HOST: Parses the hostname from a URL.
NET_PUBLIC_SUFFIX: Parses the public suffix, such as com, org, or net, from a URL.
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_REG_DOMAIN is a network function. For more network functions, see Network functions.