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 sequences are replaced with the default replacement character U+FFFD. |
from_utf8(x,replace_string) | Decodes a binary string into a UTF-8 encoded string. Invalid UTF-8 sequences are replaced with 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 space 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,replace_string) | Replaces the matched characters in a string with specified characters. |
replace(x,sub_string) | Removes the matched characters from a string. | |
reverse function | reverse(x) | Returns a string in reverse order. |
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 representation. |
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 value of this parameter is an 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 results
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 results
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 results
from_utf8 function
The from_utf8 function decodes a binary string into a UTF-8 encoded string.
Syntax
- Invalid UTF-8 sequences are replaced with the default replacement character U+FFFD.
from_utf8(x)
- Invalid UTF-8 sequences are replaced 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
sequences 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 results
- Query statement
- Decode the binary string 0x80 into a UTF-8 encoded string and replace invalid UTF-8
sequences in the result with 0.
- Query statement
* | SELECT from_utf8(from_base64('0x80'),'0')
- Query and analysis results
- 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 results
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 results
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 results
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 value of this parameter is 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 results
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 results
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 results
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 value of this parameter is 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.
Examples
- Sample field
region:cn-shanghai
- Query statement
* | SELECT position('cn' in region)
- Query and analysis results
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 value of this parameter is the substring that you want to match. |
replace_string | The value of this parameter is 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 results
- 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 results
- Sample field
reverse function
The reverse function returns a string in reverse order.
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 results
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 value of this parameter is 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 results
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 results
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 value of this parameter is the delimiter that you want to use. |
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 into four substrings and obtain a set of the substrings.
- Sample field
request_uri:/request/path-1/file-9
- Query statement
* | SELECT split(request_uri,'/')
- Query and analysis results
- Sample field
- Example 2: Use a forward slash (/) to split the value of the request_uri field into three substrings and obtain a set of the substrings.
- Sample field
request_uri:/request/path-1/file-9
- Query statement
* | SELECT split(request_uri,'/',3)
- Query and analysis results
- 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 value of this parameter is the delimiter that you want to use. |
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 results
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 value of this parameter is the delimiter that you want to use. |
delimeter02 | The value of this parameter is the delimiter that you want to use. |
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 results
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 value of this parameter is the substring whose position you want to query. |
Return value type
The integer type. Valid values start from 1.
Examples
- Query statement
* | SELECT strpos(server_protocol,'H')
- Query and analysis results
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 value of this parameter is the start position from which you want to extract a substring. Valid values start from 1. |
length | The value of this parameter is 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 results
to_utf8 function
The to_utf8 function converts a string to a UTF-8 representation.
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 representation.
- Query statement
* | SELECT to_utf8('log')
- Query and analysis results
trim function
The trim function removes spaces from the start and 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
Remove spaces from the start and end of the value of the instance_id field.
- Sample field
instance_id: i-01
- Query statement
* | SELECT trim(instance_id)
- Query and analysis results
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 results