This topic describes the string functions supported by AnalyticDB for MySQL.

  • ASCII: returns the ASCII value of a character or of the leftmost character of a string.
  • BIN: returns the binary string of an integer.
  • BIT_LENGTH: returns the length of a string, measured in bits.
  • CHAR: returns the string of the ASCII values of integers.
  • CHAR_LENGTH or CHARACTER_LENGTH: returns the length of a string, measured in characters.
  • CONCAT: concatenates strings.
  • CONCAT_WS: concatenates strings and separates them with delimiters.
  • ELT: returns the string specified by the integer N.
  • ENCRYPT: encrypts a string.
  • EXPORT_SET: returns a string that represents the bits in an integer.
  • FIELD: returns the index position of a specific string in the string list.
  • FIND_IN_SET: returns the position of a character or string in another string.
  • FORMAT: formats the number N and returns a string.
  • FROM_BASE64: decodes a Base64-encoded string and returns the result.
  • FROM_UTF8: decodes a UTF-8 encoded string and returns the result.
  • HEX: converts an integer or a string into a hexadecimal string.
  • INSTR: returns the position of the first occurrence of a substring in 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 string in another string.
  • LOWER or LCASE: converts a string to lowercase.
  • LPAD: returns a string that is left-padded with another string.
  • LTRIM: removes the leading spaces of a string.
  • MAKE_SET: returns a set of comma-separated strings.
  • MID: returns a substring of a specific length, which starts from a specific position in a string. This function serves the same purpose as SUBSTR or SUBSTRING.
  • OCT: returns the octal string of an integer.
  • ORD: returns the code of the leftmost character of a string if the character is a multibyte character.
  • POSITION: returns the position of the first occurrence of a substring in a string.
  • REPEAT: returns a string that consists of the source string concatenated to itself a specific number of times.
  • REPLACE: replaces specific characters in a string with another string.
  • REVERSE: reverses the characters in a string.
  • RIGHT: returns the N rightmost characters of a string.
  • RLIKE or REGEXP: performs pattern matching of a string against a regular expression. If the string matches the regular expression, 1 is returned. Otherwise, 0 is returned.
  • RPAD: returns a string that is right-padded with another string.
  • RTRIM: removes the trailing spaces of a string.
  • SPACE: returns a string that consists of a specific number of spaces.
  • SPLIT: splits a string with delimiters and returns an array.
  • SPLIT_PART: splits a string with delimiters and returns a specific substring in the array.
  • SPLIT_TO_MAP: splits a string by using entryDelimiter and keyValueDelimiter and returns a map.
  • STRCMP: returns 0, 1, or -1 based on the comparison results of two strings.
  • SUBSTR or SUBSTRING: returns a substring of a specific length that starts from a specific position.
  • SUBSTRING_INDEX: returns the substring before the last occurrence of the delimiter in the string.
  • TO_BASE64: returns a Base64-encoded string.
  • TO_UTF8: returns a UTF-8 encoded string.
  • TRIM: removes the leading and trailing spaces of a string.
  • UPPER or UCASE: converts a string to uppercase.
  • UNHEX: interprets each pair of hexadecimal digits in the argument as a number and converts it to a character.

ASCII

ascii(str)
  • Description: This function returns the decimal ASCII value of the str character or of the leftmost character of the str string.
  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Examples:
    • Sample statement:
      SELECT ascii('2');
      Result:
      +------------+
| ascii('2') |
+------------+
|         50 |
+------------+
    • Sample statement:
      SELECT ascii('dx');
      Result:
      +-------------+
| ascii('dx') |
+-------------+
|         100 |
+-------------+

BIN

bin(N)
  • Description: This function returns the binary string of N.

    If N is null, NULL is returned.

  • Input value type: BIGINT.
  • Return value type: VARCHAR.
  • Example: 
    SELECT bin(12);
    Result:
    +---------+
| bin(12) |
+---------+
| 1100    |
+---------+

BIT_LENGTH

bit_length(str)
  • Description: This function returns the length of the str string, measured in bits.
  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Examples:
    • Sample statement:
      SELECT bit_length('text');
      Result:
      +--------------------+
| bit_length('text') |
+--------------------+
|                 32 |
+--------------------+
    • Sample statement:
      SELECT bit_length('China');
      Result:
      +---------------------+
| bit_length('China') |
+---------------------+
|                  40 |
+---------------------+

CHAR

char(N1, N2, ...)
  • Description: This function returns the string that consists of the decimal ASCII values of the specified integers.
  • Input value type: BIGINT.
  • Return value type: VARBINARY.
  • Example: 
    SELECT char(97,110,97,108,121,116,105,99,100,98);
    Result:
    +-------------------------------------------+
| char(97,110,97,108,121,116,105,99,100,98) |
+-------------------------------------------+
| analyticdb                                |
+-------------------------------------------+

CHAR_LENGTH or CHARACTER_LENGTH

char_length(str)
character_length(str)
  • Description: This function returns the length of the str string, measured in characters.

    The length of a Chinese character is 1.

  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Examples:
    • Sample statement:
      SELECT char_length('China');
      Result:
      +----------------------+
| char_length('China') |
+----------------------+
|                    5 |
+----------------------+
    • Sample statement:
      SELECT char_length('abc');
      Result:
      +--------------------+
| char_length('abc') |
+--------------------+
|                  3 |
+--------------------+

CONCAT

concat(str 1, …, str n)
  • Description: This function concatenates strings. If an argument is null, null is returned.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Examples:
    • Sample statement:
      SELECT concat('aliyun', ', ', 'analyticdb');
      Result:
      +--------------------------------------+
| concat('aliyun', ', ', 'analyticdb') |
+--------------------------------------+
| aliyun, analyticdb                   |
+--------------------------------------+
    • Sample statement:
      SELECT concat('abc',null,'def');
      Result:
      +--------------------------+
| concat('abc',null,'def') |
+--------------------------+
| NULL                     |
+--------------------------+

CONCAT_WS

concat_ws(separator, str 1, …, str n)
  • Description: This function concatenates strings and separates them with delimiters. The separator argument specifies the delimiter for other arguments. null strings are skipped.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Examples:
    • Sample statement:
      SELECT concat_ws(',', 'First name', 'Second name', 'Last Name')AS result;
      Result:
      +----------------------------------+
| result                           |
+----------------------------------+
| First name,Second name,Last Name |
+----------------------------------+
    • Sample statement:
      SELECT concat_ws(',','First name',NULL,'Last Name')AS result;
      Result:
      +----------------------+
| result               |
+----------------------+
| First name,Last Name |
+----------------------+

ELT

elt(N, str 1, ...,str n);
  • Description: This function returns the Nth string.

    If N is less than 1 or is greater than the number of string arguments, null is returned.

  • Input value type: N is of the BIGINT type, and str is of the VARCHAR type.
  • Return value type: VARCHAR.
  • Example: 
    SELECT elt(4, 'Aa', 'Bb', 'Cc', 'Dd');
    Result:
    +--------------------------------+
| elt(4, 'Aa', 'Bb', 'Cc', 'Dd') |
+--------------------------------+
| Dd                             |
+--------------------------------+

ENCRYPT

encrypt(x, y);
  • Description: This function encrypts the x argument by using y as the salt value.
  • Input value type: x is of the VARBINARY type, and y is of the VARCHAR type.
  • Return value type: VARBINARY.
  • Example: 
    SELECT encrypt('abdABC123','key');
    Result:
    +--------------------------------------------------------+
| encrypt('abdABC123','key')                             |
+--------------------------------------------------------+
| 0x6B657A617A6D63496F2E614377                           |
+--------------------------------------------------------+

EXPORT_SET

export_set(bits, onstr, offstr [, separator[,number_of_bits]]);
  • Description: This function converts bits into a binary value. Argument description:
    • The system replaces 1 in the binary value with the onstr value and 0 with the offstr value.
    • The returned values are separated by the separator value.
    • number_of_bits specifies the number of bits that the system scans from right to left. If the number_of_bits value is greater than 64 or is -1, 64 bits are returned.
  • Input value type: bits and number_of_bits are of the BIGINT type, and onstr, offstr, and separator are of the VARCHAR type.
  • Return value type: VARCHAR.
  • Examples:
    • In the following statement, 5 is converted into a binary value. The two rightmost bits of the binary value are returned. 1 is replaced with a, and 0 is replaced with b. The returned values are separated by commas (,).
      SELECT export_set(5,'a','b',',',2);
      Result:
      +-----------------------------+
| export_set(5,'a','b',',',2) |
+-----------------------------+
| a,b                         |
+-----------------------------+
    • In the following statement, 6 is converted into a binary value. The two rightmost bits of the binary value are returned. 1 and 0 are not replaced with other values. The returned values are separated by commas (,).
      SELECT export_set(6,'1','0',',',10);
      Result:
      +------------------------------+
| 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: This function returns the index position of the str value in several strings. If the str value is not found among the strings, 0 is returned.
  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Example: 
    SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    Result:
    +-------------------------------------------+
| FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff') |
+-------------------------------------------+
|                                         2 |
+-------------------------------------------+

FIND_IN_SET

find_in_set(str, strlist)
  • Description: This function returns the position of the str value in the strlist list.

    If the str value is not found in strlist or if strlist is an empty list, 0 is returned.

    If str or strlist is null, null is returned.

  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Example: 
    SELECT find_in_set('b','a,b,c,d');
    Result:
    +----------------------------+
| find_in_set('b','a,b,c,d') |
+----------------------------+
|                          2 |
+----------------------------+

FORMAT

format(X, D)
  • This function formats the X number to the #,###,###.## format rounded to D decimal places and returns the result as a string.

    If D is 0, the result does not have the decimal point or fractional part.

  • Input value type: X is of the DOUBLE type, and D is of the BIGINT type.
  • Return value type: VARCHAR.
  • Example: 
    SELECT format(12332.123456, 4)AS result1, format(12332.1,4)AS result2, format(12332.2,0)AS result3;
    Result:
    +-------------+-------------+---------+
| result1     | result2     | result3 |
+-------------+-------------+---------+
| 12,332.1235 | 12,332.1000 | 12,332  |
+-------------+-------------+---------+

FROM_BASE64

from_base64(x)
  • Description: This function decodes the Base64-encoded x string and returns the result.
  • Input value type: VARBINARY or VARCHAR.
  • Return value type: VARBINARY.
    Note You can use the following methods to convert the VARBINARY-typed result into a VARCHAR value.
    • If the AnalyticDB for MySQL cluster version is 3.1.4 or later, you can use the CAST AS VARCHAR function. For more information, see CAST functions.
    • If the AnalyticDB for MySQL cluster version is earlier than 3.1.4, you can use the FROM_UTF8 function. For more information, see FROM_UTF8.
  • Examples:
    • In the following statement, a VARCHAR-typed value is specified:
      SELECT from_base64('Q2hpbmE=');
      Result:
      +--------------------------------------------------+
| from_base64('Q2hpbmE=')                          |
+--------------------------------------------------+
| 0x4368696E61                                     |
+--------------------------------------------------+
      Note The preceding returned value is of the VARBINARY type. If you want the system to return a VARCHAR-typed value, you can use the following statement:
      SELECT cast(from_base64('Q2hpbmE=') AS varchar);
      Result:
      +------------------------------------------+
| cast(from_base64('Q2hpbmE=') AS varchar) |
+------------------------------------------+
| China                                    |
+------------------------------------------+
    • In the following statement, a VARBINARY-typed value is specified:
      SELECT from_base64(cast(to_base64('China') AS varbinary));
      Result:
      +--------------------------------------------------------------------------------------------------------+
| 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 value and returns the result.
    • from_utf8(x, y): decodes the x value that is not UTF-8 encoded and replaces it with a specific invalid character.
      Note
      • y is not required. If you do not specify y, � is returned.
      • The y value can be an invalid character, such as a number sign (#), or the ASCII value of the invalid character, such as 35.
  • Input value type: x is of the VARBINARY type, and y is of the VARCHAR or BIGINT type.
  • Return value type: VARCHAR.
  • Examples:
    • In the following statement, a UTF-8 encoded argument value is decoded:
      SELECT from_utf8(to_utf8('hello'));
      Result:
      +-----------------------------+
| from_utf8(to_utf8('hello')) |
+-----------------------------+
| hello                       |
+-----------------------------+
    • In the following statement, an argument value that is not UTF-8 encoded is decoded:
      SELECT from_utf8(unhex('58BF'));
      Result:
      +--------------------------+
| from_utf8(unhex('58BF')) |
+--------------------------+
| X�                       |
+--------------------------+
    • In the following statement, an argument value that is not UTF-8 encoded is decoded and replaced with a number sign (#):
      SELECT from_utf8(unhex('58BF'), '#');
      Result:
      +-------------------------------+
| from_utf8(unhex('58BF'), '#') |
+-------------------------------+
| X#                            |
+-------------------------------+
    • In the following statement, an argument value that is not UTF-8 encoded is decoded and replaced with an invalid character whose ASCII value is 35:
      SELECT from_utf8(unhex('58BF'), '35');
      Result:
      +-------------------------------+
| from_utf8(unhex('58BF'), '35') |
+-------------------------------+
| X#                            |
+-------------------------------+

HEX

hex(x)
  • Description: This function converts the x value into a hexadecimal string.
  • Input value type: BIGINT or VARCHAR.
  • Return value type: VARCHAR.
  • Examples:
    • In the following statement, a BIGINT-typed value is specified:
      SELECT hex(16);
      Result:
      +---------+
| hex(16) |
+---------+
| 10      |
+---------+
    • In the following statement, a VARCHAR-typed value is specified:
      SELECT hex('16');
      Result:
      +-----------+
| hex('16') |
+-----------+
| 3136      |
+-----------+

INSTR

instr(str, substr)
  • Description: This function returns the position of the first occurrence of the substr substring in the str string.
  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Example: 
    SELECT instr('foobarbar', 'bar');
    Result:
    +---------------------------+
| instr('foobarbar', 'bar') |
+---------------------------+
|                         4 |
+---------------------------+

LEFT

LEFT(str, len)
  • Description: This function returns a number of leftmost characters of the str string. The number is specified by the len argument.

    If str or len is null, null is returned.

  • Input value type: str is of the VARCHAR type, and len is of the BIGINT type.
  • Return value type: VARCHAR.
  • Example: 
    SELECT LEFT('foobarbar', 5);
    Result:
    +----------------------+
| LEFT('foobarbar', 5) |
+----------------------+
| fooba                |
+----------------------+

LENGTH or OCTET_LENGTH

length(str)
octet_length(str)
  • Description: This function returns the length of the str string.
  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Example: 
    SELECT length('aliyun');
    Result:
    +------------------+
| length('aliyun') |
+------------------+
|                6 |
+------------------+

LIKE

expression [NOT] LIKE pattern [ESCAPE 'escape_char']
  • Description: This function compares the values of expression and pattern. If the values are the same, 1 is returned. Otherwise, 0 is returned. Argument description:
    • The pattern value can contain the following wildcards:
      • %: matches a string of any length.
      • _: matches a single character.
    • escape_char: escapes the percent signs (%) and underscores (_) in the pattern value so that the percent signs (%) and underscores (_) that follow the escape character do not serve as wildcards.
  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Examples:
    • Sample statement:
      SELECT 'David!' LIKE 'David_' AS result1, 'David!' NOT LIKE 'David_' AS result2,  'David!' LIKE '%D%v%' AS result3;
      Result:
      +---------+---------+---------+
| result1 | result2 | result3 |
+---------+---------+---------+
|       1 |       0 |       1 |
+---------+---------+---------+
    • Sample statement:
      SELECT 'David_' LIKE 'David|_' ESCAPE '|';
      Result:
      +----------------------------------+
| David_' LIKE 'David|_' ESCAPE '| |
+----------------------------------+
|                                1 |
+----------------------------------+

LOCATE

locate(substr, str)
locate(substr, str, pos)
  • Description: This function returns the position of the first occurrence of the substr substring in the str string, or returns the position of the first occurrence of the substr substring in the str string, starting from the pos position.

    If the substr substring is not found in the str string,0 is returned.

    If substr or str is null, null is returned.

  • Input value type: str and substr are of the VARCHAR type, and pos is of the BIGINT type.
  • Return value type: BIGINT.
  • Examples:
    • Sample statement:
      SELECT locate('bar', 'foobarbar');
      Result:
      +----------------------------+
| locate('bar', 'foobarbar') |
+----------------------------+
|                          4 |
+----------------------------+
    • Sample statement:
      SELECT locate('bar', 'foobarbar', 7);
      Result:
      +-------------------------------+
| locate('bar', 'foobarbar', 7) |
+-------------------------------+
|                             7 |
+-------------------------------+

LOWER or LCASE

lower(str)
lcase(str)
  • Description: This function converts letters in the str string to lowercase.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Example: 
    SELECT lower('Aliyun');
    Result:
    +-----------------+
| lower('Aliyun') |
+-----------------+
| aliyun          |
+-----------------+

LPAD

lpad(str, len, padstr)
  • Description: This function returns the str string that is left-padded with the padstr string to a length of len characters.

    If the length of the str string is greater than len characters, the return value is shortened to len characters.

  • Input value type: str and padstr are of the VARCHAR type, and len is of the BIGINT type.
  • Return value type: VARCHAR.
  • Example: 
    SELECT lpad('Aliyun',9,'#');
    Result:
    +----------------------+
| lpad('Aliyun',9,'#') |
+----------------------+
| ###Aliyun            |
+----------------------+

LTRIM

ltrim(str)
  • Description: This function removes the leading spaces of the str string.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Example: 
    SELECT ltrim('  abc');
    Result:
    +----------------+
| ltrim('  abc') |
+----------------+
| abc            |
+----------------+

MAKE_SET

make_set(bits, str 1, str 2,...);
  • Description: This function returns a set value, which is a string that contains substrings separated by delimiters. The set value consists of strings that have the corresponding bits within the bit set.

    The str1 string corresponds to bit 0. The str2 string corresponds to bit 1. The rest may be deduced by analogy. The null values in the str1, str2, and other strings are not appended to the result.

  • Input value type: bits is of the BIGINT type, and str is of the VARCHAR type.
  • Return value type: VARCHAR.
  • Examples:
    • Sample statement:
      SELECT make_set(5,'hello','nice','world');
      Result:
      +------------------------------------+
| make_set(5,'hello','nice','world') |
+------------------------------------+
| hello,world                        |
+------------------------------------+
    • Sample statement:
      SELECT make_set(1 | 4,'hello','nice',NULL,'world')AS result;
      Result:
      +--------+
| result |
+--------+
| hello  |
+--------+

MID

mid(str, pos, len)
  • Description: This function serves the same purpose as SUBSTR or SUBSTRING. It returns a substring that contains len characters in length from the str string, starting from the pos position.
  • Input value type: str is of the VARCHAR type, and pos and len are of the BIGINT type.
  • Return value type: VARCHAR.
  • Examples:
    • Sample statement:
      SELECT mid('Quadratically',5,6);
      Result:
      +--------------------------+
| mid('Quadratically',5,6) |
+--------------------------+
| ratica                   |
+--------------------------+
    • Sample statement:
      SELECT mid('Sakila', -5, 3);
      Result:
      +----------------------+
| mid('Sakila', -5, 3) |
+----------------------+
| aki                  |
+----------------------+

OCT

oct(N)
  • Description: This function returns the octal string of the integer N.

    If N is null, null is returned.

  • Input value type: BIGINT.
  • Return value type: VARCHAR.
  • Example: 
    SELECT oct(12);
    Result:
    +---------+
| oct(12) |
+---------+
| 14      |
+---------+

ORD

ord(x)
  • Description: This function returns the code of the leftmost character of the x string if the character is a multibyte character.
  • Input value type: VARBINARY or VARCHAR.
  • Return value type: LONG.
  • Examples:
    • In the following statement, a VARCHAR-typed value is specified:
      SELECT ord('China');
      Result:
      +--------------+
| ord('China') |
+--------------+
|           67 |
+--------------+
    • In the following statement, a VARBINARY-typed value is specified:
      SELECT ord(cast('China' AS varbinary));
      Result:
      +---------------------------------+
| ord(cast('China' AS varbinary)) |
+---------------------------------+
|                              67 |
+---------------------------------+

POSITION

position(substr IN str);
  • Description: This function returns the position of the first occurrence of the substr substring in the str string, starting from position 1. If the substring is not found in the str string, 0 is returned.
  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Example: 
    SELECT position('bar' in 'foobarbar');
    Result:
    +--------------------------------+
| position('bar' in 'foobarbar') |
+--------------------------------+
|                              4 |
+--------------------------------+

REPEAT

repeat(str, count);
  • Description: This function returns a string that consists of the str string concatenated to itself the number of times specified by count.

    If the count value is less than 1, an empty string is returned.

    If str or count is null, null is returned.

  • Input value type: str is of the VARCHAR type, and count is of the BIGINT type.
  • Return value type: VARCHAR.
  • Examples:
    • Sample statement:
      SELECT repeat('a', 3);
      Result:
      +----------------+
| repeat('a', 3) |
+----------------+
| aaa            |
+----------------+
    • Sample statement:
      SELECT repeat('abc', null);
      Result:
      +---------------------+
| repeat('abc', null) |
+---------------------+
| NULL                |
+---------------------+
    • Sample statement:
      SELECT repeat(null, 3);
      Result:
      +-----------------+
| repeat(null, 3) |
+-----------------+
| NULL            |
+-----------------+

REPLACE

replace(str, from_str, to_str);
  • Description: This function replaces all from_str strings in the str string with the to_str string.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.

  • Example:

    SELECT replace('WWW.aliyun.com', 'W', 'w');
    Result:
    +-------------------------------------+
| replace('WWW.aliyun.com', 'W', 'w') |
+-------------------------------------+
| www.aliyun.com                      |
+-------------------------------------+

REVERSE

reverse(str);
  • Description: This function returns the str string with the order of the characters reversed.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Example: 
    SELECT reverse('123456');
    Result:
    +-------------------+
| reverse('123456') |
+-------------------+
| 654321            |
+-------------------+

RIGHT

RIGHT(str, len);
  • Description: This function returns the rightmost len characters of the str string.

    If str or len is null, null is returned.

  • Input value type: str is of the VARCHAR type, and len is of the BIGINT type.
  • Return value type: VARCHAR.
  • Example: 
    SELECT RIGHT('abc',3);
    Result:
    +----------------+
| RIGHT('abc',3) |
+----------------+
| abc            |
+----------------+

RLIKE or REGEXP

expression RLIKE pattern;
expression REGEXP pattern;
  • Description: This function performs pattern matching of the expression string against the regular expression specified by pattern. If the string matches the regular expression, 1 is returned. Otherwise, 0 is returned.

    If expression or pattern is null, null is returned.

  • Input value type: VARCHAR.
  • Return value type: BOOLEAN.
  • Examples:
    • Sample statement:
      SELECT 'Michael!' REGEXP '.*';
      Result:
      +----------------------+
| Michael!' REGEXP '.* |
+----------------------+
|                    1 |
+----------------------+
    • Sample statement:
      SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
      Result:
      +-------------------------------------+
| new*\n*line' REGEXP 'new\\*.\\*line |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
    • Sample statement:
      SELECT 'c' REGEXP '^[a-d]';
      Result:
      +-------------------+
| c' REGEXP '^[a-d] |
+-------------------+
|                 1 |
+-------------------+

RPAD

rpad(str, len, padstr)
  • Description: This function returns the str string that is right-padded with the padstr string to a length of len characters.

    If the length of the str string is greater than len characters, the return value is shortened to len characters.

  • Input value type: str and padstr are of the VARCHAR type, and len is of the BIGINT type.
  • Return value type: VARCHAR.
  • Example: 
    SELECT rpad('Aliyun',9,'#');
    Result:
    +----------------------+
| rpad('Aliyun',9,'#') |
+----------------------+
| Aliyun###            |
+----------------------+

RTRIM

rtrim(str)
  • Description: This function removes the trailing spaces of the str string.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Example: 
    SELECT rtrim('barbar   ');
    Result:
    +--------------------+
| rtrim('barbar   ') |
+--------------------+
| barbar             |
+--------------------+

SPACE

space(N);
  • Description: This function returns a string that consists of a specific number of spaces.
    Note We recommend that you use this function together with the contact() function to facilitate display of results.
  • Input value type: BIGINT.
  • Return value type: VARCHAR.
  • Example: 
    SELECT concat("#", space(6), "#");
    Result:
    +----------------------------+
| concat("#", space(6), "#") |
+----------------------------+
| #      #                   |
+----------------------------+

SPLIT

split(string, delimiter)
  • Description: This function splits the string with the delimiter and returns an array.
  • Input value type: VARCHAR.
  • Return value type: ARRAY<varchar>.
  • Example:
    SELECT split('1#2#3', '#'), split('#1#2#3#', '#'),  split('123', '#');
    Result:
    +---------------------+-----------------------+-------------------+
| 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: This function splits the string with the delimiter and returns the substring whose array subscript is the index value. The index value starts from 1. If the value of index is greater than the number of substrings, NULL is returned.
  • Input value type: string and delimiter are of the VARCHAR type, and index is of the BIGINT type.
  • Return value type: VARCHAR.
  • Example:
    SELECT split_part('A#B#C', '#', 2), split_part('A#B#C', '#', 4);
    Result:
    +-----------------------------+-----------------------------+
| split_part('A#B#C', '#', 2) | split_part('A#B#C', '#', 4) |
+-----------------------------+-----------------------------+
| B                           | NULL                        |
+-----------------------------+-----------------------------+

SPLIT_TO_MAP

split_to_map(string, entryDelimiter, keyValueDelimiter)
  • Description: This function splits a string by using entryDelimiter and keyValueDelimiter and returns a map. The string is split by entryDelimiter into key-value pairs, and the key-value pairs are split by keyValueDelimiter into keys and values.
  • Input value type: VARCHAR.
  • Return value type: MAP<varchar, varchar>.
  • Example:
    SELECT split_to_map('k1:v1,k2:v2', ',', ':'),split_to_map('', ',', ':');
    Result:
    +---------------------------------------+----------------------------+
| split_to_map('k1:v1,k2:v2', ',', ':') | split_to_map('', ',', ':') |
+---------------------------------------+----------------------------+
| {"k1":"v1","k2":"v2"}                 | {}                         |
+---------------------------------------+----------------------------+

STRCMP

strcmp(str 1, str 2);
  • Description: This function compares the str 1 and str 2 strings. If they are identical, 0 is returned. If the str 1 value is smaller than the str2 value, -1 is returned. Otherwise, 1 is returned.
  • Input value type: VARCHAR.
  • Return value type: BIGINT.
  • Example: 
    SELCT strcmp('text', 'text2');
    Result:
    +-------------------------+
| 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) or SUBSTRING(varchar str FROM pos): returns the substring that starts from the pos position to the end of the string. If the pos value is less than 0, the substring starts from the position that is pos characters away from the end of the string.
    • SUBSTRING(varchar str, bigint pos, bigint len) or SUBSTRING(varchar str FROM pos FOR len): returns a substring that contains len characters in length from the string, starting from the pos position. If the pos value is less than 0, the substring starts from the position that is pos characters away from the end of the string.
  • Input value type: str is of the VARCHAR type, and pos and len are of the BIGINT type.
  • Return value type: VARCHAR.
  • Examples:
    • Sample statement:
      SELECT substr('helloworld', 6);
      Result:
      +-------------------------+
| substr('helloworld', 6) |
+-------------------------+
| world                   |
+-------------------------+
    • Sample statement:
      SELECT substr('helloworld' FROM 6);
      Result:
      +-----------------------------+
| substr('helloworld' FROM 6) |
+-----------------------------+
| world                       |
+-----------------------------+
    • Sample statement:
      SELECT substr('helloworld', 6, 3);
      Result:
      +----------------------------+
| substr('helloworld', 6, 3) |
+----------------------------+
| wor                        |
+----------------------------+
    • Sample statement:
      SELECT substr('helloworld' from 6 for 3);
      Result:
      +-----------------------------------+
| substr('helloworld' FROM 6 FOR 3) |
+-----------------------------------+
| wor                               |
+-----------------------------------+

SUBSTRING_INDEX

substring_index(str, delim, count)
  • Description: This function returns the substring before the last occurrence of the delim delimiter in the str string.

    If the count value is greater than 0, this function returns all characters to the left of the last occurrence of the delim delimiter.

    If the count value is less than 0, this function returns all characters to the right of the last occurrence of the delim delimiter.

    The SUBSTRING_INDEX function performs a case-sensitive match when it searches for the delim delimiter.

  • Input value type: str and delim are of the VARCHAR type, and count is of the BIGINT type.
  • Return value type: VARCHAR.
  • Example: 
    SELECT substring_index('www.aliyun.com', '.', 2);
    Result:
    +-------------------------------------------+
| substring_index('www.aliyun.com', '.', 2) |
+-------------------------------------------+
| www.aliyun                                |
+-------------------------------------------+

TO_BASE64

to_base64(x)
  • Description: This function returns the x string encoded in Base64 format.
  • Input value type: VARBINARY or VARCHAR.
  • Return value type: VARCHAR.
  • Examples:
    • In the following statement, a VARCHAR-typed value is specified:
      SELECT to_base64('China');
      Result:
      +--------------------+
| to_base64('China') |
+--------------------+
| Q2hpbmE=           |
+--------------------+
    • In the following statement, a VARBINARY-typed value is specified:
      SELECT to_base64(cast('China' AS varbinary));
      Result:
      +---------------------------------------+
| to_base64(cast('China' AS varbinary)) |
+---------------------------------------+
| Q2hpbmE=                              |
+---------------------------------------+

TO_UTF8

to_utf8(x)
  • Description: This function returns the x string encoded in UTF-8 format.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Example: 
    SELECT to_utf8('China');
    Result:
    +------------------------------------+
| to_utf8('China')                   |
+------------------------------------+
| 0x4368696E61                       |
+------------------------------------+

TRIM

trim([remstr FROM] str)
trim([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
  • Description: This function removes the leading and trailing spaces or the characters contained in the optional remstr string from the str string.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Examples:
    • Sample statement:
      SELECT trim('  bar   ');
      Result:
      +------------------+
| trim('  bar   ') |
+------------------+
| bar              |
+------------------+
    • Sample statement:
      SELECT trim(BOTH 'x' FROM 'xxxbarxxx');
      Result:
      +---------------------------------+
| trim(BOTH 'x' FROM 'xxxbarxxx') |
+---------------------------------+
| bar                             |
+---------------------------------+
    • Sample statement:
      SELECT trim(LEADING 'x' FROM 'xxxbarxxx');
      Result:
      +------------------------------------+
| trim(LEADING 'x' FROM 'xxxbarxxx') |
+------------------------------------+
| barxxx                             |
+------------------------------------+
    • Sample statement:
      SELECT trim(TRAILING 'x' from 'xxxbarxxx');
      Result:
      +-------------------------------------+
| trim(TRAILING 'x' from 'xxxbarxxx') |
+-------------------------------------+
| xxxbar                              |
+-------------------------------------+

UPPER or UCASE

upper(str)
ucase(str)
  • Description: This function converts the letters in the str string to uppercase.
  • Input value type: VARCHAR.
  • Return value type: VARCHAR.
  • Example: 
    SELECT upper('Aliyun');
    Result:
    +-----------------+
| upper('Aliyun') |
+-----------------+
| ALIYUN          |

UNHEX

unhex(x);
  • Description: This function interprets each pair of hexadecimal digits in the x argument as a number and converts it to a character.
  • Input value type: VARBINARY or VARCHAR.
  • Return value type: VARBINARY.
    Note
    • You can use the following methods to convert the VARBINARY-typed result into a VARCHAR value.
      • If the AnalyticDB for MySQL cluster version is 3.1.4 or later, you can use the CAST AS VARCHAR function. For more information, see CAST functions.
      • If the AnalyticDB for MySQL cluster version is earlier than 3.1.4, you can use the FROM_UTF8 function. For more information, see FROM_UTF8.
    • If the input value contains nonhexadecimal digits, NULL is returned.
  • Examples:
    • In the following statement, a VARCHAR-typed value is specified:
      SELECT unhex(hex('China'));
      Result:
      +------------------------------------------+
| unhex(hex('China'))                      |
+------------------------------------------+
| 0x4368696E61                             |
+------------------------------------------+
      Note The preceding returned value is of the VARBINARY type. If you want the system to return a VARCHAR-typed value, you can use the following statement:
      SELECT cast(unhex(hex('China')) AS varchar);
      Result:
      +--------------------------------------+
| cast(unhex(hex('China')) AS varchar) |
+--------------------------------------+
| China                                |
+--------------------------------------+
    • In the following statement, a VARBINARY-typed value is specified:
      SELECT unhex(cast(hex('China') AS varbinary));
      Result:
      +--------------------------------------------------------------------------------+
| unhex(cast(hex('China') AS varbinary))                                         |
+--------------------------------------------------------------------------------+
| 0x4368696E61                                                                   |
+--------------------------------------------------------------------------------+