String functions - Simple Log Service - Alibaba Cloud Documentation Center

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 Simple 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	Supported in SQL	Supported in SPL
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 function	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 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 function	replace(x, sub_string, replace_string)	Replaces the matched characters in a string with specified characters.	√	√
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 function	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 function	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 (Debug)

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

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 (Debug)

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

Concatenate the values of the region and request_method fields into one string.

Sample field

region:cn-shanghai
time:14/Jul/2021:02:19:40

Query statement (Debug)
```
* | SELECT
  concat(region, '-', time)
```
Query and analysis results

from_utf8 function

The from_utf8 function decodes a binary string into a UTF-8 encoded string.

Syntax

If you use the following syntax, the function replaces invalid UTF-8 sequences with the default replacement character U+FFFD.
```
from_utf8(x)
```
If you use the following syntax, the function replaces invalid UTF-8 sequences 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 (Debug)
```
* | SELECT
  from_utf8(from_base64('0x80'))
```
- Query and analysis results
Decode the binary string 0x80 into a UTF-8 encoded string and replace invalid UTF-8 sequences in the result with 0.
- Query statement (Debug)
```
* | SELECT
  from_utf8(from_base64('0x80'), '0')
```
- Query and analysis results

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 (Debug)
```
* | 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

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 (Debug)

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

Convert the characters in the value of the request_method field to lowercase letters.

Sample field
```
request_method:GET
```
Query statement (Debug)
```
* | 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. 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 (Debug)
```
* | 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

Remove spaces from the start of the value of the region field.

Sample field
```
region: cn-shanghai
```
Query statement (Debug)
```
* | 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 (Debug)
```
* | 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. 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 (Debug)
```
* | 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

If you use the following syntax, the function removes the matched characters from a string.
```
replace(x, sub_string)
```
If you use the following syntax, the 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 (Debug)
```
* | select
  replace(region, 'cn', 'China')
```
- Query and analysis results
Example 2: Remove cn- from the value of the region field.
- Sample field
```
region:cn-shanghai
```
- Query statement (Debug)
```
* | select
  replace(region, 'cn-')
```
- Query and analysis results

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 (Debug)
```
* | 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. 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.
rpad_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 (Debug)
```
* | 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

Remove spaces from the end of the value of the instance_id field.

Sample field
```
instance_id:i-01 
```
Query statement (Debug)
```
* | 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

If you use the following syntax, the function splits a string by using a specified delimiter and returns a set of substrings.
```
split(x, delimeter)
```
If you use the following syntax, the 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 an integer greater than 0. 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 (Debug)
```
* | SELECT
  split(request_uri, '/')
```
- Query and analysis results
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 (Debug)
```
* | SELECT
  split(request_uri, '/', 3)
```
- Query and analysis results

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 an integer greater than 0.

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.

Sample field

request_uri: /request/path-2/file-6?name=value&age=18
request_uri: /request/path-2/file-0?name=value&age=18
request_uri: /request/path-3/file-2?name=value&age=18

Query statement (Debug)

* | SELECT
  count(*) AS PV,
  split_part(request_uri, '?', 1) AS Path
GROUP BY
  Path
ORDER BY
  pv DESC

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

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 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. 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 (Debug)

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

If you use the following syntax, the function returns the substring at a specified position in a string.
```
substr(x, start)
```
If you use the following syntax, the 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 (Debug)

* | 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.

Example

Convert the log string to a UTF-8 representation.

Query statement (Debug)
```
* | 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

Removes spaces from the start and end of the value of the instance_id field.

Sample field
```
instance_id: i-01 
```
Query statement (Debug)
```
* | 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

Convert the characters in the value of the region field to uppercase letters.

Sample field
```
region:cn-shanghai
```
Query statement (Debug)
```
* | SELECT
  upper(region)
```
Query and analysis results