All Products
Search

This Product

    AnalyticDB:String functions

    Document Center

    AnalyticDB:String functions

    Last Updated:Feb 05, 2026

    Use string functions to process strings. For example, you can convert character cases, remove spaces, or extract substrings. This topic describes the syntax of string functions in AnalyticDB for MySQL and provides examples.

    • ASCII: Returns the ASCII value of a character or the leftmost character in a string.

    • BIN: Returns the binary string representation of an integer.

    • BIT_LENGTH: Returns the length of a string in bits.

    • CHAR: Returns a string composed of characters corresponding to the decimal ASCII codes of integers.

    • CHAR_LENGTH or CHARACTER_LENGTH: Returns the length of a string, in characters.

    • CONCAT: Concatenates multiple strings.

    • CONCAT_WS: Concatenates multiple strings using a specified separator.

    • ELT: Returns the Nth string from a given list of strings.

    • ENCRYPT: Encrypts a string.

    • EXPORT_SET: Returns a combined string based on the bit values of an integer.

    • FIELD: Returns the index of a specified string within a list of strings.

    • FIND_IN_SET: Returns the position of a string within a comma-separated list.

    • FORMAT: Formats the number N and returns a string.

    • FROM_BASE64: Decodes a Base64-encoded string and returns the decoded result.

    • FROM_UTF8: Decodes a UTF-8 encoded string and returns the decoded result.

    • HEX: Converts an integer or a string to its hexadecimal string representation.

    • INSTR: Returns the position of the first occurrence of a substring within a string.

    • LEFT: Returns the N leftmost characters of a string.

    • LENGTH or OCTET_LENGTH: Returns the length of a string.

    • LIKE: Performs simple pattern matching.

    • LOCATE: Returns the position of the first occurrence of a substring within another string.

    • LOWER or LCASE: Converts a string to lowercase.

    • LPAD: Left-pads a string.

    • LTRIM: Removes leading spaces from a string.

    • MAKE_SET: Returns a set of comma-separated strings.

    • MD5_MUR: Converts a string to a numeric value.

    • MID: Returns a substring of a specified length from a specified position within a string. This function is the same as SUBSTR or SUBSTRING.

    • OCT: Returns the octal string representation of a specified integer.

    • ORD: Returns the code of the leftmost character if it is a multibyte character.

    • POSITION: Returns the position of the first occurrence of a substring within a string.

    • REPEAT: Returns a string repeated a specified number of times.

    • REPLACE: Replaces a part of a string with a specified string.

    • REVERSE: Reverses a string.

    • RIGHT: Returns a specified number of the rightmost characters of a string.

    • RLIKE or REGEXP: Matches a string against a regular expression. It returns 1 if the string matches the pattern, and 0 otherwise.

    • RPAD: Right-pads a string.

    • RTRIM: Removes trailing spaces from a string.

    • SPACE: Returns a string consisting of a specified number of spaces.

    • SPLIT: Splits a string by a delimiter and returns an array.

    • SPLIT_PART: Splits a string by a delimiter and returns the substring at a specified index.

    • SPLIT_TO_MAP: Splits a string using an entryDelimiter and a keyValueDelimiter, then returns a map.

    • STRCMP: Compares two strings and returns 0 if they are identical, -1 if the first string is smaller than the second based on the current sorting order, or 1 otherwise.

    • SUBSTR or SUBSTRING: Returns a substring of a specified length from a specified position.

    • SUBSTRING_INDEX: Returns a substring from a string, either before or after a specified number of occurrences of a delimiter.

    • TO_BASE64: Returns the Base64-encoded form of a string.

    • TO_UTF8: Returns the UTF-8 encoded form of a string.

    • TRIM: Removes all leading and trailing spaces from a string.

    • UPPER or UCASE: Converts a string to uppercase.

    • UNHEX: Converts hexadecimal numbers to characters.

    ASCII

    ascii(str)

    • Description: Returns the decimal ASCII value of the leftmost character of either the character str or the string str.

    • Input parameter data type: VARCHAR.

    • Return value data type: BIGINT.

    • Examples:

      • Statement:

        SELECT ascii('2');

        The following result is returned:

        +------------+
| ascii('2') |
+------------+
|         50 |
+------------+

      • Statement:

        SELECT ascii('dx');

        The following result is returned:

        +-------------+
| ascii('dx') |
+-------------+
|         100 |
+-------------+

    BIN

    bin(N)

    • Description: Returns the binary string representation of N.

      If N is null, NULL is returned.

    • Input parameter data type: BIGINT.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT bin(12);

      The following result is returned:

      +---------+
| bin(12) |
+---------+
| 1100    |
+---------+

    BIT_LENGTH

    bit_length(str)

    • Description: Returns the length of the string str in bits.

    • Input parameter data type: VARCHAR.

    • Return value data type: BIGINT.

    • Examples:

      • Statement:

        SELECT bit_length('text');

        The following result is returned:

        +--------------------+
| bit_length('text') |
+--------------------+
|                 32 |
+--------------------+

      • Statement:

        SELECT bit_length('China');

        The following result is returned:

        +---------------------+
| bit_length('China') |
+---------------------+
|                  40 |
+---------------------+

    CHAR

    char(N1, N2, ...)

    • Description: Returns a string composed of characters corresponding to the decimal ASCII codes of the integers N1, N2, and so on.

    • Input parameter data type: BIGINT.

    • Return value data type: VARBINARY.

    • Example: 

      SELECT char(97,110,97,108,121,116,105,99,100,98);

      The following result is returned:

      +-------------------------------------------+
| char(97,110,97,108,121,116,105,99,100,98) |
+-------------------------------------------+
| analyticdb                                |
+-------------------------------------------+

    CHAR_LENGTH or CHARACTER_LENGTH

    char_length(str)
character_length(str)

    • Description: Returns the length of the string str, in characters.

      The length of a Chinese character is 1.

    • Input parameter data type: VARCHAR.

    • Return value data type: BIGINT.

    • Examples:

      • Statement:

        SELECT char_length('China');

        The following result is returned:

        +----------------------+
| char_length('China') |
+----------------------+
|                    5 |
+----------------------+

      • Statement:

        SELECT char_length('abc');

        The following result is returned:

        +--------------------+
| char_length('abc') |
+--------------------+
|                  3 |
+--------------------+

    CONCAT

    concat(str 1, …, str n)

    • Description: Concatenates multiple strings. If any parameter is null, the return value is null.

    • Input parameter data type: VARCHAR.

    • Return value data type: VARCHAR.

    • Examples:

      • Statement:

        SELECT concat('aliyun', ', ', 'analyticdb');

        The following result is returned:

        +--------------------------------------+
| concat('aliyun', ', ', 'analyticdb') |
+--------------------------------------+
| aliyun, analyticdb                   |
+--------------------------------------+

      • Statement:

        SELECT concat('abc',null,'def');

        The following result is returned:

        +--------------------------+
| concat('abc',null,'def') |
+--------------------------+
| NULL                     |
+--------------------------+

    CONCAT_WS

    concat_ws(separator, str 1, …, str n)

    • Description: Concatenates multiple strings using a specified separator. The first parameter separator is the delimiter for the other parameters. Any null strings are skipped during concatenation.

    • Input parameter data type: VARCHAR.

    • Return value data type: VARCHAR.

    • Examples:

      • Statement:

        SELECT concat_ws(',', 'First name', 'Second name', 'Last Name')AS result;

        The following result is returned:

        +----------------------------------+
| result                           |
+----------------------------------+
| First name,Second name,Last Name |
+----------------------------------+

      • Statement:

        SELECT concat_ws(',','First name',NULL,'Last Name')AS result;

        The following result is returned:

        +----------------------+
| result               |
+----------------------+
| First name,Last Name |
+----------------------+

    ELT

    elt(N, str 1, ...,str n);

    • Returns the Nth string.

      If N<1 or N is greater than the number of string parameters, null is returned.

    • Input parameter data type: N is of the BIGINT type, and str is of the VARCHAR type.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT elt(4, 'Aa', 'Bb', 'Cc', 'Dd');

      The following result is returned:

      +--------------------------------+
| elt(4, 'Aa', 'Bb', 'Cc', 'Dd') |
+--------------------------------+
| Dd                             |
+--------------------------------+

    ENCRYPT

    encrypt(x, y);

    • Description: Encrypts the parameter x. y is the salt value.

    • Input parameter data type: x is of the VARBINARY type, and y is of the VARCHAR type.

    • Return value data type: VARBINARY

    • Example: 

      SELECT encrypt('abdABC123','key');

      The following result is returned:

      +--------------------------------------------------------+
| encrypt('abdABC123','key')                             |
+--------------------------------------------------------+
| 0x6B657A617A6D63496F2E614377                           |
+--------------------------------------------------------+

    EXPORT_SET

    export_set(bits, onstr, offstr [, separator[,number_of_bits]]);

    • Description: Converts bits to a binary value. The parameters are described as follows:

      • The system checks the binary value from right to left. If a bit is 1, it is replaced with the onstr value. If a bit is 0, it is replaced with the offstr value.

      • The return values are separated by the separator.

      • number_of_bits specifies the number of bits to check. The default value is 64. If you specify a value greater than 64 for number_of_bits, the value is trimmed to 64. If you specify -1 for number_of_bits, the default value 64 is used.

    • Input parameter data type: bits and number_of_bits are of the BIGINT type. onstr, offstr, and separator are of the VARCHAR type.

    • Return value data type: VARCHAR.

    • Examples:

      • Convert 5 to a binary value, and take the first two bits from right to left. Replace 1 with a and 0 with b. Separate a and b with a comma (,). The statement is as follows:

        SELECT export_set(5,'a','b',',',2);

        The following result is returned:

        +-----------------------------+
| export_set(5,'a','b',',',2) |
+-----------------------------+
| a,b                         |
+-----------------------------+

      • Convert 6 to a binary value, and take the first 10 bits from right to left. Replace 1 with 1 and 0 with 0. Separate 1 and 0 with a comma (,). The statement is as follows:

        SELECT export_set(6,'1','0',',',10);

        The following result is returned:

        +------------------------------+
| export_set(6,'1','0',',',10) |
+------------------------------+
| 0,1,1,0,0,0,0,0,0,0          |
+------------------------------+

    FIELD

    FIELD(str, str 1, str 2,..., str n);

    • Description: Returns the index of str within the list of str 1, str 2, ..., str n. If str is not found, 0 is returned.

    • Input parameter data type: VARCHAR.

    • Return value data type: BIGINT.

    • Example: 

      SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');

      The following result is returned:

      sq+-------------------------------------------+
| FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff') |
+-------------------------------------------+
|                                         2 |
+-------------------------------------------+

    FIND_IN_SET

    find_in_set(str, strlist)

    • Description: Returns the position of str within the list strlist.

      If str is not in strlist or if strlist is an empty string, 0 is returned.

      If either the str or strlist parameter is null, null is returned.

    • Input parameter data type: str and strlist are of the VARCHAR type.

    • Return value data type: BIGINT.

    • Example: 

      SELECT find_in_set('b','a,b,c,d');

      The following result is returned:

      +----------------------------+
| find_in_set('b','a,b,c,d') |
+----------------------------+
|                          2 |
+----------------------------+

    FORMAT

    format(X, D)

    • Description: Formats the number X into a #,###,###.## style, rounds it to D decimal places, and returns the result as a string.

      If D is 0, the result has no decimal point or fractional part.

    • Input parameter data type: X is of the DOUBLE type, and D is of the BIGINT type.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT format(12332.123456, 4)AS result1, format(12332.1,4)AS result2, format(12332.2,0)AS result3;

      The following result is returned:

      +-------------+-------------+---------+
| result1     | result2     | result3 |
+-------------+-------------+---------+
| 12,332.1235 | 12,332.1000 | 12,332  |
+-------------+-------------+---------+

    FROM_BASE64

    from_base64(x)

    • Description: Decodes the Base64-encoded parameter x and returns the decoded result.

    • Input parameter data type: VARBINARY or VARCHAR.

    • Return value data type: VARBINARY.

      Note

      The decoded return value is of the VARBINARY type. To convert the result to the VARCHAR type, use one of the following methods:

      • For AnalyticDB for MySQL clusters of version 3.1.4 or later, you can use the CAST AS VARCHAR function to convert data types. For more information, see the CAST function.

      • For AnalyticDB for MySQL clusters earlier than V3.1.4, use the FROM_UTF8 function to convert data types. For more information, see FROM_UTF8.

    • Examples:

      • The input parameter is of the VARCHAR type. The statement is as follows:

        SELECT from_base64('Q2hpbmE=');

        The following result is returned:

        +--------------------------------------------------+
| from_base64('Q2hpbmE=')                          |
+--------------------------------------------------+
| 0x4368696E61                                     |
+--------------------------------------------------+
        Note

        The return value of the preceding statement is of the VARBINARY type. To obtain the original VARCHAR value, run the following statement:

        SELECT cast(from_base64('Q2hpbmE=') AS varchar);

        The following result is returned:

        +------------------------------------------+
| cast(from_base64('Q2hpbmE=') AS varchar) |
+------------------------------------------+
| China                                    |
+------------------------------------------+

      • The input parameter is of the VARBINARY type. The statement is as follows:

        SELECT from_base64(cast(to_base64('China') AS varbinary));

        The following result is returned:

        +--------------------------------------------------------------------------------------------------------+
| from_base64(cast(to_base64('China') AS varbinary))                                                     |
+--------------------------------------------------------------------------------------------------------+
| 0x4368696E61                                                                                           |
+--------------------------------------------------------------------------------------------------------+

    FROM_UTF8

    from_utf8(x)
from_utf8(x, y)

    • Description:

      • from_utf8(x): Decodes the UTF-8 encoded x and returns the decoded result.

      • from_utf8(x, y): Decodes the parameter x as a UTF-8 string, replacing any invalid characters with a specified character.

        Note

        • The y parameter is optional. If you do not specify y, is returned by default.

        • y can be the invalid character itself, such as #, or the ASCII code of the invalid character, such as 35.

    • Input parameter data type: x is of the VARBINARY type, and y is of the VARCHAR or BIGINT type.

    • Return value data type: VARCHAR.

    • Examples:

      • Decode a UTF-8 encoded parameter and return the result. The statement is as follows:

        SELECT from_utf8(to_utf8('hello'));

        The following result is returned:

        +-----------------------------+
| from_utf8(to_utf8('hello')) |
+-----------------------------+
| hello                       |
+-----------------------------+

      • Decode a parameter that is not UTF-8 encoded. The statement is as follows:

        SELECT from_utf8(unhex('58BF'));

        The following result is returned:

        +--------------------------+
| from_utf8(unhex('58BF')) |
+--------------------------+
| X                       |
+--------------------------+

      • Decode a parameter that is not UTF-8 encoded and replace the invalid character with #. The statement is as follows:

        SELECT from_utf8(unhex('58BF'), '#');

        The following result is returned:

        +-------------------------------+
| from_utf8(unhex('58BF'), '#') |
+-------------------------------+
| X#                            |
+-------------------------------+

      • Decodes non-UTF-8 encoded parameters and replaces invalid byte sequences with an invalid character that has an ASCII code of 35. The statement is as follows:

        SELECT from_utf8(unhex('58BF'), '35');

        The following result is returned:

        +-------------------------------+
| from_utf8(unhex('58BF'), '35') |
+-------------------------------+
| X#                            |
+-------------------------------+

    HEX

    hex(x)

    • Description: Converts the parameter x to its hexadecimal string representation.

    • Input parameter data type: BIGINT or VARCHAR.

    • Return value data type: VARCHAR.

    • Examples:

      • The input value is of the BIGINT type. The statement is as follows:

        SELECT hex(16);

        The following result is returned:

        +---------+
| hex(16) |
+---------+
| 10      |
+---------+

      • The input value is of the VARCHAR type. The statement is as follows:

        SELECT hex('16');

        The following result is returned:

        +-----------+
| hex('16') |
+-----------+
| 3136      |
+-----------+

    INSTR

    instr(str, substr)

    • Description: Returns the position of the first occurrence of the substring substr within the string str.

    • Input parameter data type: str and substr are of the VARCHAR type.

    • Return value data type: BIGINT.

    • Example: 

      SELECT instr('foobarbar', 'bar');

      The following result is returned:

      +---------------------------+
| instr('foobarbar', 'bar') |
+---------------------------+
|                         4 |
+---------------------------+

    LEFT

    LEFT(str, len)

    • Description: Returns the len leftmost characters of the string str.

      If str or len is null, null is returned.

    • Input parameter data type: str is of the VARCHAR type, and len is of the BIGINT type.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT LEFT('foobarbar', 5);

      The following result is returned:

      +----------------------+
| LEFT('foobarbar', 5) |
+----------------------+
| fooba                |
+----------------------+

    LENGTH or OCTET_LENGTH

    length(str)
octet_length(str)

    • Description: Returns the length of the string str.

    • Input parameter data type: VARCHAR.

    • Return value data type: BIGINT.

    • Example: 

      SELECT length('aliyun');

      The following result is returned:

      +------------------+
| length('aliyun') |
+------------------+
|                6 |
+------------------+

    LIKE

    expression [NOT] LIKE pattern [ESCAPE 'escape_char']

    • Description: The LIKE operator matches the string expression with pattern. It returns 1 for a successful match and 0 otherwise. The parameters are described as follows:

      • pattern is a wildcard pattern. The wildcards include the following:

        • %: Matches a string of any length.

        • _: Matches a single character.

      • escape_char: Escapes the % and _ characters in pattern so that the % and _ characters that follow the escape character are not used as wildcards.

    • Input parameter data type: expression and pattern are of the VARCHAR type.

    • Return value data type: BIGINT.

    • Examples:

      • Statement:

        SELECT 'David!' LIKE 'David_' AS result1, 'David!' NOT LIKE 'David_' AS result2,  'David!' LIKE '%D%v%' AS result3;

        The following result is returned:

        +---------+---------+---------+
| result1 | result2 | result3 |
+---------+---------+---------+
|       1 |       0 |       1 |
+---------+---------+---------+

      • Statement:

        SELECT 'David_' LIKE 'David|_' ESCAPE '|';

        The following result is returned:

        +----------------------------------+
| David_' LIKE 'David|_' ESCAPE '| |
+----------------------------------+
|                                1 |
+----------------------------------+

    LOCATE

    locate(substr, str)
locate(substr, str, pos)

    • Description: Returns the position of the first occurrence of substr within the string str. Alternatively, it returns the position of the first occurrence of substr within str starting from position pos.

      If substr is not in str, 0 is returned.

      If substr or str is null, null is returned.

    • Input parameter data type: str and substr are of the VARCHAR type, and pos is of the BIGINT type.

    • Return value data type: BIGINT.

    • Examples:

      • Statement:

        SELECT locate('bar', 'foobarbar');

        The following result is returned:

        +----------------------------+
| locate('bar', 'foobarbar') |
+----------------------------+
|                          4 |
+----------------------------+

      • Statement:

        SELECT locate('bar', 'foobarbar', 7);

        The following result is returned:

        +-------------------------------+
| locate('bar', 'foobarbar', 7) |
+-------------------------------+
|                             7 |
+-------------------------------+

    LOWER or LCASE

    lower(str)
lcase(str)

    • Description: Converts letters in the string str to lowercase.

    • Return value type: VARCHAR.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT lower('Aliyun');

      The following result is returned:

      +-----------------+
| lower('Aliyun') |
+-----------------+
| aliyun          |
+-----------------+

    LPAD

    lpad(str, len, padstr)

    • Description: Left-pads the string str with padstr to a length of len characters and returns the padded string.

      If str is longer than len, the return value is shortened to len characters.

    • Input parameter data type: str and padstr are of the VARCHAR type, and len is of the BIGINT type.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT lpad('Aliyun',9,'#');

      The following result is returned:

      +----------------------+
| lpad('Aliyun',9,'#') |
+----------------------+
| ###Aliyun            |
+----------------------+

    LTRIM

    ltrim(str)

    • Description: Removes all leading spaces from the string str.

    • Input parameter data type: VARCHAR.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT ltrim('  abc');

      The following result is returned:

      +----------------+
| ltrim('  abc') |
+----------------+
| abc            |
+----------------+

    MAKE_SET

    make_set(bits, str 1, str 2,...);

    • Description: Returns a set value, which is a string containing substrings separated by characters. The set contains strings that have the corresponding bit set.

      str 1 corresponds to bit 0, str 2 corresponds to bit 1, and so on. null values in str 1, str 2, and so on are not appended to the result.

    • Input parameter data type: bits is of the BIGINT type, and str is of the VARCHAR type.

    • Return value data type: VARCHAR.

    • Examples:

      • Statement:

        SELECT make_set(5,'hello','nice','world');

        The following result is returned:

        +------------------------------------+
| make_set(5,'hello','nice','world') |
+------------------------------------+
| hello,world                        |
+------------------------------------+

      • Statement:

        SELECT make_set(1 | 4,'hello','nice',NULL,'world')AS result;

        The following result is returned:

        +--------+
| result |
+--------+
| hello  |
+--------+

    MD5_MUR

    MD5_MUR(x)

    • Description: Calculates the MD5 hash of the input parameter x and then converts the hash to a LONG hash value using the MurmurHash3 algorithm. This function has a lower collision rate than the CRC32 algorithm.

    • Input parameter data type: VARCHAR.

    • Return value data type: LONG.

    • Version requirement: Your cluster must be of kernel version 3.2.5 or later.

    • Example:

      • Statement:

        SELECT MD5_MUR('TEST') as result;

      • The following result is returned:

        +--------------------------+
| result |
+--------------------------+
| -6711128042951586494     |
+--------------------------+

    MID

    mid(str, pos, len)

    • Description: This function is equivalent to SUBSTR or SUBSTRING and returns a substring of length len from string str, starting at pos.

    • Input parameter data type: str is of the VARCHAR type, and pos and len are of the BIGINT type.

    • Return value data type: VARCHAR.

    • Examples:

      • Statement:

        SELECT mid('Quadratically',5,6);

        The following result is returned:

        +--------------------------+
| mid('Quadratically',5,6) |
+--------------------------+
| ratica                   |
+--------------------------+

      • Statement:

        SELECT mid('Sakila', -5, 3);

        The following result is returned:

        +----------------------+
| mid('Sakila', -5, 3) |
+----------------------+
| aki                  |
+----------------------+

    OCT

    oct(N)

    • Description: Returns the octal string representation of the integer N.

      If N is null, null is returned.

    • Input parameter data type: BIGINT.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT oct(12);

      The following result is returned:

      +---------+
| oct(12) |
+---------+
| 14      |
+---------+

    ORD

    ord(x)

    • Description: If the leftmost character of the string x is a multibyte character, this function returns the code of that character.

    • Input parameter data type: VARBINARY or VARCHAR.

    • Return value data type: LONG.

    • Examples:

      • The input parameter is of the VARCHAR type. The statement is as follows:

        SELECT ord('China');

        The following result is returned:

        +--------------+
| ord('China') |
+--------------+
|           67 |
+--------------+

      • The input parameter is of the VARBINARY type. The statement is as follows:

        SELECT ord(cast('China' AS varbinary));

        The following result is returned:

        +---------------------------------+
| ord(cast('China' AS varbinary)) |
+---------------------------------+
|                              67 |
+---------------------------------+

    POSITION

    position(substr IN str);

    • Description: Returns the position of the first occurrence of the substring substr within the string str. The position starts from 1. If the substring is not found, 0 is returned.

    • Input parameter data type: substr and str are of the VARCHAR type.

    • Return value data type: BIGINT.

    • Example: 

      SELECT position('bar' in 'foobarbar');

      The following result is returned:

      +--------------------------------+
| position('bar' in 'foobarbar') |
+--------------------------------+
|                              4 |
+--------------------------------+

    REPEAT

    repeat(str, count);

    • Description: Returns a string consisting of str repeated count times.

      If count<1, an empty string is returned.

      If str or count is null, null is returned.

    • Input parameter data type: str is of the VARCHAR type, and count is of the BIGINT type.

    • Return value data type: VARCHAR.

    • Examples:

      • Statement:

        SELECT repeat('a', 3);

        The following result is returned:

        +----------------+
| repeat('a', 3) |
+----------------+
| aaa            |
+----------------+

      • Statement:

        SELECT repeat('abc', null);

        The following result is returned:

        +---------------------+
| repeat('abc', null) |
+---------------------+
| NULL                |
+---------------------+

      • Statement:

        SELECT repeat(null, 3);

        The following result is returned:

        +-----------------+
| repeat(null, 3) |
+-----------------+
| NULL            |
+-----------------+

    REPLACE

    replace(str, from_str, to_str);

    • Description: Replaces all occurrences of from_str in str with to_str.

    • Input parameter data type: str, from_str, and to_str are of the VARCHAR type.

    • Return value data type: VARCHAR.

    • Example:

      SELECT replace('WWW.aliyun.com', 'W', 'w');

      The following result is returned:

      +-------------------------------------+
| replace('WWW.aliyun.com', 'W', 'w') |
+-------------------------------------+
| www.aliyun.com                      |
+-------------------------------------+

    REVERSE

    reverse(str);

    • Description: Returns the string str in reverse order.

    • Input parameter data type: VARCHAR.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT reverse('123456');

      The following result is returned:

      +-------------------+
| reverse('123456') |
+-------------------+
| 654321            |
+-------------------+

    RIGHT

    RIGHT(str, len);

    • Description: Returns the len rightmost characters of the string str.

      If str or len is null, null is returned.

    • Input parameter data type: str is of the VARCHAR type, and len is of the BIGINT type.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT RIGHT('abc',3);

      The following result is returned:

      +----------------+
| RIGHT('abc',3) |
+----------------+
| abc            |
+----------------+

    RLIKE or REGEXP

    expression RLIKE pattern;
expression REGEXP pattern;

    • Description: Returns 1 if the string expression matches the regular expression pattern, and 0 otherwise.

      If expression or pattern is null, null is returned.

    • Input parameter data type: expression and pattern are of the VARCHAR type.

    • Return value data type: BOOLEAN.

    • Examples:

      • Statement:

        SELECT 'Michael!' REGEXP '.*';

        The following result is returned:

        +----------------------+
| Michael!' REGEXP '.* |
+----------------------+
|                    1 |
+----------------------+

      • Statement:

        SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';

        The following result is returned:

        +-------------------------------------+
| new*\n*line' REGEXP 'new\\*.\\*line |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

      • Statement:

        SELECT 'c' REGEXP '^[a-d]';

        The following result is returned:

        +-------------------+
| c' REGEXP '^[a-d] |
+-------------------+
|                 1 |
+-------------------+

    RPAD

    rpad(str, len, padstr)

    • Description: Right-pads the string str with padstr to a length of len characters and returns the padded string.

      If str is longer than len, the return value is shortened to len characters.

    • Input parameter data type: str and padstr are of the VARCHAR type, and len is of the BIGINT type.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT rpad('Aliyun',9,'#');

      The following result is returned:

      +----------------------+
| rpad('Aliyun',9,'#') |
+----------------------+
| Aliyun###            |
+----------------------+

    RTRIM

    rtrim(str)

    • Description: Removes all trailing spaces from the string str.

    • Input parameter data type: VARCHAR.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT rtrim('barbar   ');

      The following result is returned:

      +--------------------+
| rtrim('barbar   ') |
+--------------------+
| barbar             |
+--------------------+

    SPACE

    space(N);

    • Description: Returns a string consisting of a specified number of spaces.

      Note

      You can use this function with the contact() function to display the results.

    • Input parameter data type: BIGINT.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT concat("#", space(6), "#");

      The following result is returned:

      +----------------------------+
| concat("#", space(6), "#") |
+----------------------------+
| #      #                   |
+----------------------------+

    SPLIT

    split(string, delimiter)

    • Description: Splits the string by the delimiter and returns an array.

    • Input parameter data type: string and delimiter are of the VARCHAR type.

    • Return value data type: ARRAY<varchar>.

    • Example:

      SELECT split('1#2#3', '#'), split('#1#2#3#', '#'),  split('123', '#');

      The following result is returned:

      +---------------------+-----------------------+-------------------+
| split('1#2#3', '#') | split('#1#2#3#', '#') | split('123', '#') |
+---------------------+-----------------------+-------------------+
| ["1","2","3"]       | ["","1","2","3",""]   | ["123"]           |
+---------------------+-----------------------+-------------------+

    SPLIT_PART

    split_part(string, delimiter, index)

    • Description: Splits the string by the delimiter and returns the substring at the array index index. The index starts from 1. If the index is greater than the number of fields, NULL is returned.

    • Input parameter data type: string and delimiter are of the VARCHAR type, and index is of the BIGINT type.

    • Return value data type: VARCHAR.

    • Example:

      SELECT split_part('A#B#C', '#', 2), split_part('A#B#C', '#', 4);

      The following result is returned:

      +-----------------------------+-----------------------------+
| split_part('A#B#C', '#', 2) | split_part('A#B#C', '#', 4) |
+-----------------------------+-----------------------------+
| B                           | NULL                        |
+-----------------------------+-----------------------------+

    SPLIT_TO_MAP

    split_to_map(string, entryDelimiter, keyValueDelimiter)

    • Description: Splits a string using an entryDelimiter and a keyValueDelimiter, then returns a map. entryDelimiter splits the string into key-value pairs. keyValueDelimiter splits each key-value pair into a key and a value.

    • Input parameter data type: string, entryDelimiter, and keyValueDelimiter are of the VARCHAR type.

    • Return value data type: MAP<varchar, varchar>.

    • Example:

      SELECT split_to_map('k1:v1,k2:v2', ',', ':'),split_to_map('', ',', ':');

      The following result is returned:

      +---------------------------------------+----------------------------+
| split_to_map('k1:v1,k2:v2', ',', ':') | split_to_map('', ',', ':') |
+---------------------------------------+----------------------------+
| {"k1":"v1","k2":"v2"}                 | {}                         |
+---------------------------------------+----------------------------+

    STRCMP

    strcmp(str 1, str 2);

    • Description: If the strings str 1 and str 2 are identical, the function returns 0. If str 1 is less than str 2 according to the current sort order, it returns -1. Otherwise, it returns 1.

    • Input parameter data type: str 1 and str 2 are of the VARCHAR type.

    • Return value data type: BIGINT.

    • Example: 

      SELECT strcmp('text', 'text2');

      The following result is returned:

      +-------------------------+
| strcmp('text', 'text2') |
+-------------------------+
|                      -1 |
+-------------------------+

    SUBSTR or SUBSTRING

    substr(str, pos)
substr(str FROM pos)
substr(str, pos, len)
substr(str FROM pos FOR len)
substring(str, pos)
substring(str FROM pos)
substring(str, pos, len)
substring(str FROM pos FOR len)

    • Description:

      • SUBSTRING(varchar str, bigint pos) and SUBSTRING(varchar str FROM pos) return a substring from position pos to the end of the string. If pos<0, the starting position is counted backwards from the end of the string.

      • SUBSTRING(varchar str, bigint pos, bigint len) and SUBSTRING(varchar str FROM pos FOR len) return a substring of length len from position pos. If pos<0, the starting position is counted backwards from the end of the string.

    • Input parameter data type: str is of the VARCHAR type, and pos and len are of the BIGINT type.

    • Return value data type: VARCHAR.

    • Examples:

      • Statement:

        SELECT substr('helloworld', 6);

        The following result is returned:

        +-------------------------+
| substr('helloworld', 6) |
+-------------------------+
| world                   |
+-------------------------+

      • Statement:

        SELECT substr('helloworld' FROM 6);

        The following result is returned:

        +-----------------------------+
| substr('helloworld' FROM 6) |
+-----------------------------+
| world                       |
+-----------------------------+

      • Statement:

        SELECT substr('helloworld', 6, 3);

        The following result is returned:

        +----------------------------+
| substr('helloworld', 6, 3) |
+----------------------------+
| wor                        |
+----------------------------+

      • Statement:

        SELECT substr('helloworld' from 6 for 3);

        The following result is returned:

        +-----------------------------------+
| substr('helloworld' FROM 6 FOR 3) |
+-----------------------------------+
| wor                               |
+-----------------------------------+

    SUBSTRING_INDEX

    substring_index(str, delim, count)

    • Description: Returns a substring from the string str that is before or after the count-th occurrence of the delimiter delim.

      If count>0, the function returns everything to the left of the count-th occurrence of the delimiter (delim). Counting starts from the left.

      If count<0, everything to the right of the count-th delimiter (delim) is returned, with the count starting from the right.

      The SUBSTRING_INDEX function performs a case-sensitive search for delim.

    • Input parameter data type: str and delim are of the VARCHAR type, and count is of the BIGINT type.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT substring_index('www.aliyun.com', '.', 2);

      The following result is returned:

      +-------------------------------------------+
| substring_index('www.aliyun.com', '.', 2) |
+-------------------------------------------+
| www.aliyun                                |
+-------------------------------------------+

    TO_BASE64

    to_base64(x)

    • Description: Returns the Base64-encoded form of the parameter x.

    • Input parameter data type: VARBINARY or VARCHAR.

    • Return value data type: VARCHAR.

    • Examples:

      • The input parameter is of the VARCHAR type. The statement is as follows:

        SELECT to_base64('China');

        The following result is returned:

        +--------------------+
| to_base64('China') |
+--------------------+
| Q2hpbmE=           |
+--------------------+

      • The input parameter is of the VARBINARY type. The statement is as follows:

        SELECT to_base64(cast('China' AS varbinary));

        The following result is returned:

        +---------------------------------------+
| to_base64(cast('China' AS varbinary)) |
+---------------------------------------+
| Q2hpbmE=                              |
+---------------------------------------+

    TO_UTF8

    to_utf8(x)

    • Description: Returns the UTF-8 encoded form of the parameter x.

    • Input parameter data type: VARCHAR.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT from_utf8(to_utf8('China'));

      The following result is returned:

      +-----------------------------+
| from_utf8(to_utf8('China')) |
+-----------------------------+
| China                       |
+-----------------------------+

    TRIM

    trim([remstr FROM] str)
trim([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

    • Description: Trims the string str by removing leading and trailing spaces, or by removing characters that match the optional remstr string.

    • Input parameter data type: VARCHAR.

    • Return value data type: VARCHAR.

    • Examples:

      • Statement:

        SELECT trim('  bar   ');

        The following result is returned:

        +------------------+
| trim('  bar   ') |
+------------------+
| bar              |
+------------------+

      • Statement:

        SELECT trim(BOTH 'x' FROM 'xxxbarxxx');

        The following result is returned:

        +---------------------------------+
| trim(BOTH 'x' FROM 'xxxbarxxx') |
+---------------------------------+
| bar                             |
+---------------------------------+

      • Statement:

        SELECT trim(LEADING 'x' FROM 'xxxbarxxx');

        The following result is returned:

        +------------------------------------+
| trim(LEADING 'x' FROM 'xxxbarxxx') |
+------------------------------------+
| barxxx                             |
+------------------------------------+

      • Statement:

        SELECT trim(TRAILING 'x' from 'xxxbarxxx');

        The following result is returned:

        +-------------------------------------+
| trim(TRAILING 'x' from 'xxxbarxxx') |
+-------------------------------------+
| xxxbar                              |
+-------------------------------------+

    UPPER or UCASE

    upper(str)
ucase(str)

    • Description: Converts letters in the string str to uppercase.

    • Input parameter data type: VARCHAR.

    • Return value data type: VARCHAR.

    • Example: 

      SELECT upper('Aliyun');

      The following result is returned:

      +-----------------+
| upper('Aliyun') |
+-----------------+
| ALIYUN          |

    UNHEX

    unhex(x);

    • Description: Interprets each pair of hexadecimal digits in the parameter x as a number and converts it to the character that the number represents.

    • Input parameter data type: VARBINARY or VARCHAR.

    • Return value data type: VARBINARY.

      Note

      • The decoded return value is of the VARBINARY type. To convert the result to the VARCHAR type, use one of the following methods:

        • For AnalyticDB for MySQL clusters of V3.1.4 or later, use the CAST AS VARCHAR function. For more information, see CAST functions.

        • For AnalyticDB for MySQL clusters earlier than V3.1.4, you can use the FROM_UTF8 function to convert data types. For more information, see FROM_UTF8.

      • If the input value for UNHEX contains any non-hexadecimal digits, NULL is returned.

    • Examples:

      • The input parameter is of the VARCHAR type. The statement is as follows:

        SELECT unhex(hex('China'));

        The following result is returned:

        +------------------------------------------+
| unhex(hex('China'))                      |
+------------------------------------------+
| China                                    |
+------------------------------------------+
        Note

        The return value of the preceding statement is of the VARBINARY type. To obtain the original VARCHAR value, run the following statement:

        SELECT cast(unhex(hex('China')) AS varchar);

        The following result is returned:

        +--------------------------------------+
| cast(unhex(hex('China')) AS varchar) |
+--------------------------------------+
| China                                |
+--------------------------------------+

      • The input parameter is of the VARBINARY type. The statement is as follows:

        SELECT unhex(cast(hex('China') AS varbinary));

        The following result is returned:

        +------------------------------------------+
| unhex(cast(hex('China') AS varbinary))   |
+------------------------------------------+
| China                                    |
+------------------------------------------+