Use URL functions in AnalyticDB for MySQL - AnalyticDB - Alibaba Cloud Documentation Center

This topic describes the syntax of URL functions and provides examples on how to use them.

PARSE_URL: returns the specified content from a URL.
URL_DECODE: decodes a URL string encoded based on the application/x-www-form-urlencoded standard and returns the result.
URL_ENCODE: encodes a URL string based on the application/x-www-form-urlencoded standard and returns the result.
URL_EXTRACT_FRAGMENT: returns the fragment identifier from a URL.
URL_EXTRACT_HOST: returns the hostname or IP address from a URL.
URL_EXTRACT_PARAMETER: returns the specified parameter value from the query part of a URL.
URL_EXTRACT_PATH: returns the path information from a URL.
URL_EXTRACT_PORT: returns the port information from a URL.
URL_EXTRACT_PROTOCOL: returns the protocol information from a URL.
URL_EXTRACT_QUERY: returns the query information from a URL.

PARSE_URL

PARSE_URL(url, part [,key] )

Description: This function returns the content specified by part from url.
Rules:
- If part is set to QUERY, this function returns the value corresponding to key.
- If the value of url, part, or key is NULL, this function returns NULL.
- If part is set to an invalid value, this function returns NULL.
The value of part is case-insensitive. The following values are supported:
- HOST: the hostname or IP address in the URL.
- PATH: the path information in the URL. Example: /path/to/resource.
- QUERY: the query part in the URL, which starts with ?.
- REF: the fragment identifier in the URL, which starts with #.
- PROTOCOL: the protocol information in the URL. Example: http or https.
- AUTHORITY: the user information, hostname, and port number in the URL.
- FILE: the file path of the URL.
- USERINFO: the user information in the URL, including the username and password.
Data type of the input value: STRING for url, part, and key.
Data type of the return value: STRING.

Examples:

Return the hostname or IP address from url. Sample statement:

SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'HOST');

Sample result:

example.com

Return the path from url. Sample statement:

SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'PATH');

Sample result:

/over/there/index.dtb

Return the query part from url. Sample statement:

SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'QUERY', 'type');

Sample result:

animal

Return the fragment identifier from url. Sample statement:

SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'REF');

Sample result:

nose

Return the protocol from url. Sample statement:

SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'PROTOCOL');

Sample result:

file

Return the user information, hostname, and port number from url. Sample statement:

SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'AUTHORITY');

Sample result:

username:password@example.com:8042

Return the user information from url. Sample statement:

SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'USERINFO');

Sample result:

username:password

URL_DECODE

URL_DECODE(input)

Description: This function decodes a URL string encoded based on the application/x-www-form-urlencoded standard and returns the result.
Rules: If the value of input is NULL, this function returns NULL.
Encoding rules:
- The characters a to z, A to Z, and 0 to 9 are not changed.
- Periods (.), hyphens (-), asterisks (*), and underscores (_) are retained.
- Plus signs (+) are converted into spaces.
- Percent-encoded sequences (%xy) are converted into their corresponding byte values, and the resulting byte stream is decoded as a UTF-8-encoded string.
Data type of the input value: STRING.
Data type of the return value: STRING.
Example:
Decode a URL string encoded based on the application/x-www-form-urlencoded standard and return the result. Sample statement:
```
SELECT url_decode('example+for+url_decode%3A%2F%2F+%28fdsf%29');
```
Sample result:
```
example for url_decode:// (fdsf)
```

URL_ENCODE

URL_ENCODE(input)

Description: This function encodes a URL string based on the application/x-www-form-urlencoded standard and returns the result.
Rules: If the value of input is NULL, this function returns NULL.
Encoding rules:
- The characters a to z, A to Z, and 0 to 9 are not changed.
- Periods (.), hyphens (-), asterisks (*), and underscores (_) are retained.
- Plus signs (+) are converted into spaces.
- Other characters are encoded as byte values by using UTF-8. Each byte value is in the %xy format, where xy is the corresponding hexadecimal representation.
Data type of the input value: STRING.
Data type of the return value: STRING.
Example:
Encode the example for url_encode:// (fdsf) string based on the application/x-www-form-urlencoded standard and return the result.
```
SELECT url_encode('example for url_encode:// (fdsf)');
```
Sample result:
```
example+for+url_encode%3A%2F%2F+%28fdsf%29
```

URL_EXTRACT_FRAGMENT

URL_EXTRACT_FRAGMENT(url)

Description: This function returns the fragment identifier that starts with # from url.
Rules:
- If the value of url is NULL or invalid, this function returns NULL.
- If url does not contain a fragment identifier, this function returns an empty string.
Data type of the input value: STRING.
Data type of the return value: STRING.

Example:

Return the fragment identifier from url. Sample statement:

SELECT url_extract_fragment('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');

Sample result:

Ref1

URL_EXTRACT_HOST

URL_EXTRACT_HOST(url)

Description: This function returns the hostname or IP address from url.
Rules: If the value of url is NULL or invalid, this function returns NULL.
Data type of the input value: STRING.
Data type of the return value: STRING.

Example:

Return the hostname or IP address from url. Sample statement:

SELECT url_extract_host('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');

Sample result:

example.com

URL_EXTRACT_PARAMETER

URL_EXTRACT_PARAMETER(url, parameter)

Description: This function returns the information specified by parameter from the query part of url.
Rules: If the value of url is NULL, the value of parameter is NULL, or the query part of url does not contain parameter, this function returns NULL.
Data type of the input value: STRING for url and parameter.
Data type of the return value: STRING.
Examples:
- Return the information specified by k1 from the query part of url. Sample statement:
```
SELECT url_extract_parameter('http://example.com/path1/p.php?k1=v1&k2=v2&k3&k4#Ref1', 'k1');
```
  Sample result:
```
v1
```
- Return the information specified by k5 from the query part of url. Sample statement:
```
SELECT url_extract_parameter('http://example.com/path1/p.php?k1=v1&k2=v2&k3&k4#Ref1', 'k5');
```
  Sample result:
```
NULL
```

URL_EXTRACT_PATH

URL_EXTRACT_PATH(url)

Description: This function returns the path information from url.
Rules:
- If the value of url is NULL or invalid, this function returns NULL.
- If url does not contain a path, this function returns an empty string.
Data type of the input value: STRING.
Data type of the return value: STRING.

Example:

Return the path information from url. Sample statement:

SELECT url_extract_path('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');

Sample result:

/path1/p.php

URL_EXTRACT_PORT

URL_EXTRACT_PORT(url)

Description: This function returns the port information from url.
Rules: If the value of url is NULL or invalid, or url does not contain a port, this function returns NULL.
Data type of the input value: STRING.
Data type of the return value: STRING.

Examples:

Return the port information from url. Sample statement:

SELECT url_extract_port('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');

Sample result:

NULL

Return the port information from url. Sample statement:

SELECT url_extract_port('http://example.com:8080/path1/p.php?k1=v1&k2=v2#Ref1');

Sample result:

URL_EXTRACT_PROTOCOL

URL_EXTRACT_PROTOCOL(url)

Description: This function returns the protocol information from url.
Rules: If the value of url is NULL or invalid, this function returns NULL.
Data type of the input value: STRING.
Data type of the return value: STRING.

Example:

Return the protocol information from url. Sample statement:

SELECT url_extract_protocol('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');

Sample result:

http

URL_EXTRACT_QUERY

URL_EXTRACT_QUERY(url)

Description: This function returns the query information from url.
Rules:
- If the value of url is NULL or invalid, this function returns NULL.
- If url does not contain query information, this function returns an empty string.
Data type of the input value: STRING.
Data type of the return value: STRING.

Example:

Return the query information from url. Sample statement:

SELECT url_extract_query('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');

Sample result:

k1=v1&k2=v2