Syntax and examples for built in string functions - AnalyticDB

AnalyticDB for MySQL supports a set of string functions for common string processing tasks — converting case, extracting substrings, searching for patterns, encoding and decoding data, and more. This topic describes the syntax and usage of each function, with examples.

Functions in this topic:

Category	Functions
Length and character info	ASCII, BIT_LENGTH, CHAR_LENGTH or CHARACTER_LENGTH, LENGTH or OCTET_LENGTH, ORD
Case conversion	LOWER or LCASE, UPPER or UCASE
Concatenation and formatting	CONCAT, CONCAT_WS, FORMAT, REPEAT, SPACE
Substrings and extraction	LEFT, MID, RIGHT, SUBSTR or SUBSTRING, SUBSTRING_INDEX
Search and position	FIELD, FIND_IN_SET, INSTR, LOCATE, POSITION
Pattern matching	LIKE, RLIKE or REGEXP
Padding and trimming	LPAD, LTRIM, RPAD, RTRIM, TRIM
Splitting	SPLIT, SPLIT_PART, SPLIT_TO_MAP
Encoding and decoding	BIN, CHAR, FROM_BASE64, FROM_UTF8, HEX, OCT, TO_BASE64, TO_UTF8, UNHEX
Comparison	STRCMP
Set operations	ELT, EXPORT_SET, MAKE_SET
Replacement and reversal	REPLACE, REVERSE
Encryption and hashing	ENCRYPT, MD5_MUR

ASCII

ascii(str)

Returns the decimal ASCII value of the leftmost character of str.

Parameter	Type	Description
`str`	VARCHAR	Input string or single character

Return type: BIGINT

Examples:

SELECT ascii('2');

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

SELECT ascii('dx');

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

BIN

bin(N)

Returns the binary string representation of N.

Parameter	Type	Description
`N`	BIGINT	Integer to convert

Return type: VARCHAR

Usage notes:

If N is NULL, NULL is returned.

Example:

SELECT bin(12);

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

BIT_LENGTH

bit_length(str)

Returns the length of str in bits.

Parameter	Type	Description
`str`	VARCHAR	Input string

Return type: BIGINT

Examples:

SELECT bit_length('text');

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

SELECT bit_length('China');

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

CHAR

char(N1, N2, ...)

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

Parameter	Type	Description
`N1, N2, ...`	BIGINT	One or more integer ASCII codes

Return type: VARBINARY

Example:

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

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

CHAR_LENGTH or CHARACTER_LENGTH

char_length(str)
character_length(str)

Returns the length of str in characters.

Parameter	Type	Description
`str`	VARCHAR	Input string

Return type: BIGINT

Usage notes:

Each Chinese character counts as 1.

Examples:

SELECT char_length('China');

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

SELECT char_length('abc');

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

CONCAT

concat(str1, ..., strN)

Concatenates multiple strings into one.

Parameter	Type	Description
`str1, ..., strN`	VARCHAR	Two or more strings to concatenate

Return type: VARCHAR

Usage notes:

If any argument is NULL, the return value is NULL.

Examples:

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

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

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

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

CONCAT_WS

concat_ws(separator, str1, ..., strN)

Concatenates multiple strings using a specified separator. NULL values among the string arguments are skipped.

Parameter	Type	Description
`separator`	VARCHAR	Delimiter inserted between each string
`str1, ..., strN`	VARCHAR	Strings to concatenate

Return type: VARCHAR

Usage notes:

NULL string arguments are skipped — they do not produce a double separator or empty value in the output.

Examples:

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

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

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

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

ELT

elt(N, str1, ..., strN)

Returns the Nth string from the list.

Parameter	Type	Description
`N`	BIGINT	1-based index into the string list
`str1, ..., strN`	VARCHAR	List of strings

Return type: VARCHAR

Usage notes:

If N < 1 or N is greater than the number of string arguments, NULL is returned.

Example:

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

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

ENCRYPT

encrypt(x, y)

Encrypts x using y as the salt value.

Parameter	Type	Description
`x`	VARBINARY	String to encrypt
`y`	VARCHAR	Salt value

Return type: VARBINARY

Example:

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

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

EXPORT_SET

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

Converts bits to a binary representation and returns a string built by replacing each bit position with onstr (for bit 1) or offstr (for bit 0), separated by separator. Bits are checked from right to left.

Parameter	Type	Description
`bits`	BIGINT	Integer to convert to binary
`onstr`	VARCHAR	String to use for bit value `1`
`offstr`	VARCHAR	String to use for bit value `0`
`separator`	VARCHAR	(Optional) Delimiter between output values
`number_of_bits`	BIGINT	(Optional) Number of bits to check, from right to left. Defaults to `64`

Return type: VARCHAR

Usage notes:

If number_of_bits is greater than 64, it is trimmed to 64.
If number_of_bits is -1, the default value 64 is used.

Examples:

Convert 5 to binary and check the two rightmost bits. Replace 1 with a and 0 with b, separated by commas:

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

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

Convert 6 to binary and check the 10 rightmost bits. Replace 1 with 1 and 0 with 0, separated by commas:

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

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

FIELD

FIELD(str, str1, str2, ..., strN)

Returns the position of str within the list str1, str2, ..., strN.

Parameter	Type	Description
`str`	VARCHAR	String to search for
`str1, ..., strN`	VARCHAR	List of strings to search

Return type: BIGINT

Usage notes:

Returns 0 if str is not found in the list.

Example:

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

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

FIND_IN_SET

find_in_set(str, strlist)

Returns the position of str within the comma-separated list strlist.

Parameter	Type	Description
`str`	VARCHAR	String to search for
`strlist`	VARCHAR	Comma-separated list of strings

Return type: BIGINT

Usage notes:

Returns 0 if str is not found in strlist, or if strlist is an empty string.
Returns NULL if either str or strlist is NULL.

Example:

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

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

FORMAT

format(X, D)

Formats the number X in #,###,###.## style, rounded to D decimal places, and returns the result as a string.

Parameter	Type	Description
`X`	DOUBLE	Number to format
`D`	BIGINT	Number of decimal places

Return type: VARCHAR

Usage notes:

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

Example:

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

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

FROM_BASE64

from_base64(x)

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

Parameter	Type	Description
`x`	VARBINARY or VARCHAR	Base64-encoded input

Return type: VARBINARY

Usage notes:

The return value is of the VARBINARY type. To get a VARCHAR result, convert it using one of the following methods:

AnalyticDB for MySQL V3.1.4 or later: Use CAST AS VARCHAR. For more information, see CAST functions.
Earlier than V3.1.4: Use the FROM_UTF8 function. For more information, see FROM_UTF8.

Examples:

Input is a VARCHAR string:

SELECT from_base64('Q2hpbmE=');

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

To get the original VARCHAR value, cast the result:

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

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

Input is a VARBINARY value:

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

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

FROM_UTF8

from_utf8(x)
from_utf8(x, y)

Decodes a UTF-8 encoded binary value and returns a VARCHAR string.

from_utf8(x) — Decodes x and returns the decoded result.
from_utf8(x, y) — Decodes x, replacing any invalid byte sequences with y.

Parameter	Type	Description
`x`	VARBINARY	UTF-8 encoded input to decode
`y`	VARCHAR or BIGINT	(Optional) Replacement character for invalid bytes. Accepts the character itself (e.g., `#`) or its ASCII code (e.g., `35`). Defaults to empty string if omitted

Return type: VARCHAR

Examples:

Decode a valid UTF-8 encoded string:

SELECT from_utf8(to_utf8('hello'));

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

Decode a non-UTF-8 encoded value (invalid bytes are replaced with an empty string by default):

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

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

Replace invalid bytes with #:

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

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

Replace invalid bytes using the ASCII code 35 (equivalent to #):

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

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

HEX

hex(x)

Returns the hexadecimal string representation of x.

Parameter	Type	Description
`x`	BIGINT or VARCHAR	Integer or string to convert

Return type: VARCHAR

Examples:

Integer input:

SELECT hex(16);

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

String input:

SELECT hex('16');

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

INSTR

instr(str, substr)

Returns the position of the first occurrence of substr within str.

Parameter	Type	Description
`str`	VARCHAR	String to search within
`substr`	VARCHAR	Substring to search for

Return type: BIGINT

Example:

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

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

LEFT

LEFT(str, len)

Returns the len leftmost characters of str.

Parameter	Type	Description
`str`	VARCHAR	Input string
`len`	BIGINT	Number of characters to return

Return type: VARCHAR

Usage notes:

Returns NULL if str or len is NULL.

Example:

SELECT LEFT('foobarbar', 5);

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

LENGTH or OCTET_LENGTH

length(str)
octet_length(str)

Returns the length of str.

Parameter	Type	Description
`str`	VARCHAR	Input string

Return type: BIGINT

Example:

SELECT length('aliyun');

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

LIKE

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

Matches expression against pattern. Returns 1 for a match and 0 otherwise.

Parameter	Type	Description
`expression`	VARCHAR	String to match
`pattern`	VARCHAR	Wildcard pattern. Use `%` to match any sequence of characters, and `_` to match a single character
`escape_char`	VARCHAR	(Optional) Escape character that treats the following `%` or `_` as a literal character rather than a wildcard

Return type: BIGINT

Examples:

Basic wildcard matching:

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

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

Using an escape character to match a literal underscore:

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

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

LOCATE

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

Returns the position of the first occurrence of substr within str. Optionally, starts searching from position pos.

Parameter	Type	Description
`substr`	VARCHAR	Substring to search for
`str`	VARCHAR	String to search within
`pos`	BIGINT	(Optional) Starting position for the search

Return type: BIGINT

Usage notes:

Returns 0 if substr is not found in str.
Returns NULL if substr or str is NULL.

Examples:

Search from the beginning:

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

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

Search starting at position 7:

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

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

LOWER or LCASE

lower(str)
lcase(str)

Converts all letters in str to lowercase.

Parameter	Type	Description
`str`	VARCHAR	Input string

Return type: VARCHAR

Example:

SELECT lower('Aliyun');

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

LPAD

lpad(str, len, padstr)

Left-pads str with padstr to a total length of len characters.

Parameter	Type	Description
`str`	VARCHAR	String to pad
`len`	BIGINT	Target total length
`padstr`	VARCHAR	Padding string

Return type: VARCHAR

Usage notes:

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

Example:

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

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

LTRIM

ltrim(str)

Removes all leading spaces from str.

Parameter	Type	Description
`str`	VARCHAR	Input string

Return type: VARCHAR

Example:

SELECT ltrim('  abc');

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

MAKE_SET

make_set(bits, str1, str2, ...)

Returns a set of comma-separated strings selected by the bit positions set in bits. str1 corresponds to bit 0, str2 to bit 1, and so on.

Parameter	Type	Description
`bits`	BIGINT	Bitmask that selects which strings to include
`str1, str2, ...`	VARCHAR	Strings corresponding to each bit position

Return type: VARCHAR

Usage notes:

NULL values in the string list are not included in the result.

Examples:

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

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

5 in binary is 101, so bits 0 and 2 are set, selecting hello and world.

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

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

1 | 4 = 5 (binary 101), so bits 0 and 2 are set. The string at bit 2 is NULL, so only hello is returned.

MD5_MUR

MD5_MUR(x)

Computes the MD5 hash of x, then converts it to a LONG hash value using the MurmurHash3 algorithm. Compared to CRC32, this function has a lower collision rate.

Parameter	Type	Description
`x`	VARCHAR	Input string

Return type: LONG

Prerequisites: Requires kernel version 3.2.5 or later.

Example:

SELECT MD5_MUR('TEST') AS result;

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

MID

mid(str, pos, len)

Returns a substring of length len from str, starting at position pos. This function is equivalent to SUBSTR and SUBSTRING.

Parameter	Type	Description
`str`	VARCHAR	Input string
`pos`	BIGINT	Starting position (1-based). Use a negative value to count from the end of the string
`len`	BIGINT	Number of characters to return

Return type: VARCHAR

Examples:

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

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

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

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

OCT

oct(N)

Returns the octal string representation of N.

Parameter	Type	Description
`N`	BIGINT	Integer to convert

Return type: VARCHAR

Usage notes:

Returns NULL if N is NULL.

Example:

SELECT oct(12);

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

ORD

ord(x)

Returns the code of the leftmost character of x if it is a multibyte character.

Parameter	Type	Description
`x`	VARBINARY or VARCHAR	Input string

Return type: LONG

Examples:

VARCHAR input:

SELECT ord('China');

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

VARBINARY input:

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

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

POSITION

position(substr IN str)

Returns the position of the first occurrence of substr within str. Positions are 1-based.

Parameter	Type	Description
`substr`	VARCHAR	Substring to search for
`str`	VARCHAR	String to search within

Return type: BIGINT

Usage notes:

Returns 0 if substr is not found.

Example:

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

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

REPEAT

repeat(str, count)

Returns str repeated count times.

Parameter	Type	Description
`str`	VARCHAR	String to repeat
`count`	BIGINT	Number of repetitions

Return type: VARCHAR

Usage notes:

Returns an empty string if count < 1.
Returns NULL if str or count is NULL.

Examples:

SELECT repeat('a', 3);

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

SELECT repeat('abc', null);

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

SELECT repeat(null, 3);

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

REPLACE

replace(str, from_str, to_str)

Replaces all occurrences of from_str in str with to_str.

Parameter	Type	Description
`str`	VARCHAR	Input string
`from_str`	VARCHAR	Substring to replace
`to_str`	VARCHAR	Replacement string

Return type: VARCHAR

Example:

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

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

REVERSE

reverse(str)

Returns str with characters in reverse order.

Parameter	Type	Description
`str`	VARCHAR	Input string

Return type: VARCHAR

Example:

SELECT reverse('123456');

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

RIGHT

RIGHT(str, len)

Returns the len rightmost characters of str.

Parameter	Type	Description
`str`	VARCHAR	Input string
`len`	BIGINT	Number of characters to return

Return type: VARCHAR

Usage notes:

Returns NULL if str or len is NULL.

Example:

SELECT RIGHT('abc', 3);

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

RLIKE or REGEXP

expression RLIKE pattern
expression REGEXP pattern

Returns 1 if expression matches the regular expression pattern, and 0 otherwise.

Parameter	Type	Description
`expression`	VARCHAR	String to match
`pattern`	VARCHAR	Regular expression pattern

Return type: BOOLEAN

Usage notes:

Returns NULL if expression or pattern is NULL.

Examples:

SELECT 'Michael!' REGEXP '.*';

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

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

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

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

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

RPAD

rpad(str, len, padstr)

Right-pads str with padstr to a total length of len characters.

Parameter	Type	Description
`str`	VARCHAR	String to pad
`len`	BIGINT	Target total length
`padstr`	VARCHAR	Padding string

Return type: VARCHAR

Usage notes:

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

Example:

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

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

RTRIM

rtrim(str)

Removes all trailing spaces from str.

Parameter	Type	Description
`str`	VARCHAR	Input string

Return type: VARCHAR

Example:

SELECT rtrim('barbar   ');

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

SPACE

space(N)

Returns a string of N space characters.

Parameter	Type	Description
`N`	BIGINT	Number of spaces

Return type: VARCHAR

Usage notes:

Combine with concat() to embed spaces in a larger string.

Example:

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

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

SPLIT

split(string, delimiter)

Splits string on each occurrence of delimiter and returns the parts as an array.

Parameter	Type	Description
`string`	VARCHAR	Input string
`delimiter`	VARCHAR	Delimiter to split on

Return type: ARRAY\<varchar\>

Example:

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

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

SPLIT_PART

split_part(string, delimiter, index)

Splits string on delimiter and returns the element at position index. The index is 1-based.

Parameter	Type	Description
`string`	VARCHAR	Input string
`delimiter`	VARCHAR	Delimiter to split on
`index`	BIGINT	1-based position of the element to return

Return type: VARCHAR

Usage notes:

Returns NULL if index is greater than the number of resulting fields.

Example:

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

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

SPLIT_TO_MAP

split_to_map(string, entryDelimiter, keyValueDelimiter)

Splits string into key-value pairs and returns a map. entryDelimiter separates individual pairs, and keyValueDelimiter separates each key from its value.

Parameter	Type	Description
`string`	VARCHAR	Input string
`entryDelimiter`	VARCHAR	Delimiter between key-value pairs
`keyValueDelimiter`	VARCHAR	Delimiter between key and value within each pair

Return type: MAP\<varchar, varchar\>

Example:

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

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

STRCMP

strcmp(str1, str2)

Compares str1 and str2 based on the current sort order.

Parameter	Type	Description
`str1`	VARCHAR	First string
`str2`	VARCHAR	Second string

Return type: BIGINT

Returns:

0 if the strings are identical
-1 if str1 is less than str2
1 if str1 is greater than str2

Example:

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

+-------------------------+
| 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)

Returns a substring of str starting at position pos, optionally limited to len characters.

Parameter	Type	Description
`str`	VARCHAR	Input string
`pos`	BIGINT	Starting position (1-based). A negative value counts back from the end of the string
`len`	BIGINT	(Optional) Maximum number of characters to return. If omitted, returns all characters from `pos` to the end

Return type: VARCHAR

Examples:

Return everything from position 6 to the end:

SELECT substr('helloworld', 6);

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

Using the FROM syntax:

SELECT substr('helloworld' FROM 6);

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

Return 3 characters starting at position 6:

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

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

Using the FROM ... FOR syntax:

SELECT substr('helloworld' FROM 6 FOR 3);

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

SUBSTRING_INDEX

substring_index(str, delim, count)

Returns a substring from str cut at the count-th occurrence of delim.

Parameter	Type	Description
`str`	VARCHAR	Input string
`delim`	VARCHAR	Delimiter to search for (case-sensitive)
`count`	BIGINT	Number of delimiter occurrences. Positive values count from the left and return everything to the left of the match; negative values count from the right and return everything to the right

Return type: VARCHAR

Usage notes:

The search for delim is case-sensitive.

Example:

Return everything before the second .:

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

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

TO_BASE64

to_base64(x)

Returns the Base64-encoded form of x.

Parameter	Type	Description
`x`	VARBINARY or VARCHAR	Input to encode

Return type: VARCHAR

Examples:

VARCHAR input:

SELECT to_base64('China');

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

VARBINARY input:

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

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

TO_UTF8

to_utf8(x)

Returns the UTF-8 encoded form of x.

Parameter	Type	Description
`x`	VARCHAR	Input string

Return type: VARCHAR

Example:

SELECT from_utf8(to_utf8('China'));

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

TRIM

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

Removes leading and trailing spaces from str, or removes characters matching remstr from the specified side(s).

Parameter	Type	Description
`str`	VARCHAR	Input string
`remstr`	VARCHAR	(Optional) Character(s) to remove. Defaults to space
`BOTH \| LEADING \| TRAILING`	—	(Optional) Side from which to remove characters. Defaults to `BOTH`

Return type: VARCHAR

Examples:

Remove leading and trailing spaces:

SELECT trim('  bar   ');

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

Remove a specific character from both sides:

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

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

Remove only from the leading side:

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

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

Remove only from the trailing side:

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

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

UNHEX

unhex(x)

Interprets each pair of hexadecimal digits in x as a byte value and returns the corresponding binary string.

Parameter	Type	Description
`x`	VARBINARY or VARCHAR	Hexadecimal string to decode

Return type: VARBINARY

Usage notes:

The return value is of the VARBINARY type. To get a VARCHAR result, convert it using one of the following methods:

AnalyticDB for MySQL V3.1.4 or later: Use CAST AS VARCHAR. For more information, see CAST functions.
Earlier than V3.1.4: Use the FROM_UTF8 function. For more information, see FROM_UTF8.

If the input contains any non-hexadecimal characters, NULL is returned.

Examples:

VARCHAR input — use CAST to get the VARCHAR result:

SELECT unhex(hex('China'));

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

To get the VARCHAR value explicitly:

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

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

VARBINARY input:

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

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

UPPER or UCASE

upper(str)
ucase(str)

Converts all letters in str to uppercase.

Parameter	Type	Description
`str`	VARCHAR	Input string

Return type: VARCHAR

Example:

SELECT upper('Aliyun');

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