Currently, the read-only analysis engine supports most of MySQL's string functions, but there are still limitations and incompatibilities in certain scenarios. This document introduces the list of supported string functions in the read-only analysis engine, and enumerates type restrictions on input parameters. Input parameters and functions not mentioned in the document are not supported.
Supported String Function Types
substr Function
Substring extraction function with the following usage methods.
Syntax: substr(str, start)
Supported value types for str: char and varchar.
Supported value types for start: bigint, double, and decimal.
Syntax: substr(str from start)
Supported value types for str: char and varchar.
Supported value types for start: bigint, double, and decimal.
Syntax: substr(string, start, length)
Supported value types for str: char and varchar.
Supported value types for start: bigint, double, and decimal.
Supported value types for length: bigint, double, and decimal.
Syntax: substr(string from start for length)
Supported value types for str: char and varchar.
Supported value types for start: bigint, double, and decimal.
Supported value types for length: bigint, double, and decimal.
char_length Function
String length judgment function, which returns the number of characters in a string.
Syntax: char_length(str)
Supported value types for str: char and varchar.
replace Function
String replacement function.
Syntax: replace(str, from_str, to_str)
Supported value types for str: char and varchar.
Supported value types for from_str: char and varchar.
Supported value types for to_str: char and varchar.
concat Function
String concatenation function.
Syntax: concat(str1, str2, ...)
Supported value types for str1: char and varchar.
Supported value types for str2: char and varchar.
concat_ws Function
String concatenation function with delimiter(s).
Syntax: concat_ws(str1, str2, ...)
Supported value types for str1: char and varchar.
Supported value types for str2: char and varchar.
left Function
A function that returns a specified number of characters starting from the first character of a string.
Syntax: left(str, len)
Supported value types for str1: char and varchar.
Supported value types for len: int, bigint, double, and decimal.
right Function
A function that returns a specified number of characters starting from the last character of a string.
Syntax: right(str, len)
Supported value types for str1: char and varchar.
Supported value types for len: int, bigint, double, and decimal.
ascii Function
A function that converts a character into its ASCII code value.
Syntax: ascii(str)
Supported value types for str1: char and varchar.
length Function
String length judgment function, which returns the number of bytes in a string.
Syntax: length(str)
Supported value types for str1: char and varchar.
trim Function
A function that clears unnecessary characters before and after a string.
Syntax: trim([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
Supported value types for remstr: char and varchar.
Supported value types for str: char and varchar.
ltrim Function
A function that clears unnecessary characters before a string.
Syntax: ltrim(str)
Supported value types for str: char and varchar.
trim Function
Function that removes unnecessary characters before and after a string.
Syntax: trim([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
Supported value types for remstr: char and varchar data.
Supported value types for str: char and varchar data.
ltrim Function
Function that removes unnecessary characters before a string.
Syntax: ltrim(str)
Supported value types for str: char and varchar data.
rtrim Function
A function that clears unnecessary characters after a string.
Syntax: rtrim(str)
Supported value types for str: char and varchar.
position Function
A function that returns the position of a substring.
Syntax: position(substr IN str)
Supported value types for str: char and varchar.
Supported value types for substr: char and varchar.
instr Function
A function that searches for the position of the first occurrence of a string in another string.
Syntax: instr(str, substr)
Supported value types for str: char and varchar.
Supported value types for substr: char and varchar.
locate Function
A function that returns the position of the first occurrence of a substring in a string.
Syntax: locate(str, substr)
Supported value types for str: char and varchar.
Supported value types for substr: char and varchar.
lower Function
A function that returns the lowercase of a string.
Syntax: lower(str)
Supported value types for str: char and varchar.
ucase Function
A function that converts all lowercase letters in a given string to uppercase letters.
Syntax: ucase(str)
Supported value types for str: char and varchar.
upper Function
A function that converts all lowercase letters in a given string to uppercase letters.
Syntax: upper(str)
Supported value types for str: char and varchar.
substring_index Function
A function that supports reverse substring extraction.
Syntax: substring_index(str, delim, count)
Supported value types for str: char and varchar.
Supported value types for delim: char and varchar. It can only be a constant instead of a variable value similar to a field value.
Supported value types for count: int, bigint, double, and decimal. It supports a negative number and can only be a constant instead of a variable value similar to a field value.
lpad Function
A function that pads the beginning of a string to reach a specified length.
Syntax: lpad(str, len, padstr)
Supported value types for str: char and varchar.
Supported value types for len: int, bigint, double, and decimal data. It can only be a constant instead of a variable value similar to a field value.
Supported value types for padstr: char and varchar.
rpad Function
A function that pads the end of a string to reach a specified length.
Syntax: rpad(str, len, padstr)
Supported value types for str: char and varchar.
Supported value types for len: int, bigint, double, and decimal. It can only be a constant.
Supported value types for padstr: char and varchar.
strcmp Function
A function that compares two strings and returns an integer based on the comparison.
Syntax: strcmp(expr1, expr2)
Supported value types for expr1: char and varchar.
Supported value types for expr2: char and varchar.
quote Function
A function that quotes a string with special characters in a query.
Syntax: quote(str)
Supported value types for str: char and varchar.
find_in_set Function
Function that finds the position of the corresponding character in the string. Currently, it only supports strlist as the string type, and does not support the set type.
Syntax: find_in_set(str,strlist)
str: string to be queried.
strlist: The field name parameters are separated by a comma (,), such as (1,2,6,8).
field Function
Function that sorts the query result set in SQL in the specified order.
Syntax: field(str,str2,...)
str: string value specified to be sorted from left to right.
uuid Function
Used to generate a random 36-bit snowflake ID.
Syntax: uuid()
Input parameters are not required.