The read-only analysis instance supports most MySQL string functions, but it still has limitations and incompatibilities in some scenarios. This document lists the string functions supported by the read-only analysis instance and specifies the supported data types for each function's parameters. Parameter types and functions not listed in this document are not supported.
Supported String Functions
SUBSTR Function
The string extraction function supports the following usage methods.
Syntax: SUBSTR(str, start)
str supports the following data types: char, varchar.
start supports the following data types: bigint, double, decimal.
Syntax: SUBSTR(str FROM start)
str supports the following data types: char, varchar.
start supports the following data types: bigint, double, decimal.
Syntax: SUBSTR(str, start, length)
str supports the following data types: char, varchar.
start supports the following data types: bigint, double, decimal.
length supports the following data types: bigint, double, decimal.
Syntax: SUBSTR(str FROM start FOR length)
str supports the following data types: char, varchar.
start supports the following data types: bigint, double, decimal.
length supports the following data types: bigint, double, decimal.
CHAR_LENGTH Function
String length judgment function, returns the number of characters in a string.
Syntax: CHAR_LENGTH(str)
str supports the following data types: char, varchar.
REPLACE Function
String replacement function.
Syntax: REPLACE(str, from_str, to_str)
str supports the following data types: char, varchar.
from_str supports the following data types: char, varchar.
The following data types are supported by to_str: char, varchar.
CONCAT Function
String concatenation function.
Syntax: CONCAT(str1, str2, ...)
The following data types are supported by parameters such as str1 and str2: char, varchar.
CONCAT_WS Function
String concatenation function with a delimiter. The first parameter is the delimiter.
Syntax: CONCAT_WS(separator, str1, str2, ...)
The following data types are supported by separator: char, varchar.
The following data types are supported by parameters such as str1 and str2: char, varchar.
LEFT Function
Returns a specified number of characters from the beginning of a string.
Syntax: LEFT(str, len)
str supports the following data types: char, varchar.
len supports the following data types: int, bigint, double, decimal.
RIGHT Function
Returns a specified number of characters from the end of a string.
Syntax: RIGHT(str, len)
str supports the following data types: char, varchar.
len supports the following data types: int, bigint, double, decimal.
ASCII Function
Returns the ASCII code value corresponding to the first character of a string.
Syntax: ASCII(str)
str supports the following data types: char, varchar.
LENGTH Function
String length judgment function, returns the number of bytes of a string.
Syntax: LENGTH(str)
str supports the following data types: char, varchar.
TRIM Function
Function to remove specified characters from the beginning and end of a string.
Syntax: TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
remstr supports the following data types: char, varchar.
str supports the following data types: char, varchar.
LTRIM Function
Remove the space characters preceding a string.
Syntax: LTRIM(str)
str supports the following data types: char, varchar.
RTRIM Function
Remove the space characters following a string.
Syntax: RTRIM(str)
str supports the following data types: char, varchar.
POSITION Function
Returns the position where a substring first appears in a string.
Syntax: POSITION(substr IN str)
str supports the following data types: char, varchar.
substr supports the following data types: char, varchar.
INSTR Function
Find the position where a string first appears in another string.
Syntax: INSTR(str, substr)
str supports the following data types: char, varchar.
substr supports the following data types: char, varchar.
LOCATE Function
Returns the position where a substring first appears in a string.
Syntax: LOCATE(substr, str)
substr supports the following data types: char, varchar.
str supports the following data types: char, varchar.
LOWER Function
Convert all uppercase letters in a given string to lowercase letters.
Syntax: LOWER(str)
str supports the following data types: char, varchar.
UCASE Function
Convert all lowercase letters in a given string to uppercase letters.
Syntax: UCASE(str)
str supports the following data types: char, varchar.
UPPER Function
Convert all lowercase letters in a given string to uppercase letters.
Syntax: UPPER(str)
str supports the following data types: char, varchar.
SUBSTRING_INDEX Function
It extracts a string based on a specified delimiter and supports reverse extraction.
Syntax: SUBSTRING_INDEX(str, delim, count)
str supports the following data types: char, varchar.
The data types supported for delim are: char, varchar. They must be constants; variables such as field names are not supported.
The data types supported by count are: int, bigint, double, decimal. It supports negative numbers, must be constants, and does not support variables such as field names.
LPAD Function
Pad the beginning of a string to a specified length.
Syntax: LPAD(str, len, padstr)
str supports the following data types: char, varchar.
len supports the following data types: int, bigint, double, decimal. The value must be a constant; variables such as field names are not supported.
padstr supports the following data types: char, varchar.
RPAD Function
Pad the end of a string to a specified length.
Syntax: RPAD(str, len, padstr)
str supports the following data types: char, varchar.
len supports the following data types: int, bigint, double, decimal. The value must be a constant.
padstr supports the following data types: char, varchar.
STRCMP Function
Compares two strings and returns an integer based on the comparison result.
Syntax: STRCMP(expr1, expr2)
expr1 supports the following data types: char, varchar.
expr2 supports the following data types: char, varchar.
QUOTE Function
Quotes a string to produce a result that can be used as a properly escaped SQL string literal.
Syntax: QUOTE(str)
str supports the following data types: char, varchar.
FIND_IN_SET Function
Returns the position of a specified string within a comma-separated list of strings. The strlist parameter supports only the string type and does not support the set type.
Syntax: FIND_IN_SET(str, strlist)
str: The string to be queried.
strlist: A comma-separated list of strings, for example '1,2,6,8'.
FIELD Function
Returns the first occurrence position of str within the subsequent parameter list. This is commonly used for sorting operations on query result sets in a specified order.
Syntax: FIELD(str, str1, str2, ...)
str: The string to be searched for.
str1, str2, and so on: A list of strings to be compared sequentially, sorted in left-to-right order.
UUID Function
Generates a 36-character UUID string.
Syntax: UUID()
Input parameters are not required.