Functions supported by advanced search - Resource Management

This topic describes the functions that you can use in advanced search.

to_char(timestamp/timestamptz, text)

Return value type: TEXT.
Description: Converts a timestamp into a string. By default, the time range is from 1925 to 2282.
Format
- The first parameter specifies the timestamp to be converted, and the second parameter specifies the conversion format.
- YYYY indicates the year, MM indicates the month, DD indicates the day, HH indicates the hour, MI indicates the minute, and SS indicates the second.
- You can use the to_char function to convert a timestamp into a string in the 24-hour clock or 12-hour clock. HH24 indicates the 24-hour clock, and HH12 indicates the 12-hour clock. The 12-hour clock is used by default.
Examples
Scenario
Request
Response
Convert the current timestamp into a string in the 24-hour clock.
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');
20221208

to_timestamp(text, text)

Return value type: TIMESTAMPTZ.
Description: Converts a string into a timestamp. By default, the time range is from 1925 to 2282.

Format: The first parameter specifies the string to be converted, and the second parameter specifies the conversion format.

Examples

Scenario	Request	Response
Convert a field value of the TEXT type into a date.	`SELECT TO_TIMESTAMP(properties ->> 'ExpireTime', 'YYYY-MM-DD') FROM resources WHERE resource_type = 'ACS::RDS::DBInstance';`	2001-09-28

clock_timestamp()

Return value type: TIMESTAMPTZ.
Description: Returns the current time.
Examples
Scenario
Request
Response
Query the current time.
SELECT CLOCK_TIMESTAMP();
2023-09-02T11:47:19Z

concat(param1 [,param2]...)

Return value type: TEXT.
Description: Concatenates all values except NULL.

Format: The parameters specify the values that you want to concatenate. The values can be of any data type. The first parameter is required, and other parameters are optional.

Examples
Scenario
Request
Response
Concatenate all values except NULL.
SELECT CONCAT('abcde', 2, NULL, 22);
concat
--------
abcde222

concat_ws(separator, param1 [,param2]...)

Return value type: TEXT.
Description: Uses a delimiter to concatenate all values except the value specified by the first parameter and NULL.

Format: The first parameter specifies a delimiter. Other parameters specify the values that you want to concatenate. The values can be of any data type. The first and second parameters are required, and other parameters are optional.

Examples
Scenario
Request
Response
Use a delimiter to concatenate all values except the value specified by the first parameter and NULL.
SELECT concat_ws(',', 'abcde', 2, NULL, 22);
concat_ws
--------
abcde,2,22

substring()

Return value type: TEXT.
Description: Extracts a substring from a string.
Format: substring(string[FROM start][FOR length]) or substring(string FROM pattern).
- string: required. The string.
- start: optional. The start position of the substring. The default value is 1, which indicates the beginning of the string.
- length: optional. The length of the substring. If you do not specify this parameter, the substring from the start position to the end of the string is extracted.
- pattern: required. A regular expression. Portable Operating System Interface (POSIX) regular expressions are used in the FROM pattern clause, and SQL regular expressions are used in the SIMILAR pattern clause.

Examples

Scenario

Request

Response

Extract a specified substring from a string.

SELECT SUBSTRING('Thomas' FROM 2 FOR 3);

substring

-------

hom

Extract a substring that matches a POSIX regular expression from a string.

SELECT SUBSTRING('Thomas' FROM '...$');

substring

--------

mas

char_length(string)

Return value type: INT.
Description: Returns the length of a string. The length is calculated based on the number of characters.
Examples
Scenario
Request
Response
Query the length of a string.
SELECT CHAR_LENGTH('jose');
4

length(string)

Return value type: INT.
Description: Returns the length of a string. The length is calculated based on the number of bytes. If the UTF-8 character set is used to encode the string, a Chinese character occupies three bytes, and a digit or a letter occupies one byte.
Examples
Scenario
Request
Response
Query the length of a string.
SELECT LENGTH('jose');
4

lower(string)

Return value type: TEXT.
Description: Converts a string into lowercase letters.
Examples
Scenario
Request
Response
Convert a string into lowercase letters.
SELECT LOWER('TOM');
tom

upper(string)

Return value type: TEXT.
Description: Converts a string into uppercase letters.
Examples
Scenario
Request
Response
Convert a string into uppercase letters.
SELECT UPPER('tom');
TOM

starts_with(string, string)

Return value type: TEXT.
Description: Checks whether a string starts with a specific prefix. The first parameter specifies the string to be checked, and the second parameter specifies the prefix. If the string starts with the prefix, the starts_with() function returns true. Otherwise, the function returns false.
Examples
Scenario
Request
Response
Check whether the string alphabet starts with alph.
SELECT starts_with('alphabet', 'alph');
true

case

Description: Goes through conditions until a condition is determined to be true and then returns a value.

Examples

Scenario

Request

Response

Convert the status of an Elastic Compute Service (ECS) instance.

SELECT
 resource_id,
 CASE
 properties ->> 'Status'
 WHEN 'ECS Pending' THEN 'Creating'
 WHEN 'ECS Running' THEN 'Running'
 WHEN 'ECS Starting' THEN 'Starting'
 WHEN 'ECS Stopping' THEN 'Stopping'
 WHEN 'ECS Stopped' THEN 'Stopped'
 END AS "Status",
FROM
 resources
WHERE
 resource_type = 'ACS::ECS::Instance'
ORDER BY
 resource_id;

resource_id Status
------------+-----------
1 i-xxx				Running

count

Return value type: INT.
Description: Calculates the number of rows in a table that meet the specified conditions.
Examples
Scenario
Request
Response
Calculate the number of Alibaba Cloud resources.
SELECT count(*) FROM resources;
1123

max

Description: Calculates the maximum value of a numeric expression.
Examples
Scenario
Request
Response
Query the latest creation time of resources.
SELECT MAX(create_time) FROM resources;
2023-09-13T07:32:37Z

min

Description: Calculates the minimum value of a numeric expression.
Examples
Scenario
Request
Response
Query the earliest creation time of resources.
SELECT MIN(create_time) FROM resources;
2021-08-20T02:20:37Z

jsonb_array_elements(jsonb)

Description: Expands a JSONB array to a set of JSONB values.

Examples

Scenario	Request	Response
Query all IP addresses.	`SELECT DISTINCT JSONB_ARRAY_ELEMENTS(ip_addresses) AS ip_address FROM resources`	`ip_address ----------- "172.X.X.1" "172.X.X.2" "172.X.X.3"`

jsonb_array_elements_text(jsonb)

Description: Expands a JSONB array to a set of TEXT values.

Examples

Scenario	Request	Response
Query all IP addresses.	`SELECT DISTINCT JSONB_ARRAY_ELEMENTS_TEXT(ip_addresses) AS ip_address FROM resources`	`ip_address ----------- 172.X.X.1 172.X.X.2 172.X.X.3`

jsonb_object_keys(jsonb)

Description: Returns a set of keys in the outermost JSONB object.

Examples

Scenario	Request	Response
Query all tag keys.	`SELECT DISTINCT JSONB_OBJECT_KEYS(tags) AS tag_key FROM resources`	`tag_key ------------------ key_1 key_2`

Scenario	Request	Response
Convert the current timestamp into a string in the 24-hour clock.	`SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');`	20221208

Scenario	Request	Response
Check whether the string alphabet starts with alph.	`SELECT starts_with('alphabet', 'alph');`	true

Scenario	Request	Response
Calculate the number of Alibaba Cloud resources.	`SELECT count(*) FROM resources;`	`1123`