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 thestr
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
isnull
,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
andN2
. - 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 isnull
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
andoff
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 integerbits
. Theon
string is placed for bit1
, and theoff
string is placed for bit0
. These strings are separated by delimiters. The default delimiter is a comma (,). Thenumber_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 than64
, the argument is silently clipped to64
.The same value is returned when the number_of_bits argument is set to
-1
or64
. - 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 asstr1
,str2
, andstr3
. If thestr
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 thestrlist
string.If
str
is not found instrlist
orstrlist
is empty,0
is returned.If either
str
orstrlist
isnull
,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 toD
decimal places and returns the result as a string.If
D
is0
, 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 instr
. - 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 thestr
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 thestr
string.If
str
orlen
isnull
,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 theexpression
string with thepattern
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(_)
inpattern
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 thestr
string or returns the position of the first occurrence of thesubstr
substring in thestr
string, starting from the specified positionpos
.If
substr
is not instr
,0
is returned.If
substr
orstr
isnull
,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 thepadstr
string to a length oflen
characters.If
str
contains more thanlen
characters, the return value will be shortened tolen
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 bit0
, thestr2
string corresponds to bit1
, and so on for the other bits. Strings with a value ofnull
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 thestr
string, starting from thepos
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
isnull
,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 thestr
string, starting from position1
. 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 forcount
times.If
count
is less than 1, an empty string is returned.If
str
orcount
isnull
,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 thestr
string with theto_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 thestr
string.If
str
orlen
isnull
,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 thepattern
string. If the two strings match,1
is returned. Otherwise,0
is returned.If
expression
orpattern
isnull
,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 thepadstr
string to a length oflen
characters.If
str
contains more thanlen
characters, the return value is shortened tolen
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 thestr1
string,0
is returned. If thestr1
string is smaller than thestr2
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)
orSUBSTRING(varchar str FROM pos)
: returns the substring starting from thepos
position to the end of the string. Ifpos
is less than 0, the substring will begin pos characters from the end of the string.SUBSTRING(varchar str, bigint pos, bigint len)
orSUBSTRING(varchar str FROM pos FOR len)
: returns a substring that containslen
characters in length from the string, starting from thepos
position. Ifpos
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 thestr
string.If
count
is greater than 0, this function returns all characters to the left of the lastdelim
delimiter.If
count
is less than 0, this function returns all characters to the right of the lastdelim
delimiter.The
SUBSTRING_INDEX
function performs a case-sensitive match when searching for thedelim
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 withy
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 |