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.

Notice If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.
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

Check whether the first letter in the value of the region field starts with the letter c. In the following query statement, the value 99 is an ASCII code and represents the lowercase letter c.
  • Sample field
    region:cn-shanghai
  • Query statement
    * | SELECT substr(region, 1, 1)=chr(99)
  • Query and analysis resultschr

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

Check whether the first letter in the value of the region field starts with the letter c. In the following query statement, the value 99 is an ASCII code and represents the lowercase letter c.
  • Sample field
    upstream_status:200
  • Query statement
    * | SELECT codepoint(cast (substr(region, 1, 1) AS char(1))) =99
  • Query and analysis resultscodepoint

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

Concatenate the values of the region and request_method fields into one string.
  • Sample field
    region:cn-shanghai
time_local:14/Jul/2021:02:19:40
  • Query statement
    * | SELECT concat(region,'-',time_local)
  • Query and analysis resultsconcat function

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 resultsfrom_utf8
  • 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 resultsfrom_utf8

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

Calculate the length of the value of the http_user_agent field.
  • 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 resultslength function

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

Query the minimum edit distance between the value of the instance_id field and the value of the owner_id field.
  • Sample field
    instance_id:i-01
owner_id:owner-01
  • Query statement
    * | SELECT levenshtein_distance(owner_id,instance_id)
  • Query and analysis resultslevenshtein_distance

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

Convert the characters in the value of the request_method field to lowercase letters.
  • Sample field
    request_method:GET
  • Query statement
    * | SELECT lower(request_method)
  • Query and analysis resultslower function

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.
  • If the length of a string is less than the value of the length parameter, the string is padded by using the specified character from the start of the string.
  • If the length of a string is greater than the value of the length parameter, only the n characters in the string are returned. n is specified by length.
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

Pad the value of the instance_id field to 10 characters. If the value length is less than 10 characters, use 0 to pad the value from the start of the value.
  • Sample field
    instance_id:i-01
  • Query statement
    * | SELECT lpad(instance_id,10,'0')
  • Query and analysis resultslpad

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

Remove spaces from the start of the value of the region field.
  • Sample field
    region: cn-shanghai
  • Query statement
    * | SELECT ltrim(region)
  • Query and analysis resultsltrim

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 resultsnormalize

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

Query the position of the cn substring in the value of the region field.
  • Sample field
    region:cn-shanghai
  • Query statement
    * | SELECT position('cn' in region)
  • Query and analysis resultsposition function

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 resultsreplace
  • 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 resultsreplace

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

Reverse the characters in the value of the request_method field.
  • Sample field
    request_method:GET
  • Query statement
    * | SELECT reverse(request_method)
  • Query and analysis resultsreverse

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.
  • If the length of a string is less than the value of the length parameter, the string is padded by using the specified character from the end of the string.
  • If the length of a string is greater than the value of the length parameter, only the n characters in the string are returned. n is specified by length.
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

Pad the value of the instance_id field to 10 characters. If the value length is less than 10 characters, use 0 to pad the value from the end of the value.
  • Sample field
    instance_id:i-01
  • Query statement
    * | SELECT rpad(instance_id,10,'0')
  • Query and analysis resultsrpad

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

Remove spaces from the end of the value of the instance_id field.
  • Sample field
    instance_id:i-01
  • Query statement
    * | SELECT rtrim(instance_id)
  • Query and analysis resultsrtrim

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 resultssplit
  • 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 resultssplit

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

Use a question mark (?) to split the value of the request_uri field and obtain the first substring, which is a file path. Then, measure the number of requests that correspond to each path.
  • 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 resultsTop 3 file paths that are most frequently requested

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

Use a comma (,) and a colon (:) to split the value of the time field to obtain a value of the map type.
  • Sample field
    time:upstream_response_time:"80", request_time:"40"
  • Query statement
    * | SELECT split_to_map(time,',',':')
  • Query and analysis resultssplit_to_map

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 the position of the letter H in the value of the server_protocol field.
  • Query statement
    * | SELECT strpos(server_protocol,'H')
  • Query and analysis resultsstrpos

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

Extract the first four characters (HTTP) from the value of the server_protocol field. Then, measure the number of requests that use the HTTP protocol.
  • 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 resultssubstr

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 resultsto_utf8

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 resultsrtrim

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

Convert the characters in the value of the region field to uppercase letters.
  • Sample field
    region:cn-shanghai
  • Query statement
    * | SELECT upper(region)
  • Query and analysis resultsupper function