tencent cloud

Cloud Log Service

문서Cloud Log ServiceOperation GuideSearch and Analysis (Log Topic)Statistical Analysis (SQL)SQL FunctionsString Functions

String Functions

Download
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-06-04 18:24:08
This section introduces the basic syntax and examples of string functions.
Note
Strings should be enclosed in a pair of single quotes (''). Characters that are unquoted or enclosed in a pair of double quotes ("") indicate field or column names. For example, 'status' indicates the string status, whereas status or "status" indicates the log field status.
When a string contains a single quotation mark ', use '' (two single quotation marks) to represent the single quotation mark itself. For example, '{''version'': ''1.0''}' indicates the raw string {'version': '1.0'}. No special processing is required if the string itself contains a double quotation mark ".
In the functions below, all the key parameters indicate log field names.

Function Description

Function Name
Description
Example
chr
(number)
Returns characters that match the ASCII code point (bit) specified by the input parameter. The return value is of the varchar type.
Returns the character value that matches the 77th ASCII code. * | SELECT chr(77)
codepoint
(string)
Converts field values from ASCII code format to bigint type. The return value is of the integer type.
Converts the character value in ASCII code to its corresponding position. * | SELECT codepoint('M')
concat
(key1, ..., keyN)
Concatenates the strings key1, key2, ..., keyN, yielding a result that is equivalent to using the || delimiter. The return value is of the varchar type. Note that if any string is null, the return value will also be null. To skip null values, use concat_ws.
Concatenate multiple strings into a single string: * | SELECT concat(remote_addr, host, time_local)
Concatenate two strings into a single string with a line break: * | SELECT concat('First line', chr(10), 'Second line')
Note:
Here, chr(10) uses the ASCII code to represent a line break.
concat_ws
(split_string,key0, ..., keyN)
Concatenates strings key1, key2, ..., keyN using split_string as the delimiter. split_string can be a string or a variable. If split_string is null, null values in key1, key2, ..., keyN will be skipped. The return value is of the varchar type.
Concatenates strings using / as the delimiter.
* | SELECT concat_ws('/', remote_addr,host,time_local)
concat_ws
(split_string, array(varchar))
Concatenates the elements within the array into a single string using split_string as the delimiter. If split_string is null, the result will be null, and null values in the array will be skipped. The return value is of the varchar type. Note: In this function, the array(varchar) parameter is an array, not a string.
Concatenates strings using # as the delimiter. In this example, the output of the split function is an array.
* | select concat_ws('#',split('cloud.tencent.com/product/cls', '/'))
format
(format,args...)
Formats the output of the args parameter according to the specified format string. The return value is of the varchar type.
Formats the output of the remote_addr and host parameters using the format string 'IP address: %s, domain name: %s'. * | SELECT format('IP address: %s, domain name: %s', remote_addr, host)
hamming_distance
(key1, key2)
Returns the Hamming distance between the strings key1 and key2. Note that the two strings should have the same length. The return value is of the bigint type.
Returns the Hamming distance between the remote_addr string and the remote_addr string. * | SELECT hamming_distance(remote_addr, remote_addr)
length
(key)
Calculates the length of the string. The return value is of the bigint type.
Returns the string length of http_user_agent. * | SELECT length(http_user_agent)
levenshtein_distance
(key1, key2)
Returns the Levenshtein distance between the strings key1 and key2. The return value is of the bigint type.
Returns the edit distance between the remote_addr string and the http_protocol string. * | SELECT levenshtein_distance(remote_addr, http_protocol)
lower
(key)
Converts the string to lowercase characters. The return value is of the varchar type and is in lowercase.
Converts the http_protocol string to lowercase. * | SELECT lower(http_protocol)
lpad
(key, size, padstring)
Left pads the string to the specified size using padstring. If the size is less than the length of the key, the string will be truncated to that size. The size should be a non-negative number, and padstring cannot be empty. The return value is of the varchar type.
Pads the remote_addr string to the left with '0' to a length of 32 characters. * | SELECT lpad(remote_addr, 32, '0')
ltrim
(key)
Removes leading whitespace characters from the string. The return value is of the varchar type.
Removes the whitespace characters from the left side of the http_user_agent string. * | SELECT ltrim(http_user_agent)
position
(substring IN key)
Returns the position of the substring in the string, starting from 1. It returns 0 if the substring is not found. This function uses the special IN syntax for its parameter. See also strpos(). The return value is of the bigint type.
Returns the position of the 'G' character in http_method. * | select position('G' IN http_method)
replace
(key, substring)
Removes all occurrences of the substring from the string key. The return value is of the varchar type.
Removes all 'Oct' from the time_local string. * | select replace(time_local, 'Oct')
replace
(key, substring, replace)
Replaces all occurrences of the substring in the string with replace. The return value is of the varchar type.
Replaces all 'Oct' in the time_local string with '10'. * | select replace(time_local,'Oct','10')
reverse
(key)
Reverses the key string. The return value is of the varchar type.
Reverses the host string. * | select reverse(host)
rpad
(key, size, padstring)
Right pads the string to the specified size using padstring. If the size is less than the length of the key, the string will be truncated to that size. The size should be a non-negative number, and padstring cannot be empty. The return value is of the varchar type.
Pads the remote_addr string to the right with '0' to a length of 32 characters. * | select rpad(remote_addr, 32, '0')
rtrim
(key)
Removes trailing whitespace characters from the string. The return value is of the varchar type.
Removes all whitespace characters from the right side of the http_user_agent string. * | select rtrim(http_user_agent)
split
(key, delimiter)
Splits the string using the specified delimiter, returning an array of strings.
Splits the http_user_agent string using the specified '/' delimiter and returns a string array. * | SELECT split(http_user_agent, '/')
split
(key, delimiter, limit)
Splits the string using the specified delimiter, returning an array of strings with the maximum length of limit. The last element in the array always contains all the remaining parts of the key. The limit should be a positive integer.
Splits the http_user_agent string using the specified '/' delimiter and returns a string array with a length of 10. * | SELECT split(http_user_agent, '/', 10)
split_part
(key, delimiter, index)
Splits the string using the specified delimiter and returns the string at the specified index (starting from 1) position in the array. If the index is greater than the array length, it returns null. The return value is of the varchar type.
Splits the http_user_agent string using the specified '/' delimiter and returns the string at the first position. * | SELECT split_part(http_user_agent, '/', 1)
strpos
(key, substring)
Returns the position of the substring in the string, starting from 1. It returns 0 if the substring is not found. The return value is of the bigint type.
Returns the position of the 'org' string in the host string. * | SELECT strpos(host, 'org')
strpos
(key, substring, instance)
Returns the position of the instance-th occurrence of a substring within a string. If the instance is a negative number, counting begins from the end of the string. The position result starts at 1, and it returns 0 if the specified position is not found. The return value is of the bigint type.
Returns the position of the first instance of 'g' in the host string after splitting. * | SELECT strpos(host, 'g', 1)
substr
(key, start)
Returns the rest of the characters in a string starting from the start position. The position result starts from 1. If start is negative, counting starts from the end. The return value is of the varchar type.
Returns the remaining characters from the second position in the remote_user string. * | SELECT substr(remote_user, 2)
substr
(key, start, length)
Returns a substring from the string, starting at the position specified by start, with a maximum length defined by length. The position result starts from 1. If start is negative, counting starts from the end of the string. The return value is of the varchar type.
Returns the remaining characters from the second position in the remote_user string, with a total length not exceeding 5 characters. * | SELECT substr(remote_user, 2, 5)
translate
(key, from, to)
Replaces characters in key that appear in from with the corresponding characters in to. If from contains duplicate characters, only the first occurrence is considered. Characters not present in from remain unchanged. If from is longer than to, the corresponding excess characters will be removed from the result. The return value is of the varchar type.
Replaces the characters '123' in the remote string with the corresponding characters 'ABC'. * | SELECT translate(remote_user, '123', 'ABC')
trim
(key)
Removes leading and trailing whitespace characters from the string. The return value is of the varchar type.
Deletes the whitespace characters from the beginning and end of the http_cookies string. * | SELECT trim(http_cookies)
upper
(key)
Converts the string to uppercase characters. The return value is of the varchar type and is in uppercase.
Converts lowercase characters in the host string to uppercase. * | SELECT upper(host)
word_stem
(word)
Returns the stem of word in English. The return value is of the varchar type.
Returns the word for Mozilla.
* | SELECT word_stem('Mozilla')
word_stem
(word, lang)
Returns the stem of word in the lang language. The return value is of the varchar type.
Returns the word for the stem of selects. * | SELECT word_stem('selects', 'en')

Unicode Functions

Function Name
Description
Example
normalize
(string)
Converts a string to the NFC standard format. The return value is of the varchar type.
* | SELECT normalize('ĉ')
normalize
(string, form)
Converts a string to the form format. The form parameter in this function should be keywords (NFD, NFC, NFKD, or NFKC) instead of a string. The return value is of the varchar type.
* | SELECT normalize('ĉ',NFC)
to_utf8
(string)
Converts the string to a UTF-8 binary string of the varbinary type.
* | SELECT to_utf8('test')
from_utf8
(binary)
Converts a binary string to a UTF-8 string. Invalid UTF-8 characters will be replaced with U+FFFD. The return value is of the varchar type.
* | SELECT from_utf8(from_base64('SGVsbG8='))
from_utf8
(binary, replace)
Converts a binary string to a UTF-8 string. Invalid UTF-8 characters will be replaced with replace. The return value is of the varchar type.
* | SELECT from_utf8(from_base64('SGVsbG8='),0)

Example

This section provides query and analysis statement examples based on the following log sample. Raw log data sample:
10.135.46.111 - - [05/Oct/2015:21:14:30 +0800] "GET /my/course/1 HTTP/1.1" 127.0.0.1 200 782 9703 "http://127.0.0.1/course/explore?filter%5Btype%5D=all&filter%5Bprice%5D=all&filter%5BcurrentLevelId%5D=all&orderBy=studentNum" "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:64.0) Gecko/20100101 Firefox/64.0"  0.354 0.354
Once the Single-Line - Full Regular Matching collection mode is configured, custom key names are detailed as follows:
body_bytes_sent: 9703
http_host: 127.0.0.1
http_protocol: HTTP/1.1
http_referer: http://127.0.0.1/course/explore?filter%5Btype%5D=all&filter%5Bprice%5D=all&filter%5BcurrentLevelId%5D=all&orderBy=studentNum
http_user_agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:64.0) Gecko/20100101 Firefox/64.0
remote_addr: 10.135.46.111
request_length: 782
request_method: GET
request_time: 0.354
request_url: /my/course/1
status: 200
time_local: [05/Oct/2015:21:14:30 +0800]
upstream_response_time: 0.354
Analysis statement examples:
Split the request_url field value using the question mark (?), return the first string (the file path part), and then count the number of requests for different paths.
* | SELECT count(*) AS pv, split_part(request_url, '?', 1) AS Path GROUP BY Path ORDER BY pv DESC LIMIT 3
Extract the first four characters (the HTTP part) from the http_protocol field value and count the number of requests for the HTTP protocol.
* | SELECT substr(http_protocol,1,4) AS http_protocol, count(*) AS count group by http_protocol
Replace the characters '123' in the remote_user string with the characters 'ABC'. The returned result is of the varchar type.
* | SELECT translate(remote_user, '123', 'ABC')
Extract the values from the remote_user field from the 2nd to the 5th position.
* | SELECT substr(remote_user, 2, 5)
Return the position of the H letter in the http_protocol field value.
* | SELECT strpos(http_protocol, 'H')
Split the http_protocol field value into two substrings using a slash (/) and return the collection of the substrings.
* | SELECT split(http_protocol, '/', 2)
Replace Oct in the time_local field value with 10.
* | SELECT replace(time_local, 'Oct', '10')

이전 주제: Function Overview다음 주제: Date and Time Functions

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백