URL を操作するための関数

URLHierarchy(url)

SELECT URLHierarchy('https://example.com/a/b?c=1')

['https://example.com/','https://example.com/a/','https://example.com/a/b?','https://example.com/a/b?c=1']

URLPathHierarchy(url)

SELECT URLPathHierarchy('https://example.com/a/b?c=1')

['/a/','/a/b?','/a/b?c=1']

cutFragment(url)

SELECT cutFragment('http://example.com/path?query=value#fragment123');

┌─cutFragment('http://example.com/path?query=value#fragment123')─┐
│ http://example.com/path?query=value                            │
└────────────────────────────────────────────────────────────────┘

cutQueryString(url)

SELECT cutQueryString('http://example.com/path?query=value&param=123#fragment');

┌─cutQueryString('http://example.com/path?query=value&param=123#fragment')─┐
│ http://example.com/path#fragment                                         │
└──────────────────────────────────────────────────────────────────────────┘

cutQueryStringAndFragment(url)

SELECT cutQueryStringAndFragment('http://example.com/path?query=value&param=123#fragment');

┌─cutQueryStringAndFragment('http://example.com/path?query=value&param=123#fragment')─┐
│ http://example.com/path                                                             │
└─────────────────────────────────────────────────────────────────────────────────────┘

cutToFirstSignificantSubdomain(url)

SELECT
    cutToFirstSignificantSubdomain('https://news.clickhouse.com.tr/'),
    cutToFirstSignificantSubdomain('www.tr'),
    cutToFirstSignificantSubdomain('tr');

┌─cutToFirstSignificantSubdomain('https://news.clickhouse.com.tr/')─┬─cutToFirstSignificantSubdomain('www.tr')─┬─cutToFirstSignificantSubdomain('tr')─┐
│ clickhouse.com.tr                                                 │ tr                                       │                                      │
└───────────────────────────────────────────────────────────────────┴──────────────────────────────────────────┴──────────────────────────────────────┘

<!-- <top_level_domains_path>/var/lib/clickhouse/top_level_domains/</top_level_domains_path> -->
<top_level_domains_lists>
    <!-- https://publicsuffix.org/list/public_suffix_list.dat -->
    <public_suffix_list>public_suffix_list.dat</public_suffix_list>
    <!-- 注意: パスは top_level_domains_path の下にあります -->
</top_level_domains_lists>

cutToFirstSignificantSubdomainCustom(url, tld_list_name)

SELECT cutToFirstSignificantSubdomainCustom('bar.foo.there-is-no-such-domain', 'public_suffix_list')

foo.there-is-no-such-domain

<!-- <top_level_domains_path>/var/lib/clickhouse/top_level_domains/</top_level_domains_path> -->
<top_level_domains_lists>
    <!-- https://publicsuffix.org/list/public_suffix_list.dat -->
    <public_suffix_list>public_suffix_list.dat</public_suffix_list>
    <!-- NOTE: path is under top_level_domains_path -->
</top_level_domains_lists>

cutToFirstSignificantSubdomainCustomRFC(url, tld_list_name)

SELECT cutToFirstSignificantSubdomainCustomRFC('www.foo', 'public_suffix_list');

┌─cutToFirstSignificantSubdomainCustomRFC('www.foo', 'public_suffix_list')─┐
│ foo                                                                      │
└──────────────────────────────────────────────────────────────────────────┘

<!-- <top_level_domains_path>/var/lib/clickhouse/top_level_domains/</top_level_domains_path> -->
<top_level_domains_lists>
    <!-- https://publicsuffix.org/list/public_suffix_list.dat -->
    <public_suffix_list>public_suffix_list.dat</public_suffix_list>
    <!-- 注: パスは top_level_domains_path の配下です -->
</top_level_domains_lists>
    

**Syntax**

```sql
cutToFirstSignificantSubdomainCustomWithWWW(url, tld_list_name)

SELECT cutToFirstSignificantSubdomainCustomWithWWW('www.foo', 'public_suffix_list');

┌─cutToFirstSignificantSubdomainCustomWithWWW('www.foo', 'public_suffix_list')─┐
│ www.foo                                                                      │
└──────────────────────────────────────────────────────────────────────────────┘

{/* <top_level_domains_path>/var/lib/clickhouse/top_level_domains/</top_level_domains_path> */}
<top_level_domains_lists>
    {/* https://publicsuffix.org/list/public_suffix_list.dat */}
    <public_suffix_list>public_suffix_list.dat</public_suffix_list>
    {/* NOTE: path is under top_level_domains_path */}
</top_level_domains_lists>
    

**Syntax**

```sql
cutToFirstSignificantSubdomainCustomWithWWWRFC(url, tld_list_name)

SELECT cutToFirstSignificantSubdomainCustomWithWWWRFC('https://www.subdomain.example.custom', 'public_suffix_list')

example.custom

cutToFirstSignificantSubdomainRFC(url)

SELECT
    cutToFirstSignificantSubdomain('http://user:password@example.com:8080'),
    cutToFirstSignificantSubdomainRFC('http://user:password@example.com:8080');

┌─cutToFirstSignificantSubdomain('http://user:password@example.com:8080')─┬─cutToFirstSignificantSubdomainRFC('http://user:password@example.com:8080')─┐
│                                                                         │ example.com                                                                │
└─────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────┘

cutToFirstSignificantSubdomainWithWWW(url)

SELECT
    cutToFirstSignificantSubdomainWithWWW('https://news.clickhouse.com.tr/'),
    cutToFirstSignificantSubdomainWithWWW('www.tr'),
    cutToFirstSignificantSubdomainWithWWW('tr');

┌─cutToFirstSignificantSubdomainWithWWW('https://news.clickhouse.com.tr/')─┬─cutToFirstSignificantSubdomainWithWWW('www.tr')─┬─cutToFirstSignificantSubdomainWithWWW('tr')─┐
│ clickhouse.com.tr                                                        │ www.tr                                          │                                             │
└──────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────────┴─────────────────────────────────────────────┘

cutToFirstSignificantSubdomainWithWWWRFC(url)

SELECT
    cutToFirstSignificantSubdomainWithWWW('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy'),
    cutToFirstSignificantSubdomainWithWWWRFC('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy');

┌─cutToFirstSignificantSubdomainWithWWW('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy')─┬─cutToFirstSignificantSubdomainWithWWWRFC('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy')─┐
│                                                                                       │ mail.ru                                                                                  │
└───────────────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────┘

cutURLParameter(url, name)

SELECT
    cutURLParameter('http://bigmir.net/?a=b&c=d&e=f#g', 'a') AS url_without_a,
    cutURLParameter('http://bigmir.net/?a=b&c=d&e=f#g', ['c', 'e']) AS url_without_c_and_e;

┌─url_without_a────────────────┬─url_without_c_and_e───────┐
│ http://bigmir.net/?c=d&e=f#g │ http://bigmir.net/?a=b&#g │
└──────────────────────────────┴───────────────────────────┘

cutWWW(url)

SELECT cutWWW('http://www.example.com/path?query=value#fragment');

┌─cutWWW('http://www.example.com/path?query=value#fragment')─┐
│ http://example.com/path?query=value#fragment               │
└────────────────────────────────────────────────────────────┘

decodeURLComponent(url)

SELECT decodeURLComponent('http://127.0.0.1:8123/?query=SELECT%201%3B') AS DecodedURL;

┌─DecodedURL─────────────────────────────┐
│ http://127.0.0.1:8123/?query=SELECT 1; │
└────────────────────────────────────────┘

decodeURLFormComponent(url)

SELECT decodeURLFormComponent('http://127.0.0.1:8123/?query=SELECT%201+2%2B3') AS DecodedURL;

┌─DecodedURL────────────────────────────────┐
│ http://127.0.0.1:8123/?query=SELECT 1 2+3 │
└───────────────────────────────────────────┘

domain(url)

SELECT domain('svn+ssh://some.svn-hosting.com:80/repo/trunk');

┌─domain('svn+ssh://some.svn-hosting.com:80/repo/trunk')─┐
│ some.svn-hosting.com                                   │
└────────────────────────────────────────────────────────┘

domainRFC(url)

SELECT
    domain('http://user:password@example.com:8080/path?query=value#fragment'),
    domainRFC('http://user:password@example.com:8080/path?query=value#fragment');

┌─domain('http://user:password@example.com:8080/path?query=value#fragment')─┬─domainRFC('http://user:password@example.com:8080/path?query=value#fragment')─┐
│                                                                           │ example.com                                                                  │
└───────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────┘

domainWithoutWWW(url)

SELECT domainWithoutWWW('http://paul@www.example.com:80/');

┌─domainWithoutWWW('http://paul@www.example.com:80/')─┐
│ example.com                                         │
└─────────────────────────────────────────────────────┘

domainWithoutWWWRFC(url)

SELECT
    domainWithoutWWW('http://user:password@www.example.com:8080/path?query=value#fragment'),
    domainWithoutWWWRFC('http://user:password@www.example.com:8080/path?query=value#fragment');

┌─domainWithoutWWW('http://user:password@www.example.com:8080/path?query=value#fragment')─┬─domainWithoutWWWRFC('http://user:password@www.example.com:8080/path?query=value#fragment')─┐
│                                                                                         │ example.com                                                                                │
└─────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────┘

encodeURLComponent(url)

SELECT encodeURLComponent('http://127.0.0.1:8123/?query=SELECT 1;') AS EncodedURL;

┌─EncodedURL───────────────────────────────────────────────┐
│ http%3A%2F%2F127.0.0.1%3A8123%2F%3Fquery%3DSELECT%201%3B │
└──────────────────────────────────────────────────────────┘

encodeURLFormComponent(url)

SELECT encodeURLFormComponent('http://127.0.0.1:8123/?query=SELECT 1 2+3') AS EncodedURL;

┌─EncodedURL────────────────────────────────────────────────┐
│ http%3A%2F%2F127.0.0.1%3A8123%2F%3Fquery%3DSELECT+1+2%2B3 │
└───────────────────────────────────────────────────────────┘

extractURLParameter(url, name)

SELECT extractURLParameter('http://example.com/?param1=value1&param2=value2', 'param1');

┌─extractURLParameter('http://example.com/?param1=value1&param2=value2', 'param1')─┐
│ value1                                                                           │
└──────────────────────────────────────────────────────────────────────────────────┘

extractURLParameterNames(url)

SELECT extractURLParameterNames('http://example.com/?param1=value1&param2=value2');

┌─extractURLParameterNames('http://example.com/?param1=value1&param2=value2')─┐
│ ['param1','param2']                                                         │
└─────────────────────────────────────────────────────────────────────────────┘

extractURLParameters(url)

SELECT extractURLParameters('http://example.com/?param1=value1&param2=value2');

┌─extractURLParameters('http://example.com/?param1=value1&param2=value2')─┐
│ ['param1=value1','param2=value2']                                       │
└─────────────────────────────────────────────────────────────────────────┘

firstSignificantSubdomain(url)

SELECT firstSignificantSubdomain('https://news.clickhouse.com/')

┌─firstSignificantSubdomain('https://news.clickhouse.com/')─┐
│ clickhouse                                                │
└───────────────────────────────────────────────────────────┘

firstSignificantSubdomainCustom(url, tld_list_name)

SELECT firstSignificantSubdomainCustom('https://news.example.com', 'public_suffix_list')

example

firstSignificantSubdomainCustomRFC(url, tld_list_name)

SELECT firstSignificantSubdomainCustomRFC('https://news.example.com', 'public_suffix_list')

example

firstSignificantSubdomainRFC(url)

fragment(url)

SELECT fragment('https://clickhouse.com/docs/get-started/setup/cloud#1-create-a-clickhouse-service');

┌─fragment('https://clickhouse.com/docs/get-started/setup/cloud#1-create-a-clickhouse-service')─┐
│ 1-create-a-clickhouse-service                                                                 │
└───────────────────────────────────────────────────────────────────────────────────────────────┘

netloc(url)

SELECT netloc('http://paul@www.example.com:80/');

┌─netloc('http://paul@www.example.com:80/')─┐
│ paul@www.example.com:80                   │
└───────────────────────────────────────────┘

path(url)

SELECT path('https://clickhouse.com/docs/reference/functions/regular-functions/url-functions?query=value');

┌─path('https://clickhouse.com/docs/reference/functions/regular-functions/url-functions?query=value')─┐
│ /docs/reference/functions/regular-functions/url-functions                                           │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘

pathFull(url)

SELECT pathFull('https://clickhouse.com/docs/reference/functions/regular-functions/url-functions?query=value#section');

┌─pathFull('https://clickhouse.com/docs/reference/functions/regular-functions/url-functions?query=value#section')─┐
│ /docs/reference/functions/regular-functions/url-functions?query=value#section                                   │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

port(url[, default_port])

SELECT port('https://clickhouse.com:8443/docs'), port('https://clickhouse.com/docs', 443);

┌─port('https://clickhouse.com:8443/docs')─┬─port('https://clickhouse.com/docs', 443)─┐
│                                     8443 │                                      443 │
└──────────────────────────────────────────┴──────────────────────────────────────────┘

portRFC(url[, default_port])

SELECT port('http://user:password@example.com:8080/'), portRFC('http://user:password@example.com:8080/');

┌─port('http://user:password@example.com:8080/')─┬─portRFC('http://user:password@example.com:8080/')─┐
│                                              0 │                                              8080 │
└────────────────────────────────────────────────┴───────────────────────────────────────────────────┘

protocol(url)

SELECT protocol('https://clickhouse.com/');

┌─protocol('https://clickhouse.com/')─┐
│ https                               │
└─────────────────────────────────────┘

queryString(url)

SELECT queryString('https://clickhouse.com/docs?query=value&param=123#section');

┌─queryString('https://clickhouse.com/docs?query=value&param=123#section')─┐
│ query=value&param=123                                                    │
└──────────────────────────────────────────────────────────────────────────┘

queryStringAndFragment(url)

SELECT queryStringAndFragment('https://clickhouse.com/docs?query=value&param=123#section');

┌─queryStringAndFragment('https://clickhouse.com/docs?query=value&param=123#section')─┐
│ query=value&param=123#section                                                       │
└─────────────────────────────────────────────────────────────────────────────────────┘

topLevelDomain(url)

SELECT topLevelDomain('svn+ssh://www.some.svn-hosting.com:80/repo/trunk');

┌─topLevelDomain('svn+ssh://www.some.svn-hosting.com:80/repo/trunk')─┐
│ com                                                                │
└────────────────────────────────────────────────────────────────────┘

topLevelDomainRFC(url)

SELECT topLevelDomain('http://foo:foo%41bar@foo.com'), topLevelDomainRFC('http://foo:foo%41bar@foo.com');

┌─topLevelDomain('http://foo:foo%41bar@foo.com')─┬─topLevelDomainRFC('http://foo:foo%41bar@foo.com')─┐
│                                                │ com                                               │
└────────────────────────────────────────────────┴───────────────────────────────────────────────────┘