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.

Important 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 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

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

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 c. The value 99 is an ASCII code that 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 resultcodepoint

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 a string.
  • Sample field
    region:cn-shanghai
time_local:14/Jul/2021:02:19:40
  • Query statement
    * | SELECT concat(region,'-',time_local)
  • Query and analysis resultconcat function

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 resultfrom_utf8
  • 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 resultfrom_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 resultlength 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 resultlevenshtein_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 resultlower 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 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 bits in length. If the value length is less than 10 bits, pad the value with 0 from the start of the value.
  • Sample field
    instance_id:i-01
  • Query statement
    * | SELECT lpad(instance_id,10,'0')
  • Query and analysis resultlpad

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 resultltrim

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 resultnormalize

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

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 resultposition 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 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 resultreplace
  • 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 resultreplace

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

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 resultreverse

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 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 bits in length. If the value length is less than 10 bits, pad the value with 0 from the end of the value.
  • Sample field
    instance_id:i-01
  • Query statement
    * | SELECT rpad(instance_id,10,'0')
  • Query and analysis resultrpad

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 resultrtrim

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

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

Use a question mark (?) to split the value of the request_uri field and return the first substring. The returned substring indicates a file path. Then, calculate 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 resultTop three most accessed file paths

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

Use commas (,) and colons (:) to split the value of the time field and return 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 resultsplit_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 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 the position of the letter H in the value of the server_protocol field.
  • Query statement
    * | SELECT strpos(server_protocol,'H')
  • Query and analysis resultstrpos

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

Extract the first four characters (HTTP) from the value of the server_protocol field and calculate 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 resultsubstr

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 resultto_utf8

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 resultrtrim

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 resultupper function