This topic describes the syntax of string functions. This topic also provides examples on how to use the functions.
The following table describes the string functions that are supported by Log Service.
Function | Syntax | Description |
---|---|---|
chr function | chr(x) | Converts an ASCII code to characters. |
codepoint function | codepoint(x) | Converts characters to an ASCII code. |
concat function | concat(x, y...) | Concatenates multiple strings into one string. |
from_utf8 function | from_utf8(x) | Decodes a binary string into a UTF-8 encoded string. Invalid UTF-8 characters are replaced by the default replacement character U+FFFD. |
from_utf8(x, replace_string) | Decodes a binary string into a UTF-8 encoded string. Invalid UTF-8 characters are replaced by a custom string. | |
length function | length(x) | Returns the length of a string. |
levenshtein_distance function | levenshtein_distance(x, y) | Returns the minimum edit distance between x and y. |
lower function | lower(x) | Converts the characters in a string to lowercase letters. |
lpad function | lpad(x, length, lpad_string) | Left pads a string to a specified length by using a specified character and returns the result string. |
ltrim function | ltrim(x) | Removes spaces from the start of a string. |
normalize function | normalize(x) | Transforms a string by using the NFC normalization form. |
position function | position(sub_string in x) | Returns the position of a specified substring in a string. |
replace function | replace(x, sub_string ) | Removes the matched characters from a string. |
replace(x, sub_string, replace_string) | Replaces the matched characters in a string with specified characters. | |
reverse function | reverse(x) | Reverses the characters in a string. |
rpad function | rpad(x, length, rpad_string) | Right pads a string to a specified length by using a specified character and returns the result string. |
rtrim function | rtrim(x) | Removes spaces from the end of a string. |
split function | split(x, delimeter) | Splits a string by using a specified delimiter and returns a set of substrings. |
split(x, delimeter, limit) | Splits a string by using a specified delimiter and returns a set of substrings. The number of substrings that can be generated is specified by limit. | |
split_part function | split_part(x, delimeter, part) | Splits a string by using a specified delimiter and returns the substring at a specified position. |
split_to_map function | split_to_map(x, delimiter01, delimiter02) | Splits a string by using the first specified delimiter, and then splits the string by using the second specified delimiter. |
strpos function | strpos(x, sub_string) | Returns the position of a specified substring in a string. This function is equivalent to the position(sub_string in x) function. |
substr function | substr(x, start) | Returns the substring at a specified position in a string. |
substr(x, start, length) | Returns the substring at a specified position in a string. The length of the substring is specified. | |
to_utf8 function | to_utf8(x) | Converts a string to a UTF-8 encoded string. |
trim function | trim(x) | Removes spaces from the start and end of a string. |
upper function | upper(x) | Converts the characters in a string to uppercase letters. |
chr function
The chr function converts an ASCII code to characters.
Syntax
chr(x)
Parameters
Parameter | Description |
---|---|
x | The ASCII code. |
Return value type
The varchar type.
Examples
- Sample field
region:cn-shanghai
- Query statement
* | SELECT substr(region, 1, 1)=chr(99)
- Query and analysis result
codepoint function
The codepoint function converts characters to an ASCII code.
Syntax
codepoint(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The integer type.
Examples
- Sample field
upstream_status:200
- Query statement
* | SELECT codepoint(cast (substr(region, 1, 1) AS char(1))) =99
- Query and analysis result
concat function
The concat function concatenates multiple strings into one string.
Syntax
concat(x, y...)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
y | The value of this parameter is of the varchar type. |
Return value type
The varchar type.
Examples
- Sample field
region:cn-shanghai time_local:14/Jul/2021:02:19:40
- Query statement
* | SELECT concat(region,'-',time_local)
- Query and analysis result
from_utf8 function
The from_utf8 function decodes a binary string into a UTF-8 encoded string.
Syntax
- The following function replaces invalid UTF-8 characters with the default character
U+FFFD:
from_utf8(x)
- The following function replaces invalid UTF-8 characters with a custom string:
from_utf8(x,replace_string)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the binary type. |
replace_string | The value of this parameter is the custom string that you want to use. You can specify a single character or a space. |
Return value type
The varchar type.
Examples
- Decode the binary string 0x80 into a UTF-8 encoded string and replace invalid UTF-8
characters in the result with the default replacement character U+FFFD. U+FFFD is
displayed as �.
- Query statement
* | SELECT from_utf8(from_base64('0x80'))
- Query and analysis result
- Query statement
- Decode the binary string 0x80 into a UTF-8 encoded string and replace invalid UTF-8
characters in the result with 0.
- Query statement
* | SELECT from_utf8(from_base64('0x80'),'0')
- Query and analysis result
- Query statement
length function
The length function returns the length of a string.
Syntax
length(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The bigint type.
Examples
- Sample field
http_user_agent:Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.2 (KHTML, like Gecko) Chrome/22.0.1216.0 Safari/537.2
- Query statement
* | SELECT length(http_user_agent)
- Query and analysis result
levenshtein_distance function
The levenshtein_distance function returns the minimum edit distance between two strings.
Syntax
levenshtein_distance(x, y)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
y | The value of this parameter is of the varchar type. |
Return value type
The bigint type.
Examples
- Sample field
instance_id:i-01 owner_id:owner-01
- Query statement
* | SELECT levenshtein_distance(owner_id,instance_id)
- Query and analysis result
lower function
The lower function converts the characters in a string to lowercase letters.
Syntax
lower(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The varchar type.
Examples
- Sample field
request_method:GET
- Query statement
* | SELECT lower(request_method)
- Query and analysis result
lpad function
The lpad function left pads a string to a specified length by using a specified character and returns the result string.
Syntax
lpad(x, length, lpad_string)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
length | The value of this parameter is an integer that specifies the length of the result
string.
|
lpad_string | The character that you want to use to pad a string. |
Return value type
The varchar type.
Examples
- Sample field
instance_id:i-01
- Query statement
* | SELECT lpad(instance_id,10,'0')
- Query and analysis result
ltrim function
The ltrim function removes spaces from the start of a string.
Syntax
ltrim(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The varchar type.
Examples
- Sample field
region: cn-shanghai
- Query statement
* | SELECT ltrim(region)
- Query and analysis result
normalize function
The normalize function transforms a string by using the NFC normalization form.
Syntax
normalize(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The varchar type.
Examples
Transform the schön string by using the NFC normalization form.
- Query statement
* | SELECT normalize('schön')
- Query and analysis result
position function
The position function returns the position of a specified substring in a string.
Syntax
position(sub_string in x)
Parameters
Parameter | Description |
---|---|
sub_string | The substring whose position you want to query. |
x | The value of this parameter is of the varchar type. |
Return value type
The integer type. Valid values start from 1. If a string does not contain the specified substring, 0 is returned.
Examples
- Sample field
region:cn-shanghai
- Query statement
* | SELECT position('cn' in region)
- Query and analysis result
replace function
The replace function removes the matched characters from a string or replaces the matched characters in a string with specified characters.
Syntax
- The following function removes the matched characters from a string:
replace(x, sub_string)
- The following function replaces the matched characters in a string with specified
characters:
replace(x, sub_string, replace_string)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
sub_string | The substring that you want to match. |
replace_string | The substring that you want to use to replace the matched substring. |
Return value type
The varchar type.
Examples
- Example 1: Replace cn in the value of the region field with China.
- Sample field
region:cn-shanghai
- Query statement
* | select replace(region,'cn','China')
- Query and analysis result
- Sample field
- Example 2: Remove cn- from the value of the region field.
- Sample field
region:cn-shanghai
- Query statement
* | select replace(region,'cn-')
- Query and analysis result
- Sample field
reverse function
The reverse function reverses the characters in a string.
Syntax
reverse(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The varchar type.
Examples
- Sample field
request_method:GET
- Query statement
* | SELECT reverse(request_method)
- Query and analysis result
rpad function
The rpad function right pads a string to a specified length by using a specified character and returns the result string.
Syntax
rpad(x, length, rpad_string)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
length | The value of this parameter is an integer that specifies the length of the result
string.
|
lpad_string | The character that you want to use to pad a string. |
Return value type
The varchar type.
Examples
- Sample field
instance_id:i-01
- Query statement
* | SELECT rpad(instance_id,10,'0')
- Query and analysis result
rtrim function
The rtrim function removes spaces from the end of a string.
Syntax
rtrim(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The varchar type.
Examples
- Sample field
instance_id:i-01
- Query statement
* | SELECT rtrim(instance_id)
- Query and analysis result
split function
The split function splits a string by using a specified delimiter and returns a set of substrings.
Syntax
- The following function splits a string by using a specified delimiter and returns
a set of substrings:
split(x, delimeter)
- The following function splits a string by using a specified delimiter and returns
a set of substrings. The number of substrings that can be generated is specified by
limit.
split(x,delimeter,limit)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
delimeter | The delimiter. |
limit | The value of this parameter is a positive integer. The value specifies the number of substrings that can be generated. |
Return value type
The array type.
Examples
- Example 1: Use a forward slash (/) to split the value of the request_uri field and return a set of substrings.
- Sample field
request_uri:/request/path-1/file-9
- Query statement
* | SELECT split(request_uri,'/')
- Query and analysis result
- Sample field
- Example 2: Use a forward slash (/) to split the value of the request_uri field and return a set of substrings.
- Sample field
request_uri:/request/path-1/file-9
- Query statement
* | SELECT split(request_uri,'/',3)
- Query and analysis result
- Sample field
split_part function
The split_part function splits a string by using a specified delimiter and returns the substring at a specified position.
Syntax
split_part(x, delimeter, part)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
delimeter | The delimiter. |
part | The value of this parameter is a positive integer. |
Return value type
The varchar type.
Examples
- Query statement
* | SELECT count(*) AS PV, split_part(request_uri, '?', 1) AS Path GROUP BY Path ORDER BY pv DESC LIMIT 3
- Query and analysis result
split_to_map function
The split_to_map function splits a string by using the first specified delimiter, and then splits the string by using the second specified delimiter.
Syntax
split_to_map(x, delimiter01, delimiter02)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
delimeter01 | The delimiter. |
delimeter02 | The delimiter. |
Return value type
The map type.
Examples
- Sample field
time:upstream_response_time:"80", request_time:"40"
- Query statement
* | SELECT split_to_map(time,',',':')
- Query and analysis result
strpos function
The strpos function returns the position of a specified substring in a string. This function is equivalent to the position function.
Syntax
strpos(x, sub_string)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
sub_string | The substring whose position you want to query. |
Return value type
The integer type. Valid values start from 1. If a string does not contain the specified substring, 0 is returned.
Examples
- Query statement
* | SELECT strpos(server_protocol,'H')
- Query and analysis result
substr function
The substr function returns the substring at a specified position in a string.
Syntax
- The following function returns the substring at a specified position in a string:
substr(x, start)
- The following function returns the substring at a specified position in a string.
The length of the substring is specified.
substr(x,start,length)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
start | The start position from which you want to extract a substring. Valid values start from 1. |
length | The length of the substring. |
Return value type
The varchar type.
Examples
- Sample field
server_protocol:HTTP/2.0
- Query statement
* | SELECT substr(server_protocol,1,4) AS protocol, count(*) AS count GROUP BY server_protocol
- Query and analysis result
to_utf8 function
The to_utf8 function converts a string to a UTF-8 encoded string.
Syntax
to_utf8(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The varbinary type.
Examples
Convert the log string to a UTF-8 encoded string.
- Query statement
* | SELECT to_utf8('log')
- Query and analysis result
trim function
The trim function removes spaces from the start and the end of a string.
Syntax
trim(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The varchar type.
Examples
Removes spaces from the start and the end of the value of the instance_id field.
- Sample field
instance_id: i-01
- Query statement
* | SELECT trim(instance_id)
- Query and analysis result
upper function
The upper function converts the characters in a string to uppercase letters.
Syntax
upper(x)
Parameters
Parameter | Description |
---|---|
x | The value of this parameter is of the varchar type. |
Return value type
The varchar type.
Examples
- Sample field
region:cn-shanghai
- Query statement
* | SELECT upper(region)
- Query and analysis result