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.
length function length(x) Calculates 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 characters.
lpad function lpad(x,length,lpad_string) Pads a string to a specified length from the start with a specified character and returns the result string.
ltrim function ltrim(x) Deletes space characters from the start of a string.
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 matched characters in a string with specified characters.
replace(x,sub_string) Deletes matched characters from a string.
reverse function reverse(x) Reverses the characters in a string.
rpad function rpad(x,length,rpad_string) Pads a string to a specified length from the end with a specified character and returns the result string.
rtrim function rtrim(x) Deletes space characters from the end of a string.
split function split(x,delimeter,n) Splits a string with a specified delimiter and returns an array of n substrings.
split_part function split_part(x,delimeter,part) Splits a string with a specified delimiter and returns the substring at a specified position.
split_to_map function split_to_map(x,delimiter01,delimiter02) Splits a string with the first specified delimiter, and then splits the string with 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 key) 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 and specifies the length of the substring.
trim function trim(x) Deletes space characters from the start and the end of a string.
upper function upper(x) Converts the characters in a string to uppercase characters.

chr function

The chr function is used to convert 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 is used to convert 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 is used to concatenate 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

length function

The length function is used to calculate 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 is used to return 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 is used to convert the characters in a string to lowercase characters.

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 characters.
  • Sample field
    request_method:GET
  • Query statement
    * | SELECT lower(request_method)
  • Query and analysis resultlower function

lpad function

The lpad function is used to pad a string to a specified length from the start with a specified character and return 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 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 the specified character from the start.
  • If the length of a string is greater than the value of the length parameter, only the first length characters in the string are returned.
lpad_string The character with which you want to pad the string.

Return value type

The varchar type.

Examples

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

ltrim function

The ltrim function is used to delete space characters 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

Delete space characters from the start of the value of the region field.
  • Sample field
    region: cn-shanghai
  • Query statement
    * | SELECT ltrim(region)
  • Query and analysis resultltrim

position function

The position function is used to query 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 value of this parameter is of the integer type. The value starts 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 resultposition function

replace function

The replace function is used to replace matched characters in a string with specified characters.

Syntax

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 replace.
replace_string The substring that you want to use to replace the specified substring.

Return value type

The varchar type.

Examples

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

reverse function

The reverse function is used to reverse 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 is used to pad a string to a specified length from the end with a specified character and return 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 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 the specified character from the end.
  • If the length of a string is greater than the value of the length parameter, only the first length characters in the string are returned.
lpad_string The character with which you want to pad the string.

Return value type

The varchar type.

Examples

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

rtrim function

The rtrim function is used to delete space characters 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

Delete space characters 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 is used to split a string with a specified delimiter and return an array of n substrings.

Syntax

split(x,delimeter,n)

Parameters

Parameter Description
x The value of this parameter is of the varchar type.
delimeter The delimiter.
n The integer that is greater than 0.

Return value type

The array type.

Examples

Use a forward slash (/) to split the value of the server_protocol field into two substrings and return an array of the substrings.
  • Sample field
    server_protocol:HTTP/2.0
  • Query statement
    * | SELECT split(server_protocol,'/',2)
  • Query and analysis resultsplit

split_part function

The split_part function is used to split a string with a specified delimiter and return 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 integer that is greater than 0.

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 is used to split a string with the first specified delimiter, and then split the string with 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 is used to return 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 value of this parameter is of the integer type. The value starts from 1.

Examples

Return 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 is used to return the substring at a specified position in a string.

Syntax

  • To return the substring at a specified position in a string, use the following syntax:
    substr(x,start)
  • To return the substring at a specified position in a string and specify the length of the substring, use the following syntax:
    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. The value of this parameter starts 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

trim function

The trim function is used to delete space characters 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

Delete space characters 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 is used to convert the characters in a string to uppercase characters.

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 characters.
  • Sample field
    region:cn-shanghai
  • Query statement
    * | SELECT upper(region)
  • Query and analysis resultupper function