String functions - Developer‘s Guide| Alibaba Cloud Documentation Center

AnalyticDB for MySQL supports the following string functions:

ASCII: returns the ASCII value of the leftmost character of a character or a string.
BIN: returns the binary string of an integer.
BIT_LENGTH: returns the length of the str argument, measured in bits.
CHAR: returns a string of ASCII values of an integer.
CHAR_LENGTH/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.
EXPORT_SET: returns a combined string based on the value of bits in bits.
FIELD: returns the index position of a specified string in a 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.
HEX: returns the hexadecimal string of an integer, or returns a string that consists of the hexadecimal values of all characters in the 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/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/LCASE: converts a string to lowercase.
LPAD: returns a string that is left-padded with another string.

LTRIM: removes leading spaces of a string.
MAKE_SET: returns a set of comma-separated strings.
MID: returns a substring of a specified length, which starts from a specified position in a string. It has the same effect as SUBSTR/SUBSTRING.
OCT: returns the octal string of an integer.
POSITION: returns the position of the first occurrence of a substring in a string.
REPEAT: returns a string that consists of a string repeated for multiple times.
REPLACE: replaces part of the characters in another string with a specified string.
REVERSE: reverses the characters in a string.
SPLIT: splits a string with delimiters and returns an array.

RIGHT: returns the specified number of rightmost characters of a string.
RLIKE/REGEXP: performs a pattern match of a string expression against a pattern. If the match is successful, 1 is returned. Otherwise, 0 is returned.
RPAD: returns a string that is right-padded with another string.
RTRIM: removes trailing spaces of a string.
SPACE: returns a string that consists of a specified number of spaces.
STRCMP: returns 0, 1, or -1 based on the comparison results of the values of the two strings.
SUBSTR/SUBSTRING: returns a substring of a specified length that starts from the specified position.
SUBSTRING_INDEX: returns the substring before the last occurrence of the delim delimiter in the str string.
TRIM: removes leading and trailing spaces of a string.
UPPER/UCASE: converts a string to uppercase.

ORD: returns the code of the character if the leftmost character of a string is a multibyte character.
UNHEX: interprets each pair of characters in an argument to a hexadecimal value, and then converts the hexadecimal value to bytes represented by numbers. The return value is a binary string.
ENCRYPT: encrypts a string.
TO_BASE64: returns a Base64-encoded string.
FROM_BASE64: decodes a Base64-encoded string and returns the result.

ASCII

ASCII(varchar str)

Description: This function returns the decimal ASCII value of the leftmost character of the str argument or the str string.
Type of the return value: BIGINT.

Example:

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

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

BIN

BIN(bigint N)

Description: This function returns the binary string of N.
If N is null, null is returned.
Return value type: VARCHAR.

Example:

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

BIT_LENGTH

BIT_LENGTH(varchar str)

Description: This function returns the length of the str argument, measured in bits.
Return value type: BIGINT.

Example:

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

    select bit_length('China');
    +--------------------+
    | bit_length('China') |
    +--------------------+
    |                 48 |

CHAR

CHAR(bigint N1, bigint N2...)

Description: This function returns a string of decimal ASCII values of the integers, such as N1 and N2.
Type of the return value: 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/CHARACTER_LENGTH

CHAR_LENGTH(varchar str)

Description: This function returns the length of the str string, measured in characters.
The length of a Chinese character is 1.
Return value type: BIGINT.

Example:

    select char_length('China');
    +---------------------+
    | char_length('China') |
    +---------------------+
    |                   2 |

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

CONCAT

concat(varchar str1, ..., varchar strn)

Description: This function concatenates strings. If an argument is null, null is returned.
Return value type: VARCHAR.

Example:

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

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

CONCAT_WS

concat_ws(varchar separator, varchar str1, ..., varchar strn)

Description: This function concatenates strings and separates them with delimiters. The first separator argument is the delimiter for the rest of the arguments. Strings whose value is null are skipped.
Return value type: VARCHAR.

Example:

    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(bigint N, varchar str1, varchar str2, varchar str3,...)

Description: This function returns the Nth string.
If N is less than 1 or is greater than the number of the string arguments, null is returned.
Return value type: VARCHAR.

Example:

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

EXPORT_SET

EXPORT_SET(bigint bits, varchar on, varchar off[, varchar separator[, bigint number_of_bits]])

Description: This function returns a string in which the on and off strings are placed based on the bits 0 or 1 from right to left (from low-order to high-order) in the binary value of the integer bits. The on string is placed for bit 1, and the off string is placed for bit 0. These strings are separated by delimiters. The default delimiter is a comma (,). The number_of_bits argument specifies the number of bits that are checked. Default value: 64.
If the value of the number_of_bits argument is greater than 64, the argument is silently trimmed to 64.

The same value is returned when the number_of_bits argument is set to -1 or 64.
Return value type: VARCHAR.

Example:

    select export_set(5,'1','0',',',2);
    +---------------------------------+
    | export_set(5, '1', '0', ',', 2) |
    +---------------------------------+
    | 1,0                             |

    select  export_set(5,'1','0',',',10);
    +----------------------------------+
    | export_set(5, '1', '0', ',', 10) |
    +----------------------------------+
    | 1,0,1,0,0,0,0,0,0,0              |

FIELD

field(varchar str, varchar str1, varchar str2, varchar str3,...)

Description: This function returns the index position of the str string in the strings, such as str1, str2, and str3. str1, str2, and str3. If the str string is not found, 0 is returned.
Return value type: BIGINT.

Example:

    select field('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    +-------------------------------------------+
    | field('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff') |
    +-------------------------------------------+
    |                                         2 |

FIND_IN_SET

FIND_IN_SET(varchar str, varchar strlist)

Description: This function returns the position of str in the strlist list.
If str is not found in strlist or strlist is an empty string, 0 is returned.

If either str or strlist is null, null is returned.
Return value type: BIGINT.

Example:

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

FORMAT

format(double X, bigint 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 has no decimal point or fractional part.
Return value type: BIGINT.

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  |

HEX

HEX(bigint N)
HEX(varchar str)

Description: This function returns the hexadecimal string of the integer N or returns a string that consists of the hexadecimal values of all characters in str.
Return value type: VARCHAR.

Example:

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

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

INSTR

INSTR(varchar str, varchar substr)

Description: This function returns the position of the first occurrence of the substr substring in the str string.
Return value type: BIGINT.

Example:

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

LEFT

LEFT(varchar str, bigint len)

Description: This function returns the leftmost len characters in the str string.
If str or len is null, null is returned.
Return value type: VARCHAR.

Example:

    select left('foobarbar', 5);
    +-----------------------------+
    | left('foobarbar', 5) |
    +-----------------------------+
    | fooba                       |

LENGTH/OCTET_LENGTH

length(varchar str)

Description: This function returns the length of the str string.
Return value type: BIGINT.

Example:

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

LIKE

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

Description: The LIKE operator matches the string expression with the pattern. If the match is successful, 1 is returned. Otherwise, 0 is returned.
The pattern is a wildcard pattern. The following wildcards are supported:
- %: matches a string of a length.
- _: matches a single character.
escape_char: escapes the percent signs (%) and underscores (_) in the pattern so that the percent signs (%) and underscores (_) following the escape character do not serve as wildcards.
Return value type: BIGINT.

Example:

    select 'David!' like 'David_' as result1, 'David!' not like 'David_' as result2,  'David!' like '%D%v%' as result3;
    +---------+---------+---------+
    | result1 | result2 | result3 |
    +---------+---------+---------+
    |       1 |       0 |       1 |

    select 'David_' like 'David|_' ESCAPE '|';
    +------------------------------------+
    | 'David_' LIKE 'David|_' ESCAPE '|' |
    +------------------------------------+
    |                                  1 |

LOCATE

LOCATE(varchar substr, varchar str)
LOCATE(varchar substr, varchar str, bigint pos)

Description: This function returns the position of the first occurrence of substr in the str string, or returns the position of the first occurrence of the substr substring in the str string, starting from the specified position pos.
If substr is not found in str,0 is returned.

If substr or str is null, null is returned.
Return value type: BIGINT.

Example:

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

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

LOWER/LCASE

lower(varchar str)

Description: This function converts letters in the str string to lowercase.
Return value type: VARCHAR.

Example:

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

LPAD

LPAD(varchar str, bigint len, varchar padstr)

Description: This function returns the str string, left-padded with the padstr string to a length of len characters.
If str contains more than len characters, the return value is shortened to len characters.
Return value type: VARCHAR.

Example:

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

LTRIM

LTRIM(varchar str)

Description: This function removes the leading spaces of the str string.
Return value type: VARCHAR.

Example:

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

MAKE_SET

MAKE_SET(bits, str1, str2,...)

Description: This function returns a set value (a string that contains substrings separated with delimiters). The set value consists of strings that have the corresponding bit 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.
Return value type: VARCHAR.

Example:

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

    select make_set(1 | 4,'hello','nice',NULL,'world')as result;
    +--------+
    | result |
    +--------+
    | hello  |

MID

MID(varchar str, bigint pos, bigint len)

Description: Same as SUBSTR/SUBSTRING, this function returns a substring that contains len characters in length from the str string, starting from the pos position.
Return value type: VARCHAR.

Example:

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

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

OCT

OCT(bigint N)

Description: This function returns the octal string of the integer N.
If N is null, null is returned.
Return value type: VARCHAR.

Example:

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

POSITION

POSITION(varchar substr IN varchar str)

Description: This function returns the position of the first occurrence of the substr substring in the str string, starting from position 1. If substring is not found in the str string, 0 is returned.
Return value type: BIGINT.

Example:

    select position('bar' in 'foobarbar');
    +----------------------------+
    | locate('bar', 'foobarbar') |
    +----------------------------+
    |                          4 |

REPEAT

REPEAT(varchar str, bigint count)

Description: This function returns a string that consists of the str string repeated for count times.
If count is less than 1, an empty string is returned.

If str or count is null, null is returned.
Return value type: VARCHAR.

Example:

    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(varchar str, varchar from_str, varchar to_str)

Description: This function replaces all from_str strings in the str string with the to_str string.
Return value type: VARCHAR.

Example:

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

REVERSE

REVERSE(varchar str)

Description: This function returns the str string with the order of the characters reversed.
Return value type: VARCHAR.

Example:

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

SPLIT

split(string, delimiter)
split_part(string, delimiter, index)
split_to_map(string, entryDelimiter, keyValueDelimiter)

Description:
- split(string, delimiter): splits a string by delimiter and returns an array.
- split_part(string, delimiter, index): splits the string by delimiter and returns the substring whose array subscript is index. The index starts with 1. If the value of the index parameter is greater than the number of fields, null is returned.
- split_to_map(string, entryDelimiter, keyValueDelimiter): splits a string by using entryDelimiter and keyValueDelimiter and returns a map. entryDelimiter splits the string into key-value pairs. keyValueDelimiter splits each pair into key and value.
Return value type: VARCHAR or map<varchar, varchar>.

Example:

    SELECT Split('1#2#3', '#'), Split('#1#2#3#', '#'),  
           Split('123', '#');
    | _col0     | _col1         | _col2     |
    +-----------+---------------+-----------+
    | [1, 2, 3] | [, 1, 2, 3, ] | [123]     |
    SELECT Split_part('A#B#C', '#', 2), 
           Split_part('A#B#C', '#', 4);
    +---------------------+-------------------------+
    | _col0               | _col1                   |
    +---------------------+-------------------------+
    | B                   | NULL                    |
    SELECT Split_to_map('k1:v1,k2:v2', ',', ':'), 
           Split_to_map('', ',', ':'); 
    +---------------------+-------------------------+
    | _col0               | _col1                   |
    +---------------------+-------------------------+
    | {k1=v1, k2=v2}      | {}                      |

RIGHT

RIGHT(varchar str, bigint len)

Description: This function returns the rightmost len characters of the str string.
If str or len is null, null is returned.
Return value type: VARCHAR.

Example:

    select right('abc',3);
    +------------------------+
    | presto_right('abc', 3) |
    +------------------------+
    | abc                    |

RLIKE/REGEXP

expression  RLIKE pattern
expression  REGEXP pattern

Description: This function performs a pattern match of string expression against pattern. If the match is successful, 1 is returned. Otherwise, 0 is returned.
If expression or pattern is null, null is returned.
Return value type: BIGINT.

Example:

    select 'Michael!' regexp '.*';
    +-------------------------------+
    | regexp_like('Michael!', '. *') |
    +-------------------------------+
    |                             1 |

    select 'new*\n*line' regexp 'new\\*.\\*line';
    +--------------------------------------------+
    | regexp_like('new*
    *line', 'new\\*.\\*line') |
    +--------------------------------------------+
    |                                          0 |

    select 'c' regexp '^[a-d]';
    +----------------------------+
    | regexp_like('c', '^[a-d]') |
    +----------------------------+
    |                          1 |

RPAD

RPAD(varchar str, bigint len, varchar padstr)

Description: This function returns the str string, right-padded with the padstr string to a length of len characters.
If str contains more than len characters, the return value is shortened to len characters.
Return value type: VARCHAR.

Example:

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

RTRIM

RTRIM(varchar str)

Description: This function removes the trailing spaces of the str string.
Return value type: VARCHAR.

Example:

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

SPACE

SPACE(bigint N)

Description: This function returns a string that consists of a specified number of spaces.
Return value type: VARCHAR.

Example:

    select concat("#", space(6), "#");
    +----------------------------+
    | concat('#', space(6), '#') |
    +----------------------------+
    | #      #                   |

STRCMP

STRCMP(varchar str1, varchar str2)

Description: If the str1 string is the same as the str1 string, 0 is returned. If the str1 string is smaller than the str2 string based on the current sort order, -1 is returned. Otherwise, 1 is returned.
Return value type: BIGINT.

Example:

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

SUBSTR/SUBSTRING

SUBSTRING(varchar str, bigint pos)
SUBSTRING(varchar str FROM pos)
SUBSTRING(varchar str, bigint pos, bigint len)
SUBSTRING(varchar 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 pos is less than 0, the substring starts from the position which 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 pos is less than 0, the substring starts from the position which is pos characters away from the end of the string.
Return value type: VARCHAR.

Example:

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

    select substr('helloworld' from 6);
    +-------------------------+
    | substr('helloworld', 6) |
    +-------------------------+
    | world                   |

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

    select substr('helloworld' from 6 for 3);
    +----------------------------+
    | substr('helloworld', 6, 3) |
    +----------------------------+
    | wor                        |

SUBSTRING_INDEX

SUBSTRING_INDEX(varchar str, varchar delim, bigint count)

Description: This function returns the substring before the last delim delimiter in the str string.
If count is greater than 0, this function returns all characters to the left of the last delim delimiter.

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

The SUBSTRING_INDEX function performs a case-sensitive match when it searches for the delim delimiter.
Return value type: VARCHAR.

Example:

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

TRIM

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

Description: This function removes the leading and trailing spaces or the characters that match the optional specified characters from a string to trim the string.
Return value type: VARCHAR.

Example:

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

    select trim(both 'x' from 'xxxbarxxx');
    +------------------------+
    | trim('x', 'xxxbarxxx') |
    +------------------------+
    | bar                    |

    select trim(leading 'x' from 'xxxbarxxx');
    +-------------------------+
    | ltrim('x', 'xxxbarxxx') |
    +-------------------------+
    | barxxx                  |

    select trim(trailing 'x' from 'xxxbarxxx');
    +-------------------------+
    | rtrim('x', 'xxxbarxxx') |
    +-------------------------+
    | xxxbar                  |

UPPER/UCASE

upper(varchar str)

Description: This function converts the letters in the str string to uppercase.
Return value type: VARCHAR.

Example:

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

ORD

ord(varbinary x)

Description: This function returns the code of the character if the leftmost character of the x string is a multibyte character.
Return value type: LONG.

Example:

    select ord(CAST('China' AS VARBINARY));
    +----------------------------------+
    | ord(CAST('China' AS varbinary))   |
    +----------------------------------+
    |                              228 |

UNHEX

unhex(varbinary x)

Description: This function interprets each pair of characters in the x argument as a hexadecimal value, and then converts the hexadecimal value to bytes represented by numbers. The return value is a binary string.
Return value type: VARBINARY.

Example:

    select unhex(CAST('China' AS VARBINARY));
    +------------------------------------+
    | unhex(CAST('China' AS varbinary))   |
    +------------------------------------+
    | NULL                               |

ENCRYPT

encrypt(varbinary x, varchar y)

Description: This function encrypts the x argument with y as the salt value.
Return value type: VARBINARY.

Example:

    select encrypt('abdABC123','key');
    +-----------------------------+
    | encrypt('abdABC123', 'key') |
    +-----------------------------+
    | kezazmcIo.aCw               |

TO_BASE64

to_base64(varbinary x)

Description: This function returns the x string encoded in the Base64 format.
Return value type: VARCHAR.

Example:

    select to_base64(CAST('China' AS VARBINARY));
    +----------------------------------------+
    | to_base64(CAST('China' AS varbinary))   |
    +----------------------------------------+
    | 5Lit5Zu9                               |

FROM_BASE64

from_base64(varbinary x)

Description: This function decodes the Base64-encoded x string and returns the result.
Return value type: VARBINARY.

Example:

    select from_base64(to_base64(CAST('abc' AS VARBINARY)));
    +--------------------------------------------------+
    | from_base64(to_base64(CAST('abc' AS varbinary))) |
    +--------------------------------------------------+
    | abc                                              |