Use string functions - AnalyticDB - Alibaba Cloud Documentation Center

You can use string functions to process strings, such as converting cases, removing spaces, and extracting substrings. This topic describes the string function syntax and provides examples on how to use the string functions in 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 that consists 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: converts an integer into a string based on the bit values of the integer.
FIELD: returns the index position of a specific string in the string list specified by multiple arguments.
FIND_IN_SET: returns the position of a character or string in the comma-separated string list specified by the strlist argument.
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 that starts from a specific position in a string. The function is equivalent to 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 repeated the specified 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 in a string.
SUBSTRING_INDEX: returns a substring of a string before or after the count-th occurrence of a delimiter.
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 byte and converts it into 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.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Examples:

Sample statement:

SELECT ascii('2');

Sample result:

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

Sample statement:

SELECT ascii('dx');

Sample result:

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

BIN

bin(N)

Description: This function returns the binary string of N.
If N is null, NULL is returned.
Data type of the input value: BIGINT.
Data type of the return value: VARCHAR.

Example:

SELECT bin(12);

Sample result:

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

BIT_LENGTH

bit_length(str)

Description: This function returns the length of the str string, measured in bits.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Examples:

Sample statement:

SELECT bit_length('text');

Sample result:

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

Sample statement:

SELECT bit_length('China');

Sample 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.
Data type of the input value: BIGINT.
Data type of the return value: VARBINARY.

Example:

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

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Examples:

Sample statement:

SELECT char_length('China');

Sample result:

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

Sample statement:

SELECT char_length('abc');

Sample result:

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

CONCAT

concat(str 1, …, str n)

Description: This function concatenates strings. If an argument is null, null is returned.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Examples:

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Examples:

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample 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.
Data type of the input value: BIGINT for the N argument. VARCHAR for the str arguments.
Data type of the return value: VARCHAR.

Example:

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

Sample 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.
Data type of the input value: VARBINARY for the x argument. VARCHAR for the y argument.
Data type of the return value: VARBINARY.

Example:

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

Sample result:

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

EXPORT_SET

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

Description: This function converts an integer specified by the bits argument into a binary value. Argument description:
- onstr: the value that is used to replace 1s in the binary value. offstr: the value that is used to replace 0s in the binary value.
- separator: the delimiter that is used to separate the returned values.
- number_of_bits: the number of bits in the binary value that the system checks from right to left. If the number_of_bits value is greater than 64 or is -1, 64 bits are returned.
Data type of the input value: BIGINT for the bits and number_of_bits arguments. VARCHAR for the onstr, offstr, and separator arguments.
Data type of the return value: VARCHAR.
Examples:
- The following statement converts 5 into a binary value, and returns the two rightmost bits of the binary value by replacing 1s with a and 0s with b and separating the returned values with commas (,).
```
SELECT export_set(5,'a','b',',',2);
```
  Sample result:
```
+-----------------------------+
| export_set(5,'a','b',',',2) |
+-----------------------------+
| a,b                         |
+-----------------------------+             
```
- The following statement converts 6 into a binary value, and returns the 10 rightmost bits of the binary value by using 1 for the 1s and 0 for the 0s in the binary value and separating the returned values with commas (,).
```
SELECT export_set(6,'1','0',',',10);             
```
  Sample 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 the string list specified by multiple arguments. If the str value is not found in the string list, 0 is returned.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Example:

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

Sample result:

sq+-------------------------------------------+
| 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 comma-separated string list specified by the strlist argument.
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.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Example:

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

Sample result:

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

FORMAT

format(X, D)

Description: This function formats the number X 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.
Data type of the input value: DOUBLE for the X argument. BIGINT for the D argument.
Data type of the return value: VARCHAR.

Example:

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

Sample 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.
Data type of the input value: VARBINARY or VARCHAR.
Data type of the return value: VARBINARY.
Note
You can use the following methods to convert the VARBINARY-typed result into a VARCHAR value:
- 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, use the FROM_UTF8 function. For more information, see the "FROM_UTF8" section of this topic.

Examples:

The following statement decodes a Base64-encoded string of the VARCHAR type.

SELECT from_base64('Q2hpbmE=');

Sample 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, execute the following statement:

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

Sample result:

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

The following statement decodes a Base64-encoded string of the VARBINARY type.

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

Sample 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 invalid characters in the decoding result with a specific character.
  Note
  - y is optional. If you do not specify y, � is returned for each invalid character in the decoding result.
  - The y value can be a character, such as a number sign (#), or the ASCII value of a character, such as 35.
Data type of the input value: VARBINARY for the x argument. VARCHAR or BIGINT for the y argument.
Data type of the return value: VARCHAR.

Examples:

The following statement decodes a UTF-8 encoded value.

SELECT from_utf8(to_utf8('hello'));

Sample result:

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

The following statement decodes a value that is not UTF-8 encoded.

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

Sample result:

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

The following statement decodes a value that is not UTF-8 encoded and replaces each invalid character in the decoding result with a number sign (#).

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

Sample result:

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

The following statement decodes a value that is not UTF-8 encoded and replaces each invalid character in the decoding result with the character whose ASCII value is 35.

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

Sample result:

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

HEX

hex(x)

Description: This function converts the x value into a hexadecimal string.
Data type of the input value: BIGINT or VARCHAR.
Data type of the return value: VARCHAR.

Examples:

The following statement converts a BIGINT-typed value.

SELECT hex(16);

Sample result:

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

The following statement converts a VARCHAR-typed value.

SELECT hex('16');

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Example:

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

Sample 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.
Data type of the input value: VARCHAR for the str argument. BIGINT for the len argument.
Data type of the return value: VARCHAR.

Example:

SELECT LEFT('foobarbar', 5);

Sample result:

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

LENGTH or OCTET_LENGTH

length(str)
octet_length(str)

Description: This function returns the length of the str string.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Example:

SELECT length('aliyun');

Sample result:

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

LIKE

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

Description: This function performs pattern matching of the expression string against the pattern. If the string matches the pattern, 1 is returned. Otherwise, 0 is returned. Argument description:
- pattern 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.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Examples:

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample 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.
Data type of the input value: VARCHAR for the str and substr arguments. BIGINT for the pos argument.
Data type of the return value: BIGINT.

Examples:

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample result:

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

LOWER or LCASE

lower(str)
lcase(str)

Description: This function converts letters in the str string to lowercase.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Example:

SELECT lower('Aliyun');

Sample 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.
Data type of the input value: VARCHAR for the str and padstr arguments. BIGINT for the len argument.
Data type of the return value: VARCHAR.

Example:

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

Sample result:

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

LTRIM

ltrim(str)

Description: This function removes the leading spaces of the str string.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Example:

SELECT ltrim('  abc');

Sample 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.
Data type of the input value: BIGINT for the bits argument. VARCHAR for the str argument.
Data type of the return value: VARCHAR.

Examples:

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample result:

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

MID

mid(str, pos, len)

Description: This function returns a substring that contains len characters in length from the str string, starting from the pos position. This function is equivalent to SUBSTR or SUBSTRING.
Data type of the input value: VARCHAR for the str argument. BIGINT for the pos and len arguments.
Data type of the return value: VARCHAR.

Examples:

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample 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.
Data type of the input value: BIGINT.
Data type of the return value: VARCHAR.

Example:

SELECT oct(12);

Sample 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.
Data type of the input value: VARBINARY or VARCHAR.
Data type of the return value: LONG.

Examples:

The following statement returns the code of the leftmost character of a VARCHAR-typed value.
```
SELECT ord('China');
```
Sample result:
```
+--------------+
| ord('China') |
+--------------+
|           67 |
+--------------+
```

The following statement returns the code of the leftmost character of a VARBINARY-typed value.

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

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Example:

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

Sample result:

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

REPEAT

repeat(str, count);

Description: This function returns a string that consists of the str string repeated 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.
Data type of the input value: VARCHAR for the str argument. BIGINT for the count argument.
Data type of the return value: VARCHAR.

Examples:

Sample statement:

SELECT repeat('a', 3);

Sample result:

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

Sample statement:

SELECT repeat('abc', null);

Sample result:

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

Sample statement:

SELECT repeat(null, 3);

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Example:

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

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Example:

SELECT reverse('123456');

Sample 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.
Data type of the input value: VARCHAR for the str argument. BIGINT for the len argument.
Data type of the return value: VARCHAR.

Example:

SELECT RIGHT('abc',3);

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: BOOLEAN.

Examples:

Sample statement:

SELECT 'Michael!' REGEXP '.*';

Sample result:

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

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample 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.
Data type of the input value: VARCHAR for the str and padstr arguments. BIGINT for the len argument.
Data type of the return value: VARCHAR.

Example:

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

Sample result:

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

RTRIM

rtrim(str)

Description: This function removes the trailing spaces of the str string.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Example:

SELECT rtrim('barbar   ');

Sample 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.
Data type of the input value: BIGINT.
Data type of the return value: VARCHAR.

Example:

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

Sample result:

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

SPLIT

split(string, delimiter)

Description: This function splits the string with the delimiter and returns an array.
Data type of the input value: VARCHAR.
Data type of the return value: ARRAY<varchar>.

Example:

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

Sample 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.
Data type of the input value: VARCHAR for the string and delimiter arguments. BIGINT for the index argument.
Data type of the return value: VARCHAR.

Example:

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

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: MAP<varchar, varchar>.

Example:

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

Sample 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 less than the str 2 value, -1 is returned. Otherwise, 1 is returned.
Data type of the input value: VARCHAR.
Data type of the return value: BIGINT.

Example:

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

Sample 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.
Data type of the input value: VARCHAR for the str argument. BIGINT for the pos and len arguments.
Data type of the return value: VARCHAR.

Examples:

Sample statement:

SELECT substr('helloworld', 6);

Sample result:

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

Sample statement:

SELECT substr('helloworld' FROM 6);

Sample result:

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

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample result:

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

SUBSTRING_INDEX

substring_index(str, delim, count)

Description: This function returns a substring of the str string before or after the count-th occurrence of the delim delimiter.
If the count value is greater than 0, this function returns all characters to the left of the count-th occurrence of the delim delimiter.
If the count value is less than 0, this function returns all characters to the right of the count-th occurrence of the delim delimiter.
The SUBSTRING_INDEX function performs a case-sensitive match when it searches for the delim delimiter.
Data type of the input value: VARCHAR for the str and delim arguments. BIGINT for the count argument.
Data type of the return value: VARCHAR.

Example:

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

Sample result:

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

TO_BASE64

to_base64(x)

Description: This function returns the x string encoded in the Base64 format.
Data type of the input value: VARBINARY or VARCHAR.
Data type of the return value: VARCHAR.

Examples:

The following statement returns the Base64-encoded string of a VARCHAR-typed value.

SELECT to_base64('China');

Sample result:

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

The following statement returns the Base64-encoded string of a VARBINARY-typed value.

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

Sample result:

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

TO_UTF8

to_utf8(x)

Description: This function returns the x string encoded in the UTF-8 format.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Example:

SELECT to_utf8('China');

Sample 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.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Examples:

Sample statement:

SELECT trim('  bar   ');

Sample result:

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

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample result:

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

Sample statement:

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

Sample result:

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

UPPER or UCASE

upper(str)
ucase(str)

Description: This function converts letters in the str string to uppercase.
Data type of the input value: VARCHAR.
Data type of the return value: VARCHAR.

Example:

SELECT upper('Aliyun');

Sample result:

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

UNHEX

unhex(x);

Description: This function interprets each pair of hexadecimal digits in the x argument as a byte and converts it into a character.
Data type of the input value: VARBINARY or VARCHAR.
Data type of the return value: VARBINARY.
Note
- You can use the following methods to convert the VARBINARY-typed result into a VARCHAR value:
  - 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, use the FROM_UTF8 function. For more information, see the "FROM_UTF8" section of this topic.
- If the input value contains non-hexadecimal digits, NULL is returned.

Example:

The following statement interprets each pair of hexadecimal digits in a VARCHAR-typed value as a byte and converts it into a character.

SELECT unhex(hex('China'));

Sample 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, execute the following statement:

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

Sample result:

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

The following statement interprets each pair of hexadecimal digits in a VARBINARY-typed value as a byte and converts it into a character.

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

Sample result:

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