String functions - Simple Log Service - Alibaba Cloud Documentation Center

This topic explains the basic syntax and examples of string functions.

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	Syntax	Description	SQL	SPL
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.	√	√
from_utf8 function	from_utf8(x)	Decodes a binary string as UTF-8 text, replacing any invalid characters with the default replacement character (U+FFFD).	√	√
from_utf8 function	from_utf8(x, replace_string)	Decodes a binary string as UTF-8 text, replacing any invalid characters with a custom string.	√	√
length function	length(x)	Returns 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 string to a specified length.	√	√
ltrim function	ltrim(x)	Removes leading whitespace from a string.	√	√
normalize function	normalize(x)	Normalizes a string to the NFC form.	√	×
position function	position(sub_string in x)	Returns the starting position of the first occurrence of a substring within another string.	√	×
replace function	replace(x, sub_string )	Removes all occurrences of a specified substring from a string.	√	√
replace function	replace(x, sub_string, replace_string)	Replaces all occurrences of a specified substring with a replacement string.	√	√
reverse function	reverse(x)	Reverses a string.	√	√
rpad function	rpad(x, length, rpad_string)	Pads the end of a string with a specified string to a specified length.	√	√
rtrim function	rtrim(x)	Removes trailing whitespace from a string.	√	√
split function	split(x, delimiter)	Splits a string by a delimiter and returns an array of substrings.	√	√
split function	split(x, delimiter, limit)	Splits a string by a specified separator, uses the limit parameter to restrict the number of splits, and returns an array of the resulting substrings.	√	√
split_part function	split_part(x, delimiter, part)	Splits a string by a delimiter and returns the substring at a specified position.	√	√
split_to_map function	split_to_map(x, delimiter01, delimiter02)	Splits a string into a map of key-value pairs using two delimiters.	√	√
strpos function	strpos(x, sub_string)	This function is an alias for position(sub_string in x).	√	√
substr function	substr(x, start)	Extracts a substring from a string, starting at a specified position.	√	√
substr function	substr(x, start, length)	Extracts a substring of a specified length from a string, starting at a specified position.	√	√
to_utf8 function	to_utf8(x)	Encodes a string into a UTF-8 binary string.	√	√
trim function	trim(x)	Removes leading and trailing whitespace from a string.	√	√
upper function	upper(x)	Converts a string to uppercase.	√	√
csv_extract_map function	csv_extract_map(x, delimiter, quote, keys)	Extracts specified values from a line of CSV text into a map.	√	×
ilike function	ilike(x, pattern)	Checks if a string matches a pattern, case-insensitively.	√	√
str_uuid function	str_uuid()	Generates a random 128-bit UUID and returns it as a string.	×	√
gzip_compress function	gzip_compress(data, compression_level)	Compresses a string into a binary stream using the GZIP algorithm.	×	√
gzip_decompress function	gzip_decompress(binary_data)	Decompresses a GZIP-compressed binary stream, returning the original string.	×	√
search function	search(search_expression)	Performs a full-text search on log data within an SQL analytic statement. 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 type

varchar.

Example

Determines whether the first letter of the value of the region field is 'c', where 99 is the ASCII code for the lowercase letter 'c'.

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

Query and analysis statement (Test)

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

Query and analysis results: The return value for the _col0 column is true, which indicates that the first character of the region field matches chr(99) (the character c).

Codepoint function

The codepoint function converts a character to an ASCII code.

Syntax

codepoint(x)

Parameters

Parameter	Description
x	The character to convert, which must be of the `varchar` type.

Return value type

integer.

Example

Determine if the first letter of the value of the region field is 'c', where 99 is the ASCII code for 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: The _col0 column returns two rows of data, and the value for both rows is true.

Concat function

Concatenates multiple strings into a single string.

Syntax

concat(x, y...)

Parameters

Parameter	Description
x	The value must be a varchar.
y	The value must be a varchar.

Return value type

The returned value is a varchar.

Example

Concatenate the values of the region and request_method fields into a single string.

Sample fields

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

Query and analysis statement (Test)
```
* | SELECT
  concat(region, '-', time)
```
In the query and analysis results, the _col0 column returns a concatenated string, such as cn-shanghai-14/Jul/2021:01:16:30.

from_utf8 function

Decodes a binary string into the UTF-8 encoding format.

Syntax

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

Parameters

Parameter	Description
x	The binary string to decode.
replace_string	An optional single character or space used to replace invalid UTF-8 characters.

Return value type

varchar

Examples

Decodes the binary string 0x80 and replaces invalid UTF-8 characters with the default replacement character (U+FFFD).
- Query and analysis statement (Debug)
```
* | SELECT
  from_utf8(from_base64('0x80'))
```
- Query and analysis results: The query returns a single column _col0. The value is a string that contains the replacement character (U+FFFD), which means that the invalid UTF-8 bytes have been replaced by the default replacement character.
Decodes the binary string 0x80 and replaces invalid UTF-8 characters with '0'.
- Query and analysis statement (Debug)
```
* | SELECT
  from_utf8(from_base64('0x80'), '0')
```
- The query and analysis results return a single column named _col0 with the value 0□4. The square box represents a byte that cannot be rendered correctly, which is the output from the from_utf8 function after it replaces an invalid UTF-8 byte.

Length function

Returns the length of a string.

Syntax

length(x)

Parameters

Parameter	Description
x	A varchar value.

Return value

bigint

Example

Calculate the length of the http_user_agent field value.

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)
```
The query and analysis result is 127, which represents the string length of the value for the http_user_agent field.

levenshtein_distance

Calculates the minimum edit distance between two strings.

Syntax

levenshtein_distance(x, y)

Parameters

Parameter	Description
x	A value of type varchar.
y	A value of type varchar.

Return value

bigint.

Example

Query the minimum edit distance between the values of the instance_id and owner_id fields.

Sample fields
```
instance_id:i-01
owner_id:owner-01
```

Query and analysis statement (debug)

* | SELECT
  levenshtein_distance(owner_id, instance_id)

The query and analysis result is 5.

Lower function

Converts a string to lowercase.

Syntax

lower(x)

Parameters

Parameter	Description
x	A varchar string.

Return type

varchar

Example

Convert the value of the request_method field to lowercase.

Sample field
```
request_method:GET
```
Query and analysis statement (Debug)
```
* | SELECT
  lower(request_method)
```
The query and analysis results are get.

lpad function

The lpad function left-pads a string with specified characters to a specified length.

Syntax

lpad(x, length, lpad_string)

Parameters

Parameter	Description
x	The value must be of the varchar type.
length	An integer that specifies the desired length of the resulting string. If the original string is shorter than length, the function pads it on the left. If the original string is longer than length, the function truncates it to length characters.
lpad_string	The character or string to use for padding.

Return type

varchar

Example

Pad the instance_id field value with leading zeros to a length of 10 digits.

Sample field
```
instance_id:i-01
```
Query statement (Test)
```
* | SELECT
  lpad(instance_id, 10, '0')
```
In the query and analysis results, the return value of the _col0 column is 000000i-01.

ltrim function

Removes leading spaces from a string.

Syntax

ltrim(x)

Parameters

Parameter	Description
x	The input string. It must be of type varchar.

Return value type

varchar

Example

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

Sample field
```
region: cn-shanghai
```
Query and analysis statement (Test)
```
* | SELECT
  ltrim(region)
```
The result is cn-shanghai.

Normalize function

Normalizes a string using the Normalization Form C (NFC) format.

Syntax

normalize(x)

Parameters

Parameter	Description
x	The varchar string to normalize.

Return value type

varchar.

Example

This example normalizes the string schön using the NFC format.

Query statement (debug)
```
* | SELECT
  normalize('schön')
```
The query and analysis results return one row and one column, where the column name is _col0 and the value is schön.

Position function

Returns the starting position of a substring within a string.

Syntax

position(sub_string in x)

Parameters

Parameter	Description
sub_string	The substring to search for.
x	The string to search, which must be a varchar type.

Return value type

Returns an integer that represents the 1-based starting position of the substring. If the substring is not found, the function returns 0.

Example

Find the position of the substring cn in the region field.

Sample field
```
region:cn-shanghai
```
Query and analysis statement (Test)
```
* | SELECT
  position('cn' in region)
```
Query and analysis results: The _col0 column returns 1, which indicates that the substring cn starts at position 1 in the string cn-shanghai.

Replace function

The replace function removes or replaces all occurrences of a specified substring in a string.

Syntax

Removes all occurrences of a substring 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 source string. The data type must be varchar.
sub_string	The substring to be removed or replaced.
replace_string	The replacement string.

Return value type

varchar

Examples

Example 1: In the region field value, replace cn with China.
- Sample field
```
region:cn-shanghai
```
- Query and analysis statement (Debug)
```
* | select
  replace(region, 'cn', '中国')
```
- In the query and analysis results, the value of the _col0 column is China-shanghai.
Example 2: Remove cn- from the value of the region field.
- Sample field
```
region:cn-shanghai
```
- Query and analysis statement (Debug)
```
* | select
  replace(region, 'cn-')
```
- The query and analysis result is shanghai.

Reverse function

The reverse function returns a string with its characters in reverse order.

Syntax

reverse(x)

Parameters

Parameter	Description
x	The input string, which must be of type varchar.

Return value type

varchar

Example

Reverse the value of the request_method field.

Sample field
```
request_method:GET
```
Query statement (Test)
```
* | SELECT
  reverse(request_method)
```
The result is TEG.

rpad function

Pads a string on the right with specified characters to a specified length.

Syntax

rpad(x, length, rpad_string)

Parameters

Parameter	Description
x	The source string, which must be of the varchar type.
length	The desired length of the resulting string, specified as an integer. If the original string is shorter than length, the function pads it on the right. If the original string is longer than length, the function truncates it to length characters.
rpad_string	The character or string to use for padding.

Return value type

varchar.

Example

The following example pads the instance_id field value with trailing zeros to a length of 10.

Sample field
```
instance_id:i-01
```
Query statement (debug)
```
* | SELECT
  rpad(instance_id, 10, '0')
```
Query and analysis results: The _col0 column returns the value i-01000000.

rtrim function

Removes trailing spaces from a string.

Syntax

rtrim(x)

Parameters

Parameter	Description
x	The source string, which must be a varchar.

Return type

varchar

Example

This query removes 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: The return value of the _col0 column is i-01.

split function

The split function splits a string into an array of substrings using a separator.

Syntax

Splits a string by a separator.
```
split(x, delimiter)
```
Splits a string by a separator into a maximum number of substrings.
```
split(x,delimiter,limit)
```

Parameters

Parameter	Description
x	The varchar string to split.
delimiter	The delimiter used to split the string.
limit	The maximum number of substrings to return. The value must be a positive integer.

Return value type

array

Examples

Example 1: Splits the value of the request_uri field into four substrings using a forward slash (/) and returns an array of these substrings.
- Sample field
```
request_uri:/request/path-1/file-9
```
- Query and analysis statement (Test)
```
* | SELECT
  split(request_uri, '/')
```
- Query and analysis results: The query returns ["" ,"request","path-1","file-9"], which is the array created by splitting request_uri by /.
Example 2: Use a forward slash (/) to split the value of the request_uri field into 3 substrings and return a collection of the substrings.
- Sample field
```
request_uri:/request/path-1/file-9
```
- Query and analysis statement (Test)
```
* | SELECT
  split(request_uri, '/', 3)
```
- In the query and analysis results, the value of the _col0 column is ["","request","path-1/file-9"].

Split_part function

Splits a string by a specified separator and returns the substring at a specified index.

Syntax

split_part(x, delimiter, part)

Parameters

Parameter	Description
x	The string to split. This value must be a varchar.
delimiter	A non-empty string used as the separator.
part	An integer expression specifying the 1-based index of the substring to return. The value must be greater than 0.

Return value

varchar

Example

Split the value of the request_uri field by using a question mark (?) and return the first substring, which is the file path. Then, count 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

Query and analysis results: /request/path-2/file-6 has a PV of 49, /request/path-2/file-0 has a PV of 47, and /request/path-3/file-2 has a PV of 44.

split_to_map function

The split_to_map function parses a string of key-value pairs into a map using two delimiters.

Syntax

split_to_map(x, delimiter01, delimiter02)

Parameters

Parameter	Description
x	The input string to parse, which must be of the varchar type.
delimiter01	The delimiter that separates key-value pairs.
delimiter02	The delimiter that separates the key from the value in each pair.

Return type

map

Example

Use an English comma (,) and an English colon (:) to split the value of the time field and return a MAP.

Sample field

upstream_response_time:"80",request_time:"40"

Query and analysis statement

* | SELECT
  split_to_map(time, ',', ':')

The function returns a map, which is displayed in the query and analysis results in JSON format: {"request_time":"\"40\"","upstream_response_time":"\"80\""}.

Strpos function

The strpos function returns the starting position of a substring in a string. It is an alias for the position function.

Syntax

strpos(x, sub_string)

Parameters

Parameter	Description
x	The source string to be searched. It must be a varchar.
sub_string	The substring to search for.

Return value type

Returns an integer that represents the 1-based starting position of the substring. Returns 0 if the substring is not found.

Example

This example finds the position of the character 'H' in the server_protocol field.

Query statement (Test)

* | SELECT
  strpos(server_protocol, 'H')

Query and analysis results: The _col0 column returns two rows of data, both with the value 1, which indicates that the position of the first occurrence of the character H in the server_protocol field is 1.

substr function

The substr function extracts a substring from a string, starting at a specified position.

Syntax

Extracts a substring from a specified start position to the end of the string.
```
substr(x, start)
```
Extracts a substring of a specified length from a string, starting at a specified position.
```
substr(x,start,length)
```

Parameters

Parameter	Description
x	The source string. It must be of the `varchar` type.
start	The 1-based starting position for the extraction.
length	An optional integer specifying the number of characters to extract. If this parameter is omitted, the function extracts all characters from the `start` position to the end of the string.

Return value type

varchar.

Example

Extract the first 4 characters from the value of the server_protocol field (the HTTP part), and then count the number of HTTP requests.

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

Query statement (Test)

* | SELECT
  substr(server_protocol, 1, 4) AS protocol,
  count(*) AS count
GROUP BY
  protocol

In the query and analysis results, the value of the protocol column is HTTP, and the value of the count column is 9078.

to_utf8 function

Encodes a string to a UTF-8 binary representation.

Syntax

to_utf8(x)

Parameters

Parameter	Description
x	The string to encode. This must be a varchar.

Return value type

varbinary

Example

Encode the string 'log' to the UTF-8 format.

Query statement (Test)
```
* | SELECT
  to_utf8('log')
```
In the query and analysis results, the return value of the _col0 column is bG9n.

Trim function

Removes leading and trailing spaces from a string.

Syntax

trim(x)

Parameters

Parameter	Description
x	The input string, which must be a varchar.

Return type

varchar

Example

Remove the leading and trailing spaces from the instance_id field value.

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

Upper function

Converts a string to uppercase.

Syntax

upper(x)

Parameters

Parameter	Description
x	The value must be of type varchar.

Return value type

varchar

Example

This example converts the value of the region field to uppercase.

Sample field
```
region:cn-shanghai
```
Query statement (Test)
```
* | SELECT
  upper(region)
```
The query and analysis result is CN-SHANGHAI.

csv_extract_map function

The csv_extract_map function parses a single-line CSV string and returns a map.

Syntax

csv_extract_map(x, delimiter, quote, keys)

Parameters

Parameter	Description
x	The `varchar` string that contains the single-line CSV text.
delimiter	A single `varchar` character that separates values in the string.
quote	The single `varchar` character used to enclose values.
keys	An array of strings that defines the key names for the extracted values. The number of keys must match the number of values in the CSV string. If they do not match, the function returns null.

Return value type

map(varchar, varchar)

Example

This example extracts 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
The query returns the following JSON record: {"ip":"192.168.0.100","host":"example.aliyundoc.com","time":"10/Jun/2019:11:32:16,127 +0800"}.

ilike function

Checks if a string matches a specified pattern, ignoring case.

Syntax

ilike(x, pattern)

Parameters

Parameter

Description

The string to evaluate. It must be of the varchar type.

pattern

The character pattern. The pattern can include the following wildcard characters:

The percent sign (%) matches any sequence of zero or more characters.
The underscore (_) matches a single character.

Return type

boolean

Example

This example checks if the value of request_uri ends with file-6.

Sample field

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

Query statement

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

Query and analysis results

The query and analysis result is true, indicating that request_uri ends with file-6.

str_uuid

The str_uuid() function returns a random 128-bit identifier as a string.

Syntax

str_uuid()

Return value

Return value type: VARCHAR
Format: A 36-character string with 32 hexadecimal digits and 4 hyphens -.
Structure: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

Example

To generate a large batch of unique identifiers in a simulated test environment:

* | extend uuid = str_uuid()

Gzip_compress function

The gzip_compress function compresses a string 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 to compress.
compression_level	BIGINT	Compression level, an optional 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: Achieve maximum compression for large text
To maximize compression for a large log (tens of thousands of characters) and save storage space, use level 9:
```
* | extend compress_data =  gzip_compress('Hello World',9)
```

gzip_decompress

gzip_decompress decompresses GZIP-compressed binary data (Varbinary) and returns the original plaintext content.

Syntax

gzip_decompress(binary_data)

Parameters

binary_data must be valid GZIP-compressed data, typically generated by the gzip_compress function. 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. As a standard SQL function, it supports complex query conditions, including boolean operations, field queries, fuzzy queries, and range queries.

Limitations

Limitation	Description
Limit per subquery	You can use only one `search()` function in each subquery (underlying SELECT). If you need multiple query conditions, combine the conditions into a single `search()` call, for example, `search('error AND timeout')`.
OR operator limit	The `search()` function cannot be combined with the SQL `OR` operator. However, you can use the `OR` operator inside the search() function. For example, `search('error OR warning')` is allowed.
An index is created and not in scan mode.	The search function is not supported in scan mode.
Query syntax conflict	You cannot use the search function when the query syntax input contains actual filter conditions. You can use this function when the query syntax input is empty or is `*`.
Parameter type	The parameter for the `search()` function must be a string literal. The function does not support dynamic values such as column references, variables, or function expressions.
Number of parameters	The function must take exactly one parameter.

Syntax

search(search_expression)

Usage in an SQL analytic statement:

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

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

Parameters

Parameter	Description
search_expression	The `search_expression` is a string literal that represents the query expression. It must fully comply with the SLS query syntax and provides the same features that are described in Query syntax and features, such as full-text queries, field queries, Boolean operations, fuzzy queries, and range queries.

Return value type

BOOLEAN type. true indicates that the current row matches the query condition, and false indicates that it does not match.

Examples

Example 1: Find logs that contain both error and timeout.

* | SELECT * FROM log WHERE search('error AND timeout')

Example 2: Find logs where the status field is 200.

* | SELECT * FROM log WHERE search('status: 200')

Example 3: Combine the search() function with an SQL predicate to find logs where status is 200 and 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.