This topic describes the string functions supported by AnalyticDB for MySQL.
- ASCII: returns the ASCII value of a character or of the leftmost character of a string.
- BIN: returns the binary string of an integer.
- BIT_LENGTH: returns the length of a string, measured in bits.
- CHAR: returns the string of the ASCII values of integers.
- CHAR_LENGTH or CHARACTER_LENGTH: returns the length of a string, measured in characters.
- CONCAT: concatenates strings.
- CONCAT_WS: concatenates strings and separates them with delimiters.
- ELT: returns the string specified by the integer N.
- ENCRYPT: encrypts a string.
- EXPORT_SET: returns a string that represents the bits in an integer.
- FIELD: returns the index position of a specific string in the 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.
- FROM_BASE64: decodes a Base64-encoded string and returns the result.
- FROM_UTF8: decodes a UTF-8 encoded string and returns the result.
- HEX: converts an integer or a string into a hexadecimal string.
- INSTR: returns the position of the first occurrence of a substring in a string.
- LEFT: returns the N leftmost characters of a string.
- LENGTH or OCTET_LENGTH: returns the length of a string.
- LIKE: performs simple pattern matching.
- LOCATE: returns the position of the first occurrence of a string in another string.
- LOWER or LCASE: converts a string to lowercase.
- LPAD: returns a string that is left-padded with another string.
- LTRIM: removes the leading spaces of a string.
- MAKE_SET: returns a set of comma-separated strings.
- MID: returns a substring of a specific length, which starts from a specific position in a string. This function serves the same purpose as SUBSTR or SUBSTRING.
- OCT: returns the octal string of an integer.
- ORD: returns the code of the leftmost character of a string if the character is a multibyte character.
- POSITION: returns the position of the first occurrence of a substring in a string.
- REPEAT: returns a string that consists of the source string concatenated to itself a specific number of times.
- REPLACE: replaces specific characters in a string with another string.
- REVERSE: reverses the characters in a string.
- RIGHT: returns the N rightmost characters of a string.
- RLIKE or REGEXP: performs pattern matching of a string against a regular expression. If the string matches the regular expression, 1 is returned. Otherwise, 0 is returned.
- RPAD: returns a string that is right-padded with another string.
- RTRIM: removes the trailing spaces of a string.
- SPACE: returns a string that consists of a specific number of spaces.
- SPLIT: splits a string with delimiters and returns an array.
- SPLIT_PART: splits a string with delimiters and returns a specific substring in the array.
- SPLIT_TO_MAP: splits a string by using
entryDelimiter
andkeyValueDelimiter
and returns amap
. - STRCMP: returns 0, 1, or -1 based on the comparison results of two strings.
- SUBSTR or SUBSTRING: returns a substring of a specific length that starts from a specific position.
- SUBSTRING_INDEX: returns the substring before the last occurrence of the delimiter in the string.
- TO_BASE64: returns a Base64-encoded string.
- TO_UTF8: returns a UTF-8 encoded string.
- TRIM: removes the leading and trailing spaces of a string.
- UPPER or UCASE: converts a string to uppercase.
- UNHEX: interprets each pair of hexadecimal digits in the argument as a number and converts it to a character.
ASCII
ascii(str)
- Description: This function returns the decimal ASCII value of the
str
character or of the leftmost character of thestr
string. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Examples:
- Sample statement:
SELECT ascii('2');
Result:+------------+ | ascii('2') | +------------+ | 50 | +------------+
- Sample statement:
SELECT ascii('dx');
Result:+-------------+ | ascii('dx') | +-------------+ | 100 | +-------------+
- Sample statement:
BIN
bin(N)
- Description: This function returns the binary string of
N
.If
N
isnull
,NULL
is returned. - Input value type: BIGINT.
- Return value type: VARCHAR.
- Example:
SELECT bin(12);
Result:+---------+ | bin(12) | +---------+ | 1100 | +---------+
BIT_LENGTH
bit_length(str)
- Description: This function returns the length of the
str
string, measured in bits. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Examples:
- Sample statement:
SELECT bit_length('text');
Result:+--------------------+ | bit_length('text') | +--------------------+ | 32 | +--------------------+
- Sample statement:
SELECT bit_length('China');
Result:+---------------------+ | bit_length('China') | +---------------------+ | 40 | +---------------------+
- Sample statement:
CHAR
char(N1, N2, ...)
- Description: This function returns the string that consists of the decimal ASCII values of the specified integers.
- Input value type: BIGINT.
- Return value type: VARBINARY.
- Example:
SELECT char(97,110,97,108,121,116,105,99,100,98);
Result:+-------------------------------------------+ | char(97,110,97,108,121,116,105,99,100,98) | +-------------------------------------------+ | analyticdb | +-------------------------------------------+
CHAR_LENGTH or CHARACTER_LENGTH
char_length(str)
character_length(str)
- Description: This function returns the length of the
str
string, measured in characters.The length of a Chinese character is
1
. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Examples:
- Sample statement:
SELECT char_length('China');
Result:+----------------------+ | char_length('China') | +----------------------+ | 5 | +----------------------+
- Sample statement:
SELECT char_length('abc');
Result:+--------------------+ | char_length('abc') | +--------------------+ | 3 | +--------------------+
- Sample statement:
CONCAT
concat(str 1, …, str n)
- Description: This function concatenates strings. If an argument is
null
,null
is returned. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Examples:
- Sample statement:
SELECT concat('aliyun', ', ', 'analyticdb');
Result:+--------------------------------------+ | concat('aliyun', ', ', 'analyticdb') | +--------------------------------------+ | aliyun, analyticdb | +--------------------------------------+
- Sample statement:
SELECT concat('abc',null,'def');
Result:+--------------------------+ | concat('abc',null,'def') | +--------------------------+ | NULL | +--------------------------+
- Sample statement:
CONCAT_WS
concat_ws(separator, str 1, …, str n)
- Description: This function concatenates strings and separates them with delimiters.
The
separator
argument specifies the delimiter for other arguments.null
strings are skipped. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Examples:
- Sample statement:
SELECT concat_ws(',', 'First name', 'Second name', 'Last Name')AS result;
Result:+----------------------------------+ | result | +----------------------------------+ | First name,Second name,Last Name | +----------------------------------+
- Sample statement:
SELECT concat_ws(',','First name',NULL,'Last Name')AS result;
Result:+----------------------+ | result | +----------------------+ | First name,Last Name | +----------------------+
- Sample statement:
ELT
elt(N, str 1, ...,str n);
- Description: This function returns the
N
th string.If
N
is less than 1 or is greater than the number of string arguments,null
is returned. - Input value type:
N
is of the BIGINT type, andstr
is of the VARCHAR type. - Return value type: VARCHAR.
- Example:
SELECT elt(4, 'Aa', 'Bb', 'Cc', 'Dd');
Result:+--------------------------------+ | elt(4, 'Aa', 'Bb', 'Cc', 'Dd') | +--------------------------------+ | Dd | +--------------------------------+
ENCRYPT
encrypt(x, y);
- Description: This function encrypts the
x
argument by usingy
as the salt value. - Input value type:
x
is of the VARBINARY type, andy
is of the VARCHAR type. - Return value type: VARBINARY.
- Example:
SELECT encrypt('abdABC123','key');
Result:+--------------------------------------------------------+ | encrypt('abdABC123','key') | +--------------------------------------------------------+ | 0x6B657A617A6D63496F2E614377 | +--------------------------------------------------------+
EXPORT_SET
export_set(bits, onstr, offstr [, separator[,number_of_bits]]);
- Description: This function converts
bits
into a binary value. Argument description:- The system replaces 1 in the binary value with the
onstr
value and 0 with theoffstr
value. - The returned values are separated by the
separator
value. number_of_bits
specifies the number of bits that the system scans from right to left. If thenumber_of_bits
value is greater than 64 or is -1, 64 bits are returned.
- The system replaces 1 in the binary value with the
- Input value type:
bits
andnumber_of_bits
are of the BIGINT type, andonstr
,offstr
, andseparator
are of the VARCHAR type. - Return value type: VARCHAR.
- Examples:
- In the following statement, 5 is converted into a binary value. The two rightmost
bits of the binary value are returned. 1 is replaced with
a
, and 0 is replaced withb
. The returned values are separated by commas (,).SELECT export_set(5,'a','b',',',2);
Result:+-----------------------------+ | export_set(5,'a','b',',',2) | +-----------------------------+ | a,b | +-----------------------------+
- In the following statement, 6 is converted into a binary value. The two rightmost
bits of the binary value are returned.
1
and0
are not replaced with other values. The returned values are separated by commas (,).SELECT export_set(6,'1','0',',',10);
Result:+------------------------------+ | export_set(6,'1','0',',',10) | +------------------------------+ | 0,1,1,0,0,0,0,0,0,0 | +------------------------------+
- In the following statement, 5 is converted into a binary value. The two rightmost
bits of the binary value are returned. 1 is replaced with
FIELD
FIELD(str, str 1, str 2,..., str n);
- Description: This function returns the index position of the
str
value in several strings. If thestr
value is not found among the strings,0
is returned. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Example:
SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
Result:+-------------------------------------------+ | FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff') | +-------------------------------------------+ | 2 | +-------------------------------------------+
FIND_IN_SET
find_in_set(str, strlist)
- Description: This function returns the position of the
str
value in thestrlist
list.If the
str
value is not found instrlist
or ifstrlist
is an empty list,0
is returned.If
str
orstrlist
isnull
,null
is returned. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Example:
SELECT find_in_set('b','a,b,c,d');
Result:+----------------------------+ | find_in_set('b','a,b,c,d') | +----------------------------+ | 2 | +----------------------------+
FORMAT
format(X, D)
- This function formats the
X
number to the#,###,###.##
format rounded toD
decimal places and returns the result as a string.If
D
is0
, the result does not have the decimal point or fractional part. - Input value type:
X
is of the DOUBLE type, andD
is of the BIGINT type. - Return value type: VARCHAR.
- Example:
SELECT format(12332.123456, 4)AS result1, format(12332.1,4)AS result2, format(12332.2,0)AS result3;
Result:+-------------+-------------+---------+ | result1 | result2 | result3 | +-------------+-------------+---------+ | 12,332.1235 | 12,332.1000 | 12,332 | +-------------+-------------+---------+
FROM_BASE64
from_base64(x)
- Description: This function decodes the Base64-encoded
x
string and returns the result. - Input value type: VARBINARY or VARCHAR.
- Return value type: VARBINARY.
Note You can use the following methods to convert the VARBINARY-typed result into a VARCHAR value.
- If the AnalyticDB for MySQL cluster version is 3.1.4 or later, you can use the
CAST AS VARCHAR
function. For more information, see CAST functions. - If the AnalyticDB for MySQL cluster version is earlier than 3.1.4, you can use the
FROM_UTF8
function. For more information, see FROM_UTF8.
- If the AnalyticDB for MySQL cluster version is 3.1.4 or later, you can use the
- Examples:
- In the following statement, a VARCHAR-typed value is specified:
SELECT from_base64('Q2hpbmE=');
Result:+--------------------------------------------------+ | from_base64('Q2hpbmE=') | +--------------------------------------------------+ | 0x4368696E61 | +--------------------------------------------------+
Note The preceding returned value is of the VARBINARY type. If you want the system to return a VARCHAR-typed value, you can use the following statement:SELECT cast(from_base64('Q2hpbmE=') AS varchar);
Result:+------------------------------------------+ | cast(from_base64('Q2hpbmE=') AS varchar) | +------------------------------------------+ | China | +------------------------------------------+
- In the following statement, a VARBINARY-typed value is specified:
SELECT from_base64(cast(to_base64('China') AS varbinary));
Result:+--------------------------------------------------------------------------------------------------------+ | from_base64(cast(to_base64('China') AS varbinary)) | +--------------------------------------------------------------------------------------------------------+ | 0x4368696E61 | +--------------------------------------------------------------------------------------------------------+
- In the following statement, a VARCHAR-typed value is specified:
FROM_UTF8
from_utf8(x)
from_utf8(x, y)
- Description:
from_utf8(x)
: decodes the UTF-8 encodedx
value and returns the result.from_utf8(x, y)
: decodes thex
value that is not UTF-8 encoded and replaces it with a specific invalid character.Notey
is not required. If you do not specifyy
,�
is returned.- The
y
value can be an invalid character, such as a number sign (#
), or the ASCII value of the invalid character, such as35
.
- Input value type:
x
is of the VARBINARY type, andy
is of the VARCHAR or BIGINT type. - Return value type: VARCHAR.
- Examples:
- In the following statement, a UTF-8 encoded argument value is decoded:
SELECT from_utf8(to_utf8('hello'));
Result:+-----------------------------+ | from_utf8(to_utf8('hello')) | +-----------------------------+ | hello | +-----------------------------+
- In the following statement, an argument value that is not UTF-8 encoded is decoded:
SELECT from_utf8(unhex('58BF'));
Result:+--------------------------+ | from_utf8(unhex('58BF')) | +--------------------------+ | X� | +--------------------------+
- In the following statement, an argument value that is not UTF-8 encoded is decoded
and replaced with a number sign (
#
):SELECT from_utf8(unhex('58BF'), '#');
Result:+-------------------------------+ | from_utf8(unhex('58BF'), '#') | +-------------------------------+ | X# | +-------------------------------+
- In the following statement, an argument value that is not UTF-8 encoded is decoded
and replaced with an invalid character whose ASCII value is 35:
SELECT from_utf8(unhex('58BF'), '35');
Result:+-------------------------------+ | from_utf8(unhex('58BF'), '35') | +-------------------------------+ | X# | +-------------------------------+
- In the following statement, a UTF-8 encoded argument value is decoded:
HEX
hex(x)
- Description: This function converts the
x
value into a hexadecimal string. - Input value type: BIGINT or VARCHAR.
- Return value type: VARCHAR.
- Examples:
- In the following statement, a BIGINT-typed value is specified:
SELECT hex(16);
Result:+---------+ | hex(16) | +---------+ | 10 | +---------+
- In the following statement, a VARCHAR-typed value is specified:
SELECT hex('16');
Result:+-----------+ | hex('16') | +-----------+ | 3136 | +-----------+
- In the following statement, a BIGINT-typed value is specified:
INSTR
instr(str, substr)
- Description: This function returns the position of the first occurrence of the
substr
substring in thestr
string. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Example:
SELECT instr('foobarbar', 'bar');
Result:+---------------------------+ | instr('foobarbar', 'bar') | +---------------------------+ | 4 | +---------------------------+
LEFT
LEFT(str, len)
- Description: This function returns a number of leftmost characters of the
str
string. The number is specified by thelen
argument.If
str
orlen
isnull
,null
is returned. - Input value type:
str
is of the VARCHAR type, andlen
is of the BIGINT type. - Return value type: VARCHAR.
- Example:
SELECT LEFT('foobarbar', 5);
Result:+----------------------+ | LEFT('foobarbar', 5) | +----------------------+ | fooba | +----------------------+
LENGTH or OCTET_LENGTH
length(str)
octet_length(str)
- Description: This function returns the length of the
str
string. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Example:
SELECT length('aliyun');
Result:+------------------+ | length('aliyun') | +------------------+ | 6 | +------------------+
LIKE
expression [NOT] LIKE pattern [ESCAPE 'escape_char']
- Description: This function compares the values of
expression
andpattern
. If the values are the same,1
is returned. Otherwise,0
is returned. Argument description:- The
pattern
value can contain the following wildcards:%
: matches a string of any length._
: matches a single character.
escape_char
: escapes the percent signs(%)
and underscores(_)
in thepattern
value so that the percent signs(%)
and underscores(_)
that follow the escape character do not serve as wildcards.
- The
- Input value type: VARCHAR.
- Return value type: BIGINT.
- Examples:
- Sample statement:
SELECT 'David!' LIKE 'David_' AS result1, 'David!' NOT LIKE 'David_' AS result2, 'David!' LIKE '%D%v%' AS result3;
Result:+---------+---------+---------+ | result1 | result2 | result3 | +---------+---------+---------+ | 1 | 0 | 1 | +---------+---------+---------+
- Sample statement:
SELECT 'David_' LIKE 'David|_' ESCAPE '|';
Result:+----------------------------------+ | David_' LIKE 'David|_' ESCAPE '| | +----------------------------------+ | 1 | +----------------------------------+
- Sample statement:
LOCATE
locate(substr, str)
locate(substr, str, pos)
- Description: This function returns the position of the first occurrence of the
substr
substring in thestr
string, or returns the position of the first occurrence of thesubstr
substring in thestr
string, starting from thepos
position.If the
substr
substring is not found in thestr
string,0
is returned.If
substr
orstr
isnull
,null
is returned. - Input value type:
str
andsubstr
are of the VARCHAR type, andpos
is of the BIGINT type. - Return value type: BIGINT.
- Examples:
- Sample statement:
SELECT locate('bar', 'foobarbar');
Result:+----------------------------+ | locate('bar', 'foobarbar') | +----------------------------+ | 4 | +----------------------------+
- Sample statement:
SELECT locate('bar', 'foobarbar', 7);
Result:+-------------------------------+ | locate('bar', 'foobarbar', 7) | +-------------------------------+ | 7 | +-------------------------------+
- Sample statement:
LOWER or LCASE
lower(str)
lcase(str)
- Description: This function converts letters in the
str
string to lowercase. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Example:
SELECT lower('Aliyun');
Result:+-----------------+ | lower('Aliyun') | +-----------------+ | aliyun | +-----------------+
LPAD
lpad(str, len, padstr)
- Description: This function returns the
str
string that is left-padded with thepadstr
string to a length oflen
characters.If the length of the
str
string is greater thanlen
characters, the return value is shortened tolen
characters. - Input value type:
str
andpadstr
are of the VARCHAR type, andlen
is of the BIGINT type. - Return value type: VARCHAR.
- Example:
SELECT lpad('Aliyun',9,'#');
Result:+----------------------+ | lpad('Aliyun',9,'#') | +----------------------+ | ###Aliyun | +----------------------+
LTRIM
ltrim(str)
- Description: This function removes the leading spaces of the
str
string. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Example:
SELECT ltrim(' abc');
Result:+----------------+ | ltrim(' abc') | +----------------+ | abc | +----------------+
MAKE_SET
make_set(bits, str 1, str 2,...);
- Description: This function returns a set value, which is a string that contains substrings
separated by delimiters. The set value consists of strings that have the corresponding
bits within the bit set.
The
str1
string corresponds to bit0
. Thestr2
string corresponds to bit1
. The rest may be deduced by analogy. Thenull
values in thestr1
,str2
, and other strings are not appended to the result. - Input value type:
bits
is of the BIGINT type, andstr
is of the VARCHAR type. - Return value type: VARCHAR.
- Examples:
- Sample statement:
SELECT make_set(5,'hello','nice','world');
Result:+------------------------------------+ | make_set(5,'hello','nice','world') | +------------------------------------+ | hello,world | +------------------------------------+
- Sample statement:
SELECT make_set(1 | 4,'hello','nice',NULL,'world')AS result;
Result:+--------+ | result | +--------+ | hello | +--------+
- Sample statement:
MID
mid(str, pos, len)
- Description: This function serves the same purpose as SUBSTR or SUBSTRING. It returns a substring that contains
len
characters in length from thestr
string, starting from thepos
position. - Input value type:
str
is of the VARCHAR type, andpos
andlen
are of the BIGINT type. - Return value type: VARCHAR.
- Examples:
- Sample statement:
SELECT mid('Quadratically',5,6);
Result:+--------------------------+ | mid('Quadratically',5,6) | +--------------------------+ | ratica | +--------------------------+
- Sample statement:
SELECT mid('Sakila', -5, 3);
Result:+----------------------+ | mid('Sakila', -5, 3) | +----------------------+ | aki | +----------------------+
- Sample statement:
OCT
oct(N)
- Description: This function returns the octal string of the integer
N
.If
N
isnull
,null
is returned. - Input value type: BIGINT.
- Return value type: VARCHAR.
- Example:
SELECT oct(12);
Result:+---------+ | oct(12) | +---------+ | 14 | +---------+
ORD
ord(x)
- Description: This function returns the code of the leftmost character of the
x
string if the character is a multibyte character. - Input value type: VARBINARY or VARCHAR.
- Return value type: LONG.
- Examples:
- In the following statement, a VARCHAR-typed value is specified:
SELECT ord('China');
Result:+--------------+ | ord('China') | +--------------+ | 67 | +--------------+
- In the following statement, a VARBINARY-typed value is specified:
SELECT ord(cast('China' AS varbinary));
Result:+---------------------------------+ | ord(cast('China' AS varbinary)) | +---------------------------------+ | 67 | +---------------------------------+
- In the following statement, a VARCHAR-typed value is specified:
POSITION
position(substr IN str);
- Description: This function returns the position of the first occurrence of the
substr
substring in thestr
string, starting from position1
. If the substring is not found in the str string,0
is returned. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Example:
SELECT position('bar' in 'foobarbar');
Result:+--------------------------------+ | position('bar' in 'foobarbar') | +--------------------------------+ | 4 | +--------------------------------+
REPEAT
repeat(str, count);
- Description: This function returns a string that consists of the
str
string concatenated to itself the number of times specified bycount
.If the
count
value is less than 1, an empty string is returned.If
str
orcount
isnull
,null
is returned. - Input value type:
str
is of the VARCHAR type, andcount
is of the BIGINT type. - Return value type: VARCHAR.
- Examples:
- Sample statement:
SELECT repeat('a', 3);
Result:+----------------+ | repeat('a', 3) | +----------------+ | aaa | +----------------+
- Sample statement:
SELECT repeat('abc', null);
Result:+---------------------+ | repeat('abc', null) | +---------------------+ | NULL | +---------------------+
- Sample statement:
SELECT repeat(null, 3);
Result:+-----------------+ | repeat(null, 3) | +-----------------+ | NULL | +-----------------+
- Sample statement:
REPLACE
replace(str, from_str, to_str);
- Description: This function replaces all
from_str
strings in thestr
string with theto_str
string. - Input value type: VARCHAR.
- Return value type: VARCHAR.
-
Example:
SELECT replace('WWW.aliyun.com', 'W', 'w');
Result:+-------------------------------------+ | replace('WWW.aliyun.com', 'W', 'w') | +-------------------------------------+ | www.aliyun.com | +-------------------------------------+
REVERSE
reverse(str);
- Description: This function returns the
str
string with the order of the characters reversed. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Example:
SELECT reverse('123456');
Result:+-------------------+ | reverse('123456') | +-------------------+ | 654321 | +-------------------+
RIGHT
RIGHT(str, len);
- Description: This function returns the rightmost
len
characters of thestr
string.If
str
orlen
isnull
,null
is returned. - Input value type:
str
is of the VARCHAR type, andlen
is of the BIGINT type. - Return value type: VARCHAR.
- Example:
SELECT RIGHT('abc',3);
Result:+----------------+ | RIGHT('abc',3) | +----------------+ | abc | +----------------+
RLIKE or REGEXP
expression RLIKE pattern;
expression REGEXP pattern;
- Description: This function performs pattern matching of the
expression
string against the regular expression specified bypattern
. If the string matches the regular expression,1
is returned. Otherwise,0
is returned.If
expression
orpattern
isnull
,null
is returned. - Input value type: VARCHAR.
- Return value type: BOOLEAN.
- Examples:
- Sample statement:
SELECT 'Michael!' REGEXP '.*';
Result:+----------------------+ | Michael!' REGEXP '.* | +----------------------+ | 1 | +----------------------+
- Sample statement:
SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
Result:+-------------------------------------+ | new*\n*line' REGEXP 'new\\*.\\*line | +-------------------------------------+ | 0 | +-------------------------------------+
- Sample statement:
SELECT 'c' REGEXP '^[a-d]';
Result:+-------------------+ | c' REGEXP '^[a-d] | +-------------------+ | 1 | +-------------------+
- Sample statement:
RPAD
rpad(str, len, padstr)
- Description: This function returns the
str
string that is right-padded with thepadstr
string to a length oflen
characters.If the length of the
str
string is greater thanlen
characters, the return value is shortened tolen
characters. - Input value type:
str
andpadstr
are of the VARCHAR type, andlen
is of the BIGINT type. - Return value type: VARCHAR.
- Example:
SELECT rpad('Aliyun',9,'#');
Result:+----------------------+ | rpad('Aliyun',9,'#') | +----------------------+ | Aliyun### | +----------------------+
RTRIM
rtrim(str)
- Description: This function removes the trailing spaces of the
str
string. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Example:
SELECT rtrim('barbar ');
Result:+--------------------+ | rtrim('barbar ') | +--------------------+ | barbar | +--------------------+
SPACE
space(N);
- Description: This function returns a string that consists of a specific number of
spaces.
Note We recommend that you use this function together with the
contact()
function to facilitate display of results. - Input value type: BIGINT.
- Return value type: VARCHAR.
- Example:
SELECT concat("#", space(6), "#");
Result:+----------------------------+ | concat("#", space(6), "#") | +----------------------------+ | # # | +----------------------------+
SPLIT
split(string, delimiter)
- Description: This function splits the
string
with thedelimiter
and returns an array. - Input value type: VARCHAR.
- Return value type: ARRAY<varchar>.
- Example:
SELECT split('1#2#3', '#'), split('#1#2#3#', '#'), split('123', '#');
Result:+---------------------+-----------------------+-------------------+ | split('1#2#3', '#') | split('#1#2#3#', '#') | split('123', '#') | +---------------------+-----------------------+-------------------+ | ["1","2","3"] | ["","1","2","3",""] | ["123"] | +---------------------+-----------------------+-------------------+
SPLIT_PART
split_part(string, delimiter, index)
- Description: This function splits the
string
with thedelimiter
and returns the substring whose array subscript is theindex
value. Theindex
value starts from 1. If the value of index is greater than the number of substrings, NULL is returned. - Input value type:
string
anddelimiter
are of the VARCHAR type, andindex
is of the BIGINT type. - Return value type: VARCHAR.
- Example:
SELECT split_part('A#B#C', '#', 2), split_part('A#B#C', '#', 4);
Result:+-----------------------------+-----------------------------+ | split_part('A#B#C', '#', 2) | split_part('A#B#C', '#', 4) | +-----------------------------+-----------------------------+ | B | NULL | +-----------------------------+-----------------------------+
SPLIT_TO_MAP
split_to_map(string, entryDelimiter, keyValueDelimiter)
- Description: This function splits a string by using
entryDelimiter
andkeyValueDelimiter
and returns amap
. The string is split byentryDelimiter
intokey-value pairs
, and thekey-value pairs
are split bykeyValueDelimiter
intokeys
andvalues
. - Input value type: VARCHAR.
- Return value type: MAP<varchar, varchar>.
- Example:
SELECT split_to_map('k1:v1,k2:v2', ',', ':'),split_to_map('', ',', ':');
Result:+---------------------------------------+----------------------------+ | split_to_map('k1:v1,k2:v2', ',', ':') | split_to_map('', ',', ':') | +---------------------------------------+----------------------------+ | {"k1":"v1","k2":"v2"} | {} | +---------------------------------------+----------------------------+
STRCMP
strcmp(str 1, str 2);
- Description: This function compares the
str 1
andstr 2
strings. If they are identical,0
is returned. If thestr 1
value is smaller than thestr2
value,-1
is returned. Otherwise,1
is returned. - Input value type: VARCHAR.
- Return value type: BIGINT.
- Example:
SELECT strcmp('text', 'text2');
Result:+-------------------------+ | strcmp('text', 'text2') | +-------------------------+ | -1 | +-------------------------+
SUBSTR or SUBSTRING
substr(str, pos)
substr(str FROM pos)
substr(str, pos, len)
substr(str FROM pos FOR len)
substring(str, pos)
substring(str FROM pos)
substring(str, pos, len)
substring(str FROM pos FOR len)
- Description:
SUBSTRING(varchar str, bigint pos)
orSUBSTRING(varchar str FROM pos)
: returns the substring that starts from thepos
position to the end of the string. If thepos
value is less than 0, the substring starts from the position that is pos characters away from the end of the string.SUBSTRING(varchar str, bigint pos, bigint len)
orSUBSTRING(varchar str FROM pos FOR len)
: returns a substring that containslen
characters in length from the string, starting from thepos
position. If thepos
value is less than 0, the substring starts from the position that is pos characters away from the end of the string.
- Input value type:
str
is of the VARCHAR type, andpos
andlen
are of the BIGINT type. - Return value type: VARCHAR.
- Examples:
- Sample statement:
SELECT substr('helloworld', 6);
Result:+-------------------------+ | substr('helloworld', 6) | +-------------------------+ | world | +-------------------------+
- Sample statement:
SELECT substr('helloworld' FROM 6);
Result:+-----------------------------+ | substr('helloworld' FROM 6) | +-----------------------------+ | world | +-----------------------------+
- Sample statement:
SELECT substr('helloworld', 6, 3);
Result:+----------------------------+ | substr('helloworld', 6, 3) | +----------------------------+ | wor | +----------------------------+
- Sample statement:
SELECT substr('helloworld' from 6 for 3);
Result:+-----------------------------------+ | substr('helloworld' FROM 6 FOR 3) | +-----------------------------------+ | wor | +-----------------------------------+
- Sample statement:
SUBSTRING_INDEX
substring_index(str, delim, count)
- Description: This function returns the substring before the last occurrence of the
delim
delimiter in thestr
string.If the
count
value is greater than 0, this function returns all characters to the left of the last occurrence of thedelim
delimiter.If the
count
value is less than 0, this function returns all characters to the right of the last occurrence of thedelim
delimiter.The
SUBSTRING_INDEX
function performs a case-sensitive match when it searches for thedelim
delimiter. - Input value type:
str
anddelim
are of the VARCHAR type, andcount
is of the BIGINT type. - Return value type: VARCHAR.
- Example:
SELECT substring_index('www.aliyun.com', '.', 2);
Result:+-------------------------------------------+ | substring_index('www.aliyun.com', '.', 2) | +-------------------------------------------+ | www.aliyun | +-------------------------------------------+
TO_BASE64
to_base64(x)
- Description: This function returns the
x
string encoded in Base64 format. - Input value type: VARBINARY or VARCHAR.
- Return value type: VARCHAR.
- Examples:
- In the following statement, a VARCHAR-typed value is specified:
SELECT to_base64('China');
Result:+--------------------+ | to_base64('China') | +--------------------+ | Q2hpbmE= | +--------------------+
- In the following statement, a VARBINARY-typed value is specified:
SELECT to_base64(cast('China' AS varbinary));
Result:+---------------------------------------+ | to_base64(cast('China' AS varbinary)) | +---------------------------------------+ | Q2hpbmE= | +---------------------------------------+
- In the following statement, a VARCHAR-typed value is specified:
TO_UTF8
to_utf8(x)
- Description: This function returns the
x
string encoded in UTF-8 format. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Example:
SELECT to_utf8('China');
Result:+------------------------------------+ | to_utf8('China') | +------------------------------------+ | 0x4368696E61 | +------------------------------------+
TRIM
trim([remstr FROM] str)
trim([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
- Description: This function removes the leading and trailing spaces or the characters
contained in the optional
remstr
string from thestr
string. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Examples:
- Sample statement:
SELECT trim(' bar ');
Result:+------------------+ | trim(' bar ') | +------------------+ | bar | +------------------+
- Sample statement:
SELECT trim(BOTH 'x' FROM 'xxxbarxxx');
Result:+---------------------------------+ | trim(BOTH 'x' FROM 'xxxbarxxx') | +---------------------------------+ | bar | +---------------------------------+
- Sample statement:
SELECT trim(LEADING 'x' FROM 'xxxbarxxx');
Result:+------------------------------------+ | trim(LEADING 'x' FROM 'xxxbarxxx') | +------------------------------------+ | barxxx | +------------------------------------+
- Sample statement:
SELECT trim(TRAILING 'x' from 'xxxbarxxx');
Result:+-------------------------------------+ | trim(TRAILING 'x' from 'xxxbarxxx') | +-------------------------------------+ | xxxbar | +-------------------------------------+
- Sample statement:
UPPER or UCASE
upper(str)
ucase(str)
- Description: This function converts the letters in the
str
string to uppercase. - Input value type: VARCHAR.
- Return value type: VARCHAR.
- Example:
SELECT upper('Aliyun');
Result:+-----------------+ | upper('Aliyun') | +-----------------+ | ALIYUN |
UNHEX
unhex(x);
- Description: This function interprets each pair of hexadecimal digits in the
x
argument as a number and converts it to a character. - Input value type: VARBINARY or VARCHAR.
- Return value type: VARBINARY.
Note
- You can use the following methods to convert the VARBINARY-typed result into a VARCHAR
value.
- If the AnalyticDB for MySQL cluster version is 3.1.4 or later, you can use the
CAST AS VARCHAR
function. For more information, see CAST functions. - If the AnalyticDB for MySQL cluster version is earlier than 3.1.4, you can use the
FROM_UTF8
function. For more information, see FROM_UTF8.
- If the AnalyticDB for MySQL cluster version is 3.1.4 or later, you can use the
- If the input value contains nonhexadecimal digits, NULL is returned.
- You can use the following methods to convert the VARBINARY-typed result into a VARCHAR
value.
- Examples:
- In the following statement, a VARCHAR-typed value is specified:
SELECT unhex(hex('China'));
Result:+------------------------------------------+ | unhex(hex('China')) | +------------------------------------------+ | 0x4368696E61 | +------------------------------------------+
Note The preceding returned value is of the VARBINARY type. If you want the system to return a VARCHAR-typed value, you can use the following statement:SELECT cast(unhex(hex('China')) AS varchar);
Result:+--------------------------------------+ | cast(unhex(hex('China')) AS varchar) | +--------------------------------------+ | China | +--------------------------------------+
- In the following statement, a VARBINARY-typed value is specified:
SELECT unhex(cast(hex('China') AS varbinary));
Result:+--------------------------------------------------------------------------------+ | unhex(cast(hex('China') AS varbinary)) | +--------------------------------------------------------------------------------+ | 0x4368696E61 | +--------------------------------------------------------------------------------+
- In the following statement, a VARCHAR-typed value is specified: