string functions - Simple Log Service - Alibaba Cloud Documentation Center

Syntax and examples for string functions.

Simple Log Service supports the following string functions.

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 name	Syntax	Description	SQL supported	SPL supported
chr function	chr(x)	Converts an ASCII code to a character.	√	√
codepoint function	codepoint(x)	Converts a character to an ASCII code.	√	√
concat function	concat(x, y...)	Concatenates multiple strings into a single string.	√	√
from_utf8 function	from_utf8(x)	Decodes a binary string into the UTF-8 encoding format and replaces invalid UTF-8 characters with the default character U+FFFD.	√	√
from_utf8 function	from_utf8(x, replace_string)	Decodes a binary string into the UTF-8 encoding format and replaces invalid UTF-8 characters with a custom string.	√	√
length function	length(x)	Calculates the length of a string.	√	√
levenshtein_distance function	levenshtein_distance(x, y)	Calculates the minimum edit distance between x and y.	√	×
lower function	lower(x)	Converts a string to lowercase.	√	√
lpad function	lpad(x, length, lpad_string)	Pads the beginning of a string with a specified character to a specified length and returns the result string.	√	√
ltrim function	ltrim(x)	Removes the spaces from the beginning of a string.	√	√
normalize function	normalize(x)	Formats a string in the NFC format.	√	×
position function	position(sub_string in x)	Returns the position of a substring in a string.	√	×
replace function	replace(x, sub_string )	Deletes 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)	Pads the end of a string with a specified character to a specified length and returns the result string.	√	√
rtrim function	rtrim(x)	Removes the spaces from the end of a string.	√	√
split function	split(x, delimeter)	Splits a string using a specified separator and returns a collection of substrings.	√	√
split function	split(x, delimeter, limit)	Splits a string using a specified separator, limits the number of splits using limit, and then returns a collection of the split substrings.	√	√
split_part function	split_part(x, delimeter, part)	Splits a string using a specified separator and returns the content at a specified position.	√	√
split_to_map function	split_to_map(x, delimiter01, delimiter02)	Splits a string using a specified first separator and then splits the string again using a specified second separator.	√	√
strpos function	strpos(x, sub_string)	Returns the position of a substring in a string. This function is equivalent to the position(sub_string in x) function.	√	√
substr function	substr(x, start)	Returns a substring from a specified position in a string.	√	√
substr function	substr(x, start, length)	Returns a substring of a specified length from a specified position in a string.	√	√
to_utf8 function	to_utf8(x)	Converts a string to the UTF-8 encoding format.	√	√
trim function	trim(x)	Removes the spaces from the beginning and end of a string.	√	√
upper function	upper(x)	Converts a string to uppercase.	√	√
csv_extract_map function	csv_extract_map(x, delimeter, quote, keys)	Extracts single-line CSV information from a target string.	√	×
ilike function	ilike(x, pattern)	Checks whether a string matches a specified character pattern. The check is case-insensitive.	√	√
str_uuid function	str_uuid()	Generates a random 128-bit ID and returns it in a string format.	×	√
gzip_compress function	gzip_compress(data, compression_level)	Accepts a string object, compresses it using the GZIP algorithm, and returns the compressed binary stream.	×	√
gzip_decompress function	gzip_decompress(binary_data)	Accepts GZIP-compressed binary data (Varbinary) and decompresses it.	×	√
search function	search(search_expression)	Performs a full-text search on log data within an SQL analytic statement. It supports Boolean operations, field-specific searches, fuzzy queries, and range queries.	√	×

chr function

The chr function converts an ASCII code to a character.

Syntax

chr(x)

Parameters

Parameter	Description
x	The ASCII code.

Return value type

varchar.

Example

The following query checks whether the value of the region field starts with c. The ASCII code 99 represents the lowercase letter c.

Sample field
```
region:cn-shanghai
```

Query statement (Test)

* | SELECT
  substr(region, 1, 1) = chr(99)

Query and analysis results

codepoint function

The codepoint function converts a character to an ASCII code.

Syntax

codepoint(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

integer.

Example

The following query checks whether the value of the region field starts with c. The ASCII code 99 represents the lowercase letter c.

Sample field
```
upstream_status:200
```

Query statement (Test)

* | SELECT
  codepoint(cast (substr(region, 1, 1) AS char(1))) = 99

Query and analysis results

concat function

The concat function concatenates multiple strings into a single string.

Syntax

concat(x, y...)

Parameters

Parameter	Description
x	The value is of the varchar type.
y	The value is of the varchar type.

Return value type

VARCHAR data type.

Example

The following query concatenates the values of the region field and the request_method field.

Sample fields

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

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

from_utf8 function

The from_utf8 function decodes a binary string into the UTF-8 encoding format.

Syntax

Replaces invalid UTF-8 characters with the default character U+FFFD.
```
from_utf8(x)
```
Replaces invalid UTF-8 characters with a custom character.
```
from_utf8(x,replace_string)
```

Parameters

Parameter	Description
x	The value is of the binary type.
replace_string	The string that is used for replacement. The string can be only a single character or a space.

Return value type

VARCHAR data type.

Examples

The following query decodes the binary string 0x80 into the UTF-8 encoding format and replaces invalid UTF-8 characters in the returned result with the default character U+FFFD. U+FFFD is displayed as the default replacement character.
- Query statement (Test)
```
* | SELECT
  from_utf8(from_base64('0x80'))
```
- Query and analysis results
The following query decodes the binary string 0x80 into the UTF-8 encoding format and replaces invalid UTF-8 characters in the returned result with 0.
- Query statement (Test)
```
* | SELECT
  from_utf8(from_base64('0x80'), '0')
```
- Query and analysis results

length function

The length function calculates the length of a string.

Syntax

length(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

bigint.

Example

The following query calculates 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 (Test)
```
* | SELECT
  length(http_user_agent)
```
Query and analysis results

levenshtein_distance function

The levenshtein_distance function calculates the minimum edit distance between two strings.

Syntax

levenshtein_distance(x, y)

Parameters

Parameter	Description
x	The value is of the varchar type.
y	The value is of the varchar type.

Return value type

bigint.

Example

The following query calculates the minimum edit distance between the value of the instance_id field and the value of the owner_id field.

Sample fields
```
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 a string to lowercase.

Syntax

lower(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

VARCHAR type.

Example

The following query converts the value of the request_method field to lowercase.

Sample field
```
request_method:GET
```
Query statement (Test)
```
* | SELECT
  lower(request_method)
```
Query and analysis results

lpad function

The lpad function pads the beginning of a target string with a specified character to a specified length.

Syntax

lpad(x, length, lpad_string)

Parameters

Parameter	Description
x	The value is of the varchar type.
length	An integer that specifies the length of the result string. If the length of the string is less than length, the beginning of the string is padded with the specified character. If the length of the string is greater than length, only the first length characters of the string are returned.
lpad_string	New padding characters are now available.

Return value type

varchar.

Example

The following query pads the beginning of the value of the instance_id field with 0 to a total length of 10 characters.

Sample field
```
instance_id:i-01
```
Query statement (Test)
```
* | SELECT
  lpad(instance_id, 10, '0')
```
Query and analysis results

ltrim function

The ltrim function removes leading spaces from a string.

Syntax

ltrim(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

varchar type.

Example

The following query removes the leading spaces from the value of the region field.

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

normalize function

The normalize function formats a string in the Normalization Form C (NFC) format.

Syntax

normalize(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

varchar.

Example

The following query formats the string schön in the NFC format.

Query statement (Test)
```
* | SELECT
  normalize('schön')
```
Query and analysis results

position function

The position function returns the position of a target substring in a string.

Syntax

position(sub_string in x)

Parameters

Parameter	Description
sub_string	The target substring.
x	The value is of the varchar type.

Return value type

int. The value is 1-based. If the target substring is not found, the function returns 0.

Example

The following query finds the position of the substring cn in the value of the region field.

Sample field
```
region:cn-shanghai
```
Query statement (Test)
```
* | SELECT
  position('cn' in region)
```
Query and analysis results

replace function

The replace function deletes characters from a string or replaces them with other characters.

Syntax

Delete matching characters from a string.
```
replace(x, sub_string)
```
Replaces all occurrences of a substring with another string.
```
replace(x, sub_string, replace_string)
```

Parameters

Parameter	Description
x	The value is of the varchar type.
sub_string	The target substring.
replace_string	The substring that is used for replacement.

Return value type

varchar.

Examples

Example 1: The following query replaces cn in the value of the region field with China.
- Sample field
```
region:cn-shanghai
```
- Sample query (Debug)
```
* | select
  replace(region, 'cn', 'China')
```
- Query and analysis results
Example 2: The following query removes cn- from the value of the region field.
- Sample field
```
region:cn-shanghai
```
- Query statement (Test)
```
* | 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 is of the varchar type.

Return value type

VARCHAR data type.

Example

Sort the request_method field value in descending order.

Sample field
```
request_method:GET
```
Query statement (Test)
```
* | SELECT
  reverse(request_method)
```
Query and analysis results

rpad function

The rpad function pads the end of a string with a specified character to a specified length.

Syntax

rpad(x, length, rpad_string)

Parameters

Parameter	Description
x	The value is of the varchar type.
length	An integer that specifies the length of the result string. If the length of the string is less than length, the end of the string is padded with the specified character. If the length of the string is greater than length, only the first length characters of the string are returned.
rpad_string	The new character for padding.

Return value type

The varchar data type.

Example

The following query pads the end of the value of the instance_id field with 0 to a total length of 10 characters.

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 trailing spaces from a string.

Syntax

rtrim(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

varchar.

Example

The following query removes the trailing spaces from 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 using a specified separator and returns an array of the resulting substrings.

Syntax

Splits a string using a specified separator.
```
split(x, delimeter)
```
Splits a string using a specified separator into a specified number of substrings.
```
split(x,delimeter,limit)
```

Parameters

Parameter	Description
x	The value is of the varchar type.
delimeter	The separator.
limit	The number of splits. The value must be an integer greater than 0.

Return value type

The data type is an array.

Examples

Example 1: The following query splits the value of the request_uri field into four substrings using a forward slash (/) as the separator and returns the resulting array.
- Sample field
```
request_uri:/request/path-1/file-9
```
- Query statement (Test)
```
* | SELECT
  split(request_uri, '/')
```
- Query and analysis results
Example 2: The following query splits the value of the request_uri field into three substrings using a forward slash (/) as the separator and returns the resulting array.
- Sample field
```
request_uri:/request/path-1/file-9
```
- Query statement (Test)
```
* | SELECT
  split(request_uri, '/', 3)
```
- Query and analysis results

split_part function

The split_part function splits a string using a specified separator and returns the substring at a specified position.

Syntax

split_part(x, delimeter, part)

Parameters

Parameter	Description
x	The value is of the varchar type.
delimeter	The separator.
part	An integer greater than 0.

Return value type

The VARCHAR data type.

Example

The following query splits the value of the request_uri field using a question mark (?) and returns the first substring, which is the file path. The query then counts the number of requests for each path.

Sample fields

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

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

Analysis results

split_to_map function

The split_to_map function splits a string using the first specified separator and then splits the result using the second specified separator.

Syntax

split_to_map(x, delimiter01, delimiter02)

Parameters

Parameter	Description
x	The value is of the varchar type.
delimeter01	The separator.
delimeter02	The separator.

Return value type

Map type

Example

The following query splits the value of the time field using a comma (,) and a colon (:). The result is a map.

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 target substring in a string. This function is equivalent to the position function.

Syntax

strpos(x, sub_string)

Parameters

Parameter	Description
x	The value is of the varchar type.
sub_string	The target substring.

Return value type

int. The value is 1-based. If the target substring is not found, the function returns 0.

Example

The following query returns the position of the letter H in the value of the server_protocol field.

Query statement (Test)

* | SELECT
  strpos(server_protocol, 'H')

Query and analysis results

substr function

The substr function returns a substring from a specified position in a string.

Syntax

Returns a substring from a specified position in a string.
```
substr(x, start)
```
Returns a substring of a specified length from a specified starting position.
```
substr(x,start,length)
```

Parameters

Parameter	Description
x	The value is of the varchar type.
start	The position from which the substring starts to be extracted. The value starts from 1.
length	The length of the substring.

Return value type

varchar.

Example

The following query extracts the first four characters (the HTTP part) from the value of the server_protocol field. The query then counts the number of requests that use the HTTP protocol.

Sample field
```
server_protocol:HTTP/2.0
```

Query statement (Test)

* | 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 encodes a string into a UTF-8 binary representation.

Syntax

to_utf8(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

The varbinary data type.

Example

Convert the log string to the UTF-8 encoding format.

Query statement (Test)
```
* | SELECT
  to_utf8('log')
```
Query and analysis results

trim function

The trim function removes leading and trailing spaces from a string.

Syntax

trim(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

VARCHAR type

Example

The following query removes the leading and trailing spaces from the value of the instance_id field.

Sample field
```
instance_id: i-01 
```
Query statement (Test)
```
* | SELECT
  trim(instance_id)
```
Query and analysis results

upper function

The upper function converts a target string to uppercase.

Syntax

upper(x)

Parameters

Parameter	Description
x	The value is of the varchar type.

Return value type

varchar.

Example

The following query converts the value of the region field to uppercase.

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

csv_extract_map function

The csv_extract_map function extracts single-line CSV information from a target string.

Syntax

csv_extract_map(x, delimeter, quote, keys)

Parameters

Parameter	Description
x	The value is of the varchar type.
delimeter	The CSV separator. The value is of the varchar type and the length is 1.
quote	The CSV quote. The value is of the varchar type and the length is 1.
keys	The key name for the output of the CSV information. The value is of the array type. If the number of elements is different from the number of pieces of CSV information in the data, null is returned.

Return value type

map(varchar, varchar).

Example

The following query extracts the CSV information from the content field.

Sample field

content: '192.168.0.100,"10/Jun/2019:11:32:16,127 +0800",example.aliyundoc.com'

Query statement

select csv_extract_map(content, ',', '"', array['ip', 'time', 'host']) as item

Output data

ilike function

The ilike function checks whether an input string matches a specified character pattern. The check is case-insensitive.

Syntax

ilike(x, pattern)

Parameters

Parameter

Description

The value is of the varchar type.

pattern

The character pattern, which includes strings and wildcard characters. The following table describes the wildcard characters.

The percent sign (%) represents any number of characters.
The underscore (_) represents a single character.

Return value type

Boolean type

Example

The following query checks whether request_uri ends with file-6.

Sample field

request_uri: '/request/path-2/File-6'

Query statement

select ilike(request_uri, '%file-6')

Output data

str_uuid function

The str_uuid() function generates a random 128-bit ID and returns it as a string.

Syntax

str_uuid()

Return value

Return value type: VARCHAR
Format: A standard 36-character string containing 32 hexadecimal digits and four hyphens -.
Structure example: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

Example

This example shows how to quickly generate many unique identifiers in a staging environment.

* | extend uuid = str_uuid()

gzip_compress function

The gzip_compress function accepts a string object, compresses it using the GZIP algorithm, and returns the compressed binary data.

Syntax

-- Method 1: Default compression level (6)
gzip_compress(data)

-- Method 2: Specify compression level
gzip_compress(data, compression_level)

Parameters

Parameter	Type	Description
data	VARCHAR	The string content to compress.
compression_level	BIGINT	Compression level. The value is an integer from 1 to 9.

Return value

Return value type: VARBINARY
Description: The compressed binary data.

Examples

Example 1: Basic compression

* | extend compress_data =  gzip_compress('Hello World')

Example 2: Maximum compression ratio (for large text)

If you have a log entry that is tens of thousands of characters long and storage space is a concern, use level 9:
```
* | extend compress_data =  gzip_compress('Hello World',9)
```

gzip_decompress function

The gzip_decompress function accepts GZIP-compressed binary data (Varbinary) and decompresses it.

Syntax

gzip_decompress(binary_data)

Parameters

binary_data must be valid GZIP-compressed data, which is usually generated by gzip_compress. If the input is not in the standard GZIP format, the function returns NULL.

Return value

Return value type: VARCHAR
Description: The original plaintext content after decompression.

Example

Simple compression and decompression pipeline:

* | extend original_content =  gzip_decompress(gzip_compress('Hello SLS!'))
-- Output: "Hello SLS!"

search function

The search function performs a full-text search on log data in an SQL analytic statement. The search function is provided as a standard SQL function and supports complex query conditions, including Boolean operations, field-specific searches, fuzzy queries, and range queries.

Limits

Limit	Description
Single instance limit	Only one `search()` function can be used in each subquery (underlying SELECT). If you need multiple query conditions, combine them into a single `search()` call, such as `search('error AND timeout')`.
OR operator limit	The `search()` function cannot be used with the OR operator at the SQL layer. Use OR inside the search function, such as `search('error OR warning')`.
You have created an index and are not in scan mode.	The search function is not supported in scan mode.
Query syntax input conflict	You cannot use the search function when the query syntax input contains actual filter conditions. The function is allowed when the query syntax input is empty or is `*`.
Parameter type	The parameter of the search function must be a string literal. Dynamic values such as column references, variables, or function expressions are not supported.
Number of parameters	The function must accept exactly one parameter.

Syntax

search(search_expression)

The usage format in a query statement is as follows:

* | SELECT ... FROM log WHERE search('search_expression')

Important: The search function can be used only in the WHERE clause.

Parameters

Parameter	Description
search_expression	A string literal that represents the query expression. `search_expression` must fully comply with the Simple Log Service query syntax and provides the same features as the query syntax, including full-text search, field-specific search, Boolean operations, fuzzy queries, and range queries.

Return value type

BOOLEAN. true indicates that the current row matches the query condition. false indicates that the current row does not match the query condition.

Examples

Example 1: The following query uses the search function to find logs that contain "error" and "timeout".
```
* | SELECT * FROM log WHERE search('error AND timeout')
```
Example 2: The following query uses the search function to perform a field-specific search for logs where the status field is 200.
```
* | SELECT * FROM log WHERE search('status: 200')
```
Example 3: The following query combines the search function with an SQL predicate using AND to find logs where the status is 200 and the request_time is greater than 100.
```
* | SELECT * FROM log
    WHERE search('status: 200') AND request_time > 100
```
For more information, see Use the search function to perform a full-text search.