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 a specified 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.
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 which 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: 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: repeats a string for count times.
REPLACE: replaces part of the characters in another string with a specified string.
REVERSE: reverses the characters in a string.
RIGHT: returns the N rightmost characters of a string.
RLIKE/REGEXP: matches the expression string with the pattern string. If the two strings match, 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 values of the two strings.
SUBSTR/SUBSTRING: returns a substring of a specified length starting 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 the string encoded in Base64 format.
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.
Return value type: 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.
Return value 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/CHARACTER_LENGTH

CHAR_LENGTH(varchar str)

Description: This function returns the length of the str string, measured in characters.
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 any of the arguments 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. Arguments whose value is null are not included in the final string.
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 string specified by the integer N.
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 clipped 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 position of the str string in the strings, such as 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 string.
If str is not found in strlist or strlist is empty, 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 integer 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 consisting 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 of 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 is used to match the expression string with the pattern string. If the two strings match, 1 is returned. Otherwise, 0 is returned.
pattern can contain the following wildcard characters:
- %: matches strings of any length.
- _: matches a single character.
escape_char is used to escape the wildcard characters and treat the percent signs (%) and underscores (_) in pattern following the escape character as a regular character instead of a wildcard.
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 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 will be 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 containing substrings separated by delimiters) consisting 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, and so on for the other bits. Strings with a value of null are not included within the final string.
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 str, 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 repeats the str string 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 occurrences of the from_str string 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            |

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 matches the expression string with the pattern string. If the two strings match, 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 starting from the pos position to the end of the string. If pos is less than 0, the substring will begin pos characters 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 will begin pos characters 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 occurrence of the 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 searching 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 other specified characters from a 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 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 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                                              |