字符串函数 - 云原生数仓 AnalyticDB MySQL

AnalyticDB for MySQL支持以下字符串函数。

ASCII：返回字符或者字符串最左边字符对应的ASCII值。
BIN：返回整数的二进制字符串。
BIT_LENGTH：以位为单位返回参数str的长度。
CHAR：返回整数对应的ASCII码组成的字符串。
CHAR_LENGTH/CHARACTER_LENGTH：以字符为单位返回字符串的长度。
CONCAT：连接字符串。
CONCAT_WS：连接字符串，字符串中间以分隔串间隔。
ELT：返回整数N指定的字符串。
EXPORT_SET：根据bits中的比特值，返回组合后的字符串。
FIELD：返回指定字符串在字符串列表中的索引位置。

FIND_IN_SET：返回字符或字符串在另一个字符串中的位置。
FORMAT：将数字N格式化，返回字符串。
HEX：返回整数对应的十六进制字符串，或者返回字符串中每个字符对应的十六进制数所组成的字符串。
INSTR：返回字符串中子字符串首次出现的位置。
LEFT：从字符串最左边开始，返回N个字符。
LENGTH/OCTET_LENGTH：字符串长度。
LIKE：简单的模式匹配。
LOCATE：返回字符串首次出现在另一个字符串中的位置信息。
LOWER/LCASE：将字符串转换为小写。
LPAD：左拼接字符串。

LTRIM：删除字符串的前导空格。
MAKE_SET：返回一组以逗号分隔的字符串。
MID：从字符串的指定位置开始，返回指定长度的子字符串。作用同SUBSTR/SUBSTRING。
OCT：返回指定整数的八进制字符串表示形式。
POSITION：返回字符串中子字符串首次出现的位置。
REPEAT：返回字符串重复多次后的字符串。
REPLACE：用指定字符串替换另一个字符串中的部分字符。
REVERSE：将字符串逆序。
SPLIT：将字符串按分隔符进行分隔，并返回数组。

RIGHT：返回字符串最右边的指定数量的字符。
RLIKE/REGEXP：将字符串expression与pattern进行正则匹配，匹配成功返回1，否则返回0。
RPAD：右拼接字符串。
RTRIM：删除字符串的后置空格。
SPACE：返回由指定数量空格组成的字符串。
STRCMP：根据两个字符串的大小，返回0、1或者-1。
SUBSTR/SUBSTRING：返回从指定位置开始的指定长度的子字符串。
SUBSTRING_INDEX：返回字符串str中最后一次分隔符delim出现之前的子字符串。
TRIM：删除字符串前后所有的空格。
UPPER/UCASE：将字符串转换为大写。

ORD：如果字符串最左边的字符是多字节字符，则返回该字符的代码。
UNHEX：将参数中的每对字符转换为十六进制形式，再将其转换为数字表示的字节，最后以二进制字符串形式返回结果。
ENCRYPT：对字符串进行加密。
TO_BASE64：返回字符串的BASE64编码形式。
FROM_BASE64：解码BASE64编码的字符串并返回结果。

ASCII

ASCII(varchar str)

命令说明：返回字符str或者字符串str最左边字符对应的十进制ASCII值。
返回值类型：BIGINT。

示例：

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

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

BIN

BIN(bigint N)

命令说明：返回N的二进制字符串。
如果N为null，则返回结果为null。
返回值类型：VARCHAR。

示例：

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

BIT_LENGTH

BIT_LENGTH(varchar str)

命令说明：以位为单位返回参数str的长度。
返回值类型：BIGINT。

示例：

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

    select bit_length('中国');
    +--------------------+
    | bit_length('中国') |
    +--------------------+
    |                 48 |

CHAR

CHAR(bigint N1, bigint N2...)

命令说明：返回整数N1、N2…对应的十进制ASCII码组成的字符串。
返回值类型：VARBINARY。

示例：

    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)

命令说明：以字符为单位返回字符串str的长度。
一个汉字所对应的字符长度是1。
返回值类型：BIGINT。

示例：

    select char_length('中国');
    +---------------------+
    | char_length('中国') |
    +---------------------+
    |                   2 |

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

CONCAT

concat(varchar str1, …, varchar strn)

命令说明：字符串连接操作，其中任何一个参数为null，则返回值为null。
返回值类型：VARCHAR。

示例：

    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)

命令说明：字符串连接操作，第一个参数separator是其余参数的分隔符，连接时会跳过任何为null值的字符串。
返回值类型：VARCHAR。

示例：

    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,...)

命令说明：返回第N个字符串。
若N<1或大于后面字符串参数的数量，则返结果为null。
返回值类型：VARCHAR。

示例：

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

命令说明：返回一个字符串，根据整数bits的二进制位01值，从右到左（从低位到高位）放置on、off字符串。1的位置放on字符串，0的位置放off字符串，由分隔符字符串（默认为逗号字符）分隔。检查位数由number_of_bits指定，如果未指定，默认值为64。
如果检查位数大于64，number_of_bits将被静默剪裁为64。

检查位数为-1、64，返回结果相同。
返回值类型：VARCHAR。

示例：

    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,...)

命令说明：返回str在str1、str2、str3，...列表中的索引位置。如果未找到str，则返回0。
返回值类型：BIGINT。

示例：

    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)

命令说明：返回str在列表strlist中的位置。
如果str不在strlist中或者strlist是空字符串，返回结果为0。

如果str、strlist任一参数为null，返回结果为null。
返回值类型：BIGINT。

示例：

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

FORMAT

format(double X, bigint D)

命令说明：将数字X格式化为#,###,###.##样式，舍入到D小数位，并将结果作为字符串返回。
如果D为0，则返回结果没有小数点或小数部分。
返回值类型：BIGINT。

示例：

    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)

命令说明：返回整数N所对应的十六进制字符串，或者返回str中每个字符对应的十六进制数所组成的字符串。
返回值类型：VARCHAR。

示例：

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

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

INSTR

INSTR(varchar str, varchar substr)

命令说明：返回字符串str中子字符串substr首次出现的位置。
返回值类型：BIGINT。

示例：

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

LEFT

LEFT(varchar str, bigint len)

命令说明：返回字符串str中最左边的len个字符。
如果str或者len为null，则返回结果为null。
返回值类型：VARCHAR。

示例：

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

LENGTH/OCTET_LENGTH

length(varchar str)

命令说明：返回字符串str的长度。
返回值类型：BIGINT。

示例：

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

LIKE

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

命令说明：LIKE运算符用于将字符串expression与pattern进行匹配，匹配成功返回1，匹配失败返回0。
pattern为通配符模式，通配符包括：
- %：匹配任意长度的字符串。
- _：匹配单个字符。
escape_char：对pattern中的%、_进行转义，使得转义字符后面的%、_不作通配符使用。
返回值类型：BIGINT。

示例：

    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)

命令说明：返回字符串str中首次出现substr的位置信息，或者返回字符串str中从指定位置pos开始首次出现substr的位置信息。
如果substr不在str中，返回结果为0。

如果substr或者str为null，返回结果为null。
返回值类型：BIGINT。

示例：

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

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

LOWER/LCASE

lower(varchar str)

命令说明：将字符串str中的字母转换为小写。
返回值类型：VARCHAR。

示例：

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

LPAD

LPAD(varchar str, bigint len, varchar padstr)

命令说明：将字符串str左边拼接padstr直到长度达到len，并返回拼接后的字符串。
如果str长于len，则返回值将缩短为len个字符。
返回值类型：VARCHAR。

示例：

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

LTRIM

LTRIM(varchar str)

命令说明：删除字符串str所有前导空格。
返回值类型：VARCHAR。

示例：

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

MAKE_SET

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

命令说明：返回一个设置值（包含由字符分隔的子字符串的字符串），其中包含具有相应位设置的字符串。
str1对应于0位，str2对应于1位，依此类推。str1，str2，...中的null值不会附加到结果中。
返回值类型：VARCHAR。

示例：

    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)

命令说明：与SUBSTR/SUBSTRING功能相同，从字符串str的pos开始返回len长度的子字符串。
返回值类型：VARCHAR。

示例：

    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)

命令说明：返回整数N的八进制字符串表示形式。
如果N为null，返回结果为null。
返回值类型：VARCHAR。

示例：

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

POSITION

POSITION(varchar substr IN varchar str)

命令说明：返回字符串str中子字符串substr首次出现位置，位置从1开始，如果未找到则返回0。
返回值类型：BIGINT。

示例：

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

REPEAT

REPEAT(varchar str, bigint count)

命令说明：返回由字符串str重复count次数组成的字符串。
如果count<1，则返回空字符串。

如果str或count为null，则返回null。
返回值类型：VARCHAR。

示例：

    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)

命令说明：将str中的from_str内容替换为to_str。
返回值类型：VARCHAR。

示例：

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

REVERSE

REVERSE(varchar str)

命令说明：返回str逆序后的字符串。
返回值类型：VARCHAR。

示例：

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

SPLIT

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

命令说明：
- split(string, delimiter)：将字符串string按分隔符delimiter进行分隔，并返回数组。
- split_part(string, delimiter, index)：将字符串string按分隔符delimiter分隔，并返回分隔后数组下标为index的子串，index以1开头，如果大于字段数则返回null。
- split_to_map(string, entryDelimiter, keyValueDelimiter)：通过entryDelimiter和keyValueDelimiter拆分字符串并返回map。entryDelimiter将字符串分解为key-value对，keyValueDelimiter将每对分隔成key、value。
返回值类型：VARCHAR或map<varchar, varchar>。

示例：

    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)

命令说明：返回字符串str中最右边的len个字符。
如果str或者len为null，返回结果为null。
返回值类型：VARCHAR。

示例：

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

RLIKE/REGEXP

expression  RLIKE pattern
expression  REGEXP pattern

命令说明：将字符串expression与pattern进行正则匹配，匹配成功返回1，否则返回0。
如果expression或者pattern为null，返回结果为null。
返回值类型：BIGINT。

示例：

    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)

命令说明：将字符串str右边拼接padstr直到长度达到len，并返回拼接后的字符串。
如果str长于len，则返回值将缩短为len个字符。
返回值类型：VARCHAR。

示例：

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

RTRIM

RTRIM(varchar str)

命令说明：删除字符串str所有后置空格。
返回值类型：VARCHAR。

示例：

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

SPACE

SPACE(bigint N)

命令说明：返回由指定数量空格组成的字符串。
返回值类型：VARCHAR。

示例：

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

STRCMP

STRCMP(varchar str1, varchar str2)

命令说明：如果字符串str1、str1相同，返回结果为0。如果str1根据当前排序顺序小于str2，返回结果为-1，否则返回结果为1。
返回值类型：BIGINT。

示例：

    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)

命令说明：
- SUBSTRING(varchar str, bigint pos)、SUBSTRING(varchar str FROM pos)返回从pos位置开始到字符串结束的子串。如果pos<0，则起始位置从字符串的末尾开始倒数。
- SUBSTRING(varchar str, bigint pos, bigint len)、SUBSTRING(varchar str FROM pos FOR len)返回从pos位置开始长度为len的子串。如果pos<0，则起始位置从字符串的末尾开始倒数。
返回值类型：VARCHAR。

示例：

    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)

命令说明：返回字符串str中最后一次分隔符delim出现之前的子字符串。
如果count>0，返回最后一次delim左侧的所有内容，即从左侧开始计算。

如果count<0，返回最后一次delim右侧的所有内容，即从右侧开始计算。

搜索delim时，SUBSTRING_INDEX函数区分大小写。
返回值类型：VARCHAR。

示例：

    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)

命令说明：通过删除前导空格和尾随空格或删除与可选的指定字符串匹配的字符来剪裁字符串。
返回值类型：VARCHAR。

示例：

    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)

命令说明：将字符串str中的字母转换为大写。
返回值类型：VARCHAR。

示例：

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

ORD

ord(varbinary x)

命令说明：如果字符串x最左边的字符是多字节字符，则返回该字符的代码。
返回值类型：LONG

示例：

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

UNHEX

unhex(varbinary x)

命令说明：将参数x中的每对字符解释为十六进制数，并将其转换为数字表示的字节，以二进制字符串将其返回。
返回值类型：VARBINARY

示例：

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

ENCRYPT

encrypt(varbinary x, varchar y)

命令说明：对参数x进行加密，y为Salt值。
返回值类型：VARBINARY

示例：

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

TO_BASE64

to_base64(varbinary x)

命令说明：返回字符串x的BASE64编码形式。
返回值类型：VARCHAR

示例：

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

FROM_BASE64

from_base64(varbinary x)

命令说明：解码BASE64编码的字符串x并返回结果。
返回值类型：VARBINARY

示例：

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