字符串函数

ASCII

• 命令格式

  bigint ascii(string <str>)

• 命令说明

  返回字符串str第一个字符的ASCII码。

• 参数说明

  str：必填。STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算。

• 返回值说明

  返回BIGINT类型。返回规则如下：

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • str值为NULL时，返回NULL。

• 示例

  • 示例1：返回字符串abcde第一个字符的ASCII码。命令示例如下。

    --返回97。
    select ascii('abcde'); 

  • 示例2：输入参数为NULL。命令示例如下。

    --返回NULL。
    select ascii(null);

CHAR_MATCHCOUNT

• 命令格式

  bigint char_matchcount(string <str1>, string <str2>)

• 命令说明

  计算str1中有多少个字符出现在str2中。

• 参数说明

  str1、str2：必填。STRING类型，必须为有效的UTF-8字符串。如果对比过程中发现有无效字符（非UNICODE编码），则返回负值。

• 返回值说明

  返回BIGINT类型。str1或str2值为NULL时，返回NULL。

• 示例

  • 示例1：计算字符串aabc出现在abcde中的字符个数。命令示例如下。

    --返回4。
    select char_matchcount('aabc','abcde');

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select char_matchcount(null,'abcde');

CHR

• 命令格式

  string chr(bigint <ascii>)

• 命令说明

  将指定ASCII码转换为字符。

• 参数说明

  ascii：必填。BIGINT类型的ASCII值。取值范围为0~128。如果输入为STRING、DOUBLE或DECIMAL类型，则会隐式转换为BIGINT类型后参与运算。

• 返回值说明

  返回STRING类型。返回规则如下：

  • ascii值不在取值范围内时，返回报错。

  • ascii非BIGINT、STRING、DOUBLE或DECIMAL类型时，返回报错。

  • ascii值为NULL时，返回NULL。

• 示例

  • 示例1：将ASCII码100转换为字符。命令示例如下。

    --返回d。
    select chr(100);

  • 示例2：输入参数为NULL。命令示例如下。

    --返回NULL。
    select chr(null);

  • 示例3：输入为STRING类型字符。命令示例如下。

    --隐式转换为BIGINT类型后参与运算，返回d。
    select chr('100');

CONCAT

• 命令格式

  string concat(string <str1>, string <str2>[,...])

• 命令说明

  将多个字符串连接在一起，生成一个新的字符串。

• 参数说明

  • a、b：必填。ARRAY数组。array<T>中的T指代ARRAY数组元素的数据类型，数组中的元素可以为任意类型。a和b中元素的数据类型必须一致。数组中的元素为NULL值时会参与运算。

  • str1、str2：必填。STRING类型。如果输入参数为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算，其他类型会返回报错。

• 返回值说明

  返回STRING类型。如果没有参数或任一参数为NULL，返回结果为NULL。

• 示例

  • 示例1：连接字符串aabc和abcde。命令示例如下。

    --返回aabcabcde。
    select concat('aabc','abcde');

  • 示例2：输入为空。命令示例如下。

    --返回NULL。
    select concat();

  • 示例3：任一字符串输入为NULL。命令示例如下。

    --返回NULL。
    select concat('aabc', 'abcde', null);

CONCAT_WS

• 命令格式

  string concat_ws(string <separator>, string <str1>, string <str2>[,...])

• 命令说明

  返回将参数中的所有字符串或ARRAY数组中的元素按照指定的分隔符连接在一起的结果。

• 参数说明

  • separator：必填。STRING类型的分隔符。

  • str1、str2：至少要指定2个字符串。STRING类型。如果输入为BIGINT、DECIMAL、DOUBLE或DATETIME类型，则会隐式转换为STRING类型后参与运算。

• 返回值说明

  返回STRING类型或STRUCT类型。返回规则如下：

  • str1或str2非STRING、BIGINT、DECIMAL、DOUBLE或DATETIME类型时，返回报错。

  • 如果没有输入参数或任一输入参数值为NULL，返回NULL。

• 示例

  • 示例1：将字符串name和hanmeimei通过:连接。命令示例如下。

    --返回name:hanmeimei。
    select concat_ws(':','name','hanmeimei');

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select concat_ws(':','avg',null,'34');

FIND_IN_SET

• 命令格式

  bigint find_in_set(string <str1>, string <str2>)

• 命令说明

  查找字符串str1在以逗号（,）分隔的字符串str2中的位置，从1开始计数。

• 参数说明

  • str1：必填。STRING类型。待查找的字符串。

  • str2：必填。STRING类型。以逗号（,）分隔的字符串。

• 返回值说明

  返回BIGINT类型。返回规则如下：

  • 当str2中无法匹配到str1或str1中包含逗号（,）时，返回0。

  • 当str1或str2值为NULL时，返回NULL。

• 示例

  • 示例1：查找字符串ab在字符串abc,hello,ab,c中的位置。命令示例如下。

    --返回3。
    select find_in_set('ab', 'abc,hello,ab,c');

  • 示例2：查找字符串hi在字符串abc,hello,ab,c中的位置。命令示例如下。

    --返回0。
    select find_in_set('hi', 'abc,hello,ab,c');

  • 示例3：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select find_in_set(null, 'abc,hello,ab,c');

FORMAT_NUMBER

• 命令格式

  string format_number(float|double|decimal <expr1>, int <expr2>)

• 命令说明

  将expr1转化为满足expr2格式的字符串。

• 参数说明

  • expr1：必填。FLOAT、DOUBLE、DECIMAL类型。需要格式化的数据。

  • expr2：必填。INT类型，取值范围为0~340。指代需要保留的小数位数。也可以为类似#,###,###.##格式的描述。不同取值返回的小数位数不同。

• 返回值说明

  返回STRING类型。返回规则如下：

  • 当0<expr2≤340时，四舍五入到小数点后指定位数。

  • 当expr2=0时，只保留整数，无小数点或小数部分。

  • 当expr2<0或expr2>340时，会返回报错。

  • expr1或expr2值为空或NULL时，返回NULL。

• 示例

  • 示例1：对给定数字按照指定格式输出。命令示例如下。

    --返回5.230。
    select format_number(5.230134523424545456,3);
    --返回12,332.123。
    select format_number(12332.123456, '#,###,###,###.###');

  • 示例2：任一输入参数为空或NULL。命令示例如下。

    --返回NULL。
    select format_number('',3);
    --返回NULL。
    select format_number(null,3);

INITCAP

• 命令格式

  string initcap(<str>)

• 命令说明

  将str转换为固定格式的字符串，单词之间以空格分隔，转换后的格式为：字符串中每个单词首字母大写，其余小写。

• 参数说明

  str：必填。STRING类型。输入的字符串。

• 返回值说明

  返回一个字符串，字符串中每个单词首字母大写，其余变为小写。

• 示例

  --返回Odps Sql。
  SELECT initcap("oDps sql");

INSTR

• 命令格式

  bigint instr(string <str1>, string <str2>[, bigint <start_position>[, bigint <nth_appearance>]])

• 命令说明

  计算子串str2在字符串str1中的位置。

• 参数说明

  • str1：必填。STRING类型。待搜索的目标字符串。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算，其他类型会返回报错。

  • str2：必填。STRING类型。待匹配的子串。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算，其他类型会返回报错。

  • start_position：可选。BIGINT类型，其他类型会返回报错。表示从str1的第几个字符开始搜索，默认起始位置是第一个字符位置1。当start_position为负数时表示开始位置是从字符串的结尾往前倒数，最后一个字符是-1，依次往前倒数。

  • nth_appearance：可选。BIGINT类型，大于0。表示str2在str1中第nth_appearance次匹配的位置。如果nth_appearance为其他类型或小于等于0，则返回报错。

• 返回值说明

  返回BIGINT类型。返回规则如下：

  • 如果在str1中未找到str2，则返回0。

  • 如果str2为空串，则总能匹配成功，例如select instr('abc','');会返回1。

  • str1、str2、start_position或nth_appearance值为NULL时，返回NULL。

• 示例

  • 示例1：计算字符e在字符串Tech on the net中的位置。命令示例如下。

    --返回2。
    select instr('Tech on the net', 'e');

  • 示例2：计算子串on在字符串Tech on the net中的位置。命令示例如下。

    --返回6。
    select instr('Tech on the net', 'on');

  • 示例3：计算字符e在字符串Tech on the net中，从第3个字符开始，第2次出现的位置。命令示例如下。

    --返回14。
    select instr('Tech on the net', 'e', 3, 2);

  • 示例4：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select instr('Tech on the net', null);

KEYVALUE

• 命令格式

  keyvalue(string <str>,[string <split1>,string <split2>,] string <key>)
  keyvalue(string <str>,string <key>) 

• 命令说明

  将字符串str按照split1分成Key-Value对，并按split2将Key-Value对分开，返回key所对应的Value。

• 参数说明

  • str：必填。STRING类型。待拆分的字符串。

  • split1、split2：可选。STRING类型。用于作为分隔符的字符串，按照指定的两个分隔符拆分源字符串。如果表达式中没有指定这两项，默认split1为";"，split2为":"。当某个被split1拆分后的字符串中有多个split2时，返回结果未定义。

  • key：必填。STRING类型。将字符串按照split1和split2拆分后，返回key值对应的Value。

• 返回值说明

  返回STRING类型。返回规则如下：

  • split1或split2值为NULL时，返回NULL。

  • str或key值为NULL或没有匹配的key时，返回NULL。

  • 如果有多个Key-Value匹配，返回第一个匹配上的key对应的Value。

• 示例

  • 示例1：将字符串0:1\;1:2拆分为Key-Value对，返回Key值1对应的Value。命令示例如下。

    --返回2。
    select keyvalue('0:1\;1:2', 1);

    没有指定split1和split2，默认split1为";"，split2为":"。

    经过split1拆分后，Key-Value对为0:1\,1:2。经过split2拆分后变为如下。

    0 1/  
    1 2

    返回Key为1所对应的Value值2。

  • 示例2：将字符串“\;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\;”按照“\;”拆分为Key-Value对，再按照":"将Key-Value分开，返回Key值tf对应的Value。命令示例如下。

    --返回21910。
    select keyvalue("\;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\;","\;",":","tf");

    将“\;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\;”按照“\;”拆分后，得出的Key-Value对如下所示。

    decreaseStore:1，xcard:1，isB2C:1，tf:21910，cart:1，shipping:2，pf:0，market:shoes，instPayAmount:0 

    按照":"拆分后，结果如下所示。

    decreaseStore 1  
    xcard 1  
    isB2C 1  
    tf 21910  
    cart 1  
    shipping 2  
    pf 0  
    market shoes  
    instPayAmount 0

    返回Key为tf对应的Value值21910。

LENGTH

• 命令格式

  bigint length(string <str>)

• 命令说明

  计算字符串str的长度。

• 参数说明

  str：必填。STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算。

• 返回值说明

  返回BIGINT类型。返回规则如下：

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • str值为NULL时，返回NULL。

  • str为非UTF-8编码格式时，返回-1。

• 示例

  • 示例1：计算字符串Tech on the net的长度。命令示例如下。

    --返回15。
    select length('Tech on the net');

  • 示例2：输入参数为NULL。命令示例如下。

    --返回NULL。
    select length(null);

LENGTHB

• 命令格式

  bigint lengthb(string <str>)

• 命令说明

  计算字符串str以字节为单位的长度。

• 参数说明

  str：必填。STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算。

• 返回值说明

  返回BIGINT类型。返回规则如下：

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • str值为NULL时，返回NULL。

• 示例

  • 示例1：计算字符串Tech on the net以字节为单位的长度。命令示例如下。

    --返回15。
    select lengthb('Tech on the net');

  • 示例2：输入参数为NULL。命令示例如下。

    --返回NULL。
    select lengthb(null);

LOCATE

• 命令格式

  bigint locate(string <substr>, string <str>[, bigint <start_pos>]) 

• 命令说明

  在str中查找substr的位置。您可以通过start_pos指定开始查找的位置，从1开始计数。

• 参数说明

  • substr：必填。STRING类型。待查找的字符串。

  • str：必填。STRING类型。待匹配的字符串。

  • start_pos：可选。BIGINT类型。指定查找的起始位置。

• 返回值说明

  返回为BIGINT类型。返回规则如下：

  • str中无法匹配到substr时，返回0。

  • str或substr值为NULL时，返回NULL。

  • start_pos值为NULL时，返回0。

• 示例

  • 示例1：查找字符串ab在字符串abchelloabc中的位置。命令示例如下。

    --返回1。
    select locate('ab', 'abchelloabc');

  • 示例2：查找字符串hi在字符串abchelloabc中的位置。命令示例如下。

    --返回0。
    select locate('hi', 'abc,hello,ab,c');

  • 示例3：start_pos为NULL。命令示例如下。

    --返回0。
    select locate('ab', 'abhelloabc', null);

LPAD

• 命令格式

  string lpad(string <str1>, int <length>, string <str2>)

• 命令说明

  用字符串str2将字符串str1向左补足到length位。

• 参数说明

  • str1：必填。STRING类型。待向左补位的字符串。

  • length：必填。INT类型。向左补位位数。

  • str2：必填。用于补位的字符串。

• 返回值说明

  返回STRING类型。返回规则如下：

  • 如果length小于str1的位数，则返回str1从左开始截取length位的字符串。

  • 如果length为0，则返回空串。

  • 如果没有输入参数或任一输入参数值为NULL，返回NULL。

• 示例

  • 示例1：用字符串12将字符串abcdefgh向左补足到10位。命令示例如下。

    --返回12abcdefgh。
    select lpad('abcdefgh', 10, '12');

  • 示例2：用字符串12将字符串abcdefgh向左补足到5位。命令示例如下。

    --返回abcde。
    select lpad('abcdefgh', 5, '12');

  • 示例3：length为0。命令示例如下。

    --返回空串。
    select lpad('abcdefgh' ,0, '12'); 

  • 示例4：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select lpad(null ,0, '12');

LTRIM

• 命令格式

  string ltrim(string <str>[, <trimChars>])
  string trim(leading [<trimChars>] from <str>)

• 命令说明

  从str的左端去除字符：

  • 如果未指定trimChars，则默认去除空格字符。

  • 如果指定了trimChars，则以trimChars中包含的字符作为一个集合，从str的左端去除尽可能长的所有字符都在集合trimChars中的子串。

• 参数说明

  • str：必填。STRING类型。待去除左端字符的字符串。如果输入为BIGINT、DECIMAL、DOUBLE或DATETIME类型，则会隐式转换为STRING类型后参与运算。

  • trimChars：可选。String类型。待去除的字符。

• 返回值说明

  返回为STRING类型。返回规则如下：

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • str或trimChars值为NULL时，返回NULL。

• 示例

  • 示例1：去除字符串 yxTxyomxx 的左边空格。命令示例如下。

    --返回字符串yxTxyomxx 。
    select ltrim(' yxTxyomxx ');
    --等效于如下语句。
    select trim(leading from ' yxTxyomxx ');

  • 示例2：去除字符串yxTxyomxx左端所有字符都在集合xy中的子串。

    --返回Txyomxx，只要左端遇到x或者y就会被去掉。
    select ltrim('yxTxyomxx', 'xy');
    --等效于如下语句。
    select trim(leading 'xy' from 'yxTxyomxx');

  • 示例3：输入参数为NULL。命令示例如下。

    --返回NULL。
    select ltrim(null);
    select ltrim('yxTxyomxx', null);

REGEXP_COUNT

• 命令格式

  bigint regexp_count(string <source>, string <pattern>[, bigint <start_position>])

• 命令说明

  计算source中从start_position位置开始，匹配指定pattern的子串数。

• 参数说明

  • source：必填。STRING类型。待搜索的字符串，其他类型会返回报错。

  • pattern：必填。STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。pattern为空串或其他类型时返回报错。

  • start_position：可选。BIGINT类型常量，必须大于0。其他类型或值小于等于0时返回报错。不指定时默认为1，表示从source的第一个字符开始匹配。

• 返回值说明

  返回BIGINT类型。返回规则如下：

  • 如果没有匹配成功，返回0。

  • source、pattern或start_position值为NULL时，返回NULL。

• 示例

  • 示例1：计算abababc中从指定位置开始，匹配指定规则的子串数。命令示例如下。

    --返回1。
    select regexp_count('abababc', 'a.c');
    --返回2。
    select regexp_count('abababc', '[[:alpha:]]{2}', 3);

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select regexp_count('abababc', null);

  • 示例3：计算:出现在JSON字符串{"account_id":123456789,"account_name":"allen","location":"hangzhou","bill":100}中的次数。命令示例如下。

    --返回4。
    select regexp_count('{"account_id":123456789,"account_name":"allen","location":"hangzhou","bill":100}',':');

REGEXP_EXTRACT

• 命令格式

  string regexp_extract(string <source>, string <pattern>[, bigint <groupid>])

• 命令说明

  将字符串source按照pattern的分组规则进行字符串匹配，返回第groupid个组匹配到的字符串内容。

• 参数说明

  • source：必填。STRING类型，待拆分的字符串。

  • pattern：必填。STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。

  • groupid：可选。BIGINT类型常量，必须大于等于0。

  说明

  数据以UTF-8格式保存。对于中文字符可以用对应的16进制编码表示，中文字符编码的范围是[\\x{4e00},\\x{9fa5}]。

• 返回值说明

  返回STRING类型。返回规则如下：

  • 如果pattern为空串或pattern中没有分组，返回报错。

  • groupid非BIGINT类型或小于0时，返回报错。不指定时默认为1，表示返回第一个组。如果groupid等于0，则返回满足整个pattern的子串。

  • source、pattern或groupid值为NULL时，返回NULL。

• 示例

  • 示例1：将foothebar按照foo(.*?)(bar)拆分。命令示例如下。

    --返回the。
    select regexp_extract('foothebar', 'foo(.*?)(bar)');
    --返回foothebar。
    select regexp_extract('foothebar', 'foo(.*?)(bar)', 0);

  • 示例2：将8d99d8按照8d(\\d+)d8拆分。命令示例如下。

    --返回99。提交正则计算的SQL，需要使用两个"\"作为转义字符。
    select regexp_extract('8d99d8', '8d(\\d+)d8');

  • 示例3：提取【阿里云】aliyun中的中文字符及标点。命令示例如下。

    --返回【阿里云】。
    select regexp_extract('【阿里云】aliyun', '([^\\x{00}-\\x{ff}]+)');

  • 示例4：提取【阿里云】aliyun中的中文字符。命令示例如下。

    --返回阿里云。
    select regexp_extract('【阿里云】aliyun', '([\\x{4e00}-\\x{9fa5}]+)');

  • 示例5：提取【阿里云】aliyun阿里云中的中文字符，该场景较为复杂，无法通过REGEXP_EXTRACT函数实现，需要通过REGEXP_REPLACE函数实现。命令示例如下。

    --返回【阿里云】阿里云。
    select regexp_replace('【阿里云】aliyun阿里云','([\\x{00}-\\x{ff}])', '');

  • 示例6：没有分组。错误命令示例如下：

    select regexp_extract('foothebar', 'foothebar');

REGEXP_INSTR

• 命令格式

  bigint regexp_instr(string <source>, string <pattern>[,bigint <start_position>[, bigint <occurrence>[, bigint <return_option>]]])

• 命令说明

  计算字符串source从start_position开始，与pattern第occurrence次匹配的子串的起始或结束位置。

• 参数说明

  • source：必填。STRING类型。源字符串。

  • pattern：必填。STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。pattern为空串时返回报错。

  • start_position：可选。BIGINT类型常量。搜索的开始位置。不指定时默认值为1。

  • occurrence：可选。BIGINT类型常量。指定匹配次数，不指定时默认值为1，表示搜索第一次出现的位置。

  • return_option：可选。BIGINT类型常量。指定返回的位置。值为0或1，不指定时默认值为0，其他类型或不允许的值会返回报错。0表示返回匹配的开始位置，1表示返回匹配的结束位置。

• 返回值说明

  返回BIGINT类型。return_option指定匹配的子串在source中的开始或结束位置。返回规则如下：

  • 如果pattern为空串，返回报错。

  • start_position或occurrence非BIGINT类型或小于等于0时，返回报错。

  • source、pattern、start_position、occurrence或return_option值为NULL时，返回NULL。

• 示例

  • 示例1：计算字符串i love www.taobao.com从3开始，与o[[:alpha:]]{1}第2次匹配的子串开始位置。命令示例如下。

    --返回14。
    select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2);

  • 示例2：计算字符串i love www.taobao.com从3开始，与o[[:alpha:]]{1}第2次匹配的子串结束位置。命令示例如下。

    --返回16。
    select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2, 1);

  • 示例3：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select regexp_instr('i love www.taobao.com', null, 3, 2);

REGEXP_REPLACE

• 命令格式

  string regexp_replace(string <source>, string <pattern>, string <replace_string>[, bigint <occurrence>])

• 命令说明

  将source字符串中第occurrence次匹配pattern的子串替换成指定字符串replace_string后返回结果字符串。

• 参数说明

  • source：必填。STRING类型，待替换的字符串。

  • pattern：必填。STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。pattern为空串时返回报错。

  • replace_string：必填。STRING类型，将匹配pattern的字符串替换后的字符串。

  • occurrence：可选。BIGINT类型常量，必须大于等于0，表示将第occurrence次匹配的字符串替换为replace_string，为0时表示替换所有匹配的子串。为其他类型或小于0时，返回报错。默认值为0。

• 返回值说明

  返回STRING类型。返回规则如下：

  • 当引用不存在的组时，不进行替换。

  • 如果replace_string值为NULL且pattern有匹配，返回NULL。

  • 如果replace_string值为NULL但pattern不匹配，返回原字符串。

  • source、pattern或occurrence值为NULL时，返回NULL。

• 示例

  • 示例1：将123.456.7890字符串中与([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})匹配的所有字符串替换为(\\1)\\2-\\3。命令示例如下。

    --返回(123)456-7890。
    select regexp_replace('123.456.7890', '([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})',
    '(\\1)\\2-\\3', 0);

  • 示例2：将abcd字符串中与指定规则匹配的字符串进行替换。命令示例如下。

    --返回a b c d。
    select regexp_replace('abcd', '(.)', '\\1 ', 0);
    --返回a bcd。
    select regexp_replace('abcd', '(.)', '\\1 ', 1);
    --返回abcd。
    select regexp_replace('abcd', '(.)', '\\2', 1);

  • 示例3：假设表url_set中列名为url的数据格式为www.simple@xxx.com，且每行的xxx完全不同，现需要将列中www后的所有内容都替换掉。命令示例如下。

    --返回结果为wwwtest。
    select regexp_replace(url,'(www)(.*)','wwwtest',0) from url_set;

  • 示例4：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select regexp_replace('abcd', '(.)', null, 0);

  • 示例5：引用不存在的组。命令示例如下。

    --因为pattern中只定义了一个组，引用的第二个组不存在。
    --请避免这样使用，引用不存在的组的结果未定义。
    regexp_replace("abcd", "(.*)(.)$", "\\2", 0) = "d"
    --因为在pattern中没有组的定义，所以\1引用了不存在的组，
    --请避免这样使用，引用不存在的组的结果未定义。
    regexp_replace("abcd", "a", "\\1", 0) = "bcd"

REGEXP_SUBSTR

• 命令格式

  string regexp_substr(string <source>, string <pattern>[, bigint <start_position>[, bigint <occurrence>]])

• 命令说明

  返回从start_position位置开始，source中第occurrence次匹配指定pattern的子串。

• 参数说明

  • source：必填。STRING类型。待搜索的字符串。

  • pattern：必填。STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范，请参见正则表达式规范。

  • start_position：可选。其他BIGINT常量，必须大于0。不指定时默认为1，表示从source的第一个字符开始匹配。

  • occurrence：可选。BIGINT常量，必须大于0。不指定时默认为1，表示返回第一次匹配的子串。

• 返回值说明

  返回STRING类型。返回规则如下：

  • 如果pattern为空串，返回报错。

  • 没有匹配时，返回NULL。

  • start_position或occurrence非BIGINT类型或小于等于0时，返回报错。

  • source、pattern、start_position、occurrence或return_option值为NULL时，返回NULL。

• 示例

  • 示例1：返回I love aliyun very much字符串中与指定规则匹配的字符串。命令示例如下。

    --返回aliyun。
    select regexp_substr('I love aliyun very much', 'a[[:alpha:]]{5}');
    --返回have。
    select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1);
    --返回2。
    select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 2);

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select regexp_substr('I love aliyun very much', null);

REPEAT

• 命令格式

  string repeat(string <str>, bigint <n>)

• 命令说明

  返回将str重复n次后的字符串。

• 参数说明

  • str：必填。STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算。

  • n：必填。BIGINT类型。长度不超过2 MB。

• 返回值说明

  返回STRING类型。返回规则如下：

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • n为空时，返回报错。

  • str或n值为NULL时，返回NULL。

• 示例

  • 示例1：将字符串abc重复5次。命令示例如下。

    --返回abcabcabcabcabc。
    select repeat('abc', 5); 

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select repeat('abc', null);

REPLACE

• 命令格式

  string replace(string <str>, string <old>, string <new>)

• 命令说明

  用new字符串替换str字符串中与old字符串完全重合的部分并返回替换后的str。如果没有重合的字符串，返回原str。

• 参数说明

  • str：必填。STRING类型。待替换的字符串。

  • old：必填。待比较的字符串。

  • new：必填。替换后的字符串。

• 返回值说明

  返回STRING类型。如果任一输入参数值为NULL，返回NULL。

• 示例

  • 示例1：用字符串12替换字符串ababab中与字符串abab完全重合的部分。命令示例如下。

    --返回12ab。
    select replace('ababab','abab','12');

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select replace('123abab456ab',null,'abab');

REVERSE

• 命令格式

  string reverse(string <str>)

• 命令说明

  返回倒序字符串。

• 参数说明

  str：必填。STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算。

• 返回值说明

  返回STRING类型。返回规则如下：

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • str值为NULL时，返回NULL。

• 示例

  • 示例1：返回字符串I love aliyun very much的倒序。命令示例如下。

    --返回字符串hcum yrev nuyila evol I。
    select reverse('I love aliyun very much');

  • 示例2：输入参数为NULL。命令示例如下。

    --返回NULL。
    select reverse(null);

RPAD

• 命令格式

  string rpad(string <str1>, int <length>, string <str2>)

• 命令说明

  用字符串str2将字符串str1向右补足到length位。

• 参数说明

  • str1：必填。STRING类型。待向右补位的字符串。

  • length：必填。INT类型。向右补位位数。

  • str2：必填。用于补位的字符串。

• 返回值说明

  返回STRING类型。返回规则如下：

  • 如果length小于str1的位数，则返回str1从左开始截取length位的字符串。

  • 如果length为0，则返回空串。

  • 如果没有输入参数或任一输入参数值为NULL，返回NULL。

• 示例

  • 示例1：用字符串12将字符串abcdefgh向右补足到10位。命令示例如下。

    --返回abcdefgh12。
    select rpad('abcdefgh', 10, '12');

  • 示例2：用字符串12将字符串abcdefgh向右补足到5位。命令示例如下。

    --返回abcde。
    select rpad('abcdefgh', 5, '12');

  • 示例3：length为0。命令示例如下。

    --返回空串。
    select rpad('abcdefgh' ,0, '12'); 

  • 示例4：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select rpad(null ,0, '12');

RTRIM

• 命令格式

  string rtrim(string <str>[, <trimChars>])
  string trim(trailing [<trimChars>] from <str>)

• 命令说明

  从str的右端去除字符：

  • 如果未指定trimChars，则默认去除空格字符。

  • 如果指定了trimChars，则以trimChars中包含的字符作为一个集合，从str的右端去除尽可能长的所有字符都在集合trimChars中的子串。

• 参数说明

  • str：必填。STRING类型。待去除右端字符的字符串。如果输入为BIGINT、DECIMAL、DOUBLE或DATETIME类型，则会隐式转换为STRING类型后参与运算。

  • trimChars：可选。String类型。待去除的字符。

• 返回值说明

  返回为STRING类型。返回规则如下：

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • str或trimChars值为NULL时，返回NULL。

• 示例

  • 示例1：去除字符串 yxTxyomxx 的右边空格。命令示例如下。

    --返回字符串 yxTxyomxx。
    select rtrim(' yxTxyomxx ');
    --等效于如下语句。
    select trim(trailing from ' yxTxyomxx ');

  • 示例2：去除字符串yxTxyomxx右端所有字符都在集合xy中的子串。

    --返回yxTxyom，只要右端遇到x或者y就会被去掉。
    select rtrim('yxTxyomxx', 'xy');
    --等效于如下语句。
    select trim(trailing 'xy' from 'yxTxyomxx');

  • 示例3：输入参数为NULL。命令示例如下。

    --返回NULL。
    select rtrim(null);
    select ltrim('yxTxyomxx', 'null');

SPACE

• 命令格式

  string space(bigint <n>)

• 命令说明

  生成空格字符串，长度为n。

• 参数说明

  n：必填。BIGINT类型。长度不超过2 MB。

• 返回值说明

  返回STRING类型。返回规则如下：

  • n为空时，返回报错。

  • n值为NULL时，返回NULL。

• 示例

  --返回10。
  select length(space(10));

SPLIT_PART

• 命令格式

  string split_part(string <str>, string <separator>, bigint <start>[, bigint <end>])

• 命令说明

  依照分隔符separator拆分字符串str，返回从start部分到end部分的子串（闭区间）。

• 参数说明

  • str：必填。STRING类型。待拆分的字符串。如果是BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算。

  • separator：必填。STRING类型常量。拆分用的分隔符，可以是一个字符，也可以是一个字符串。

  • start：必填。BIGINT类型常量，必须大于0。表示返回段的开始编号（从1开始）。

  • end：BIGINT类型常量，大于等于start。表示返回段的截止编号，可省略，缺省时表示和start取值相等，返回start指定的段。

• 返回值说明

  返回STRING类型。返回规则如下：

  • 如果start的值大于切分后实际的分段数，例如字符串拆分完有6个片段，start大于6，返回空串。

  • 如果separator不存在于str中，且start指定为1，返回整个str。如果str为空串，则输出空串。

  • 如果separator为空串，则返回原字符串str。

  • 如果end大于片段个数，返回从start开始的子串。

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • separator非STRING类型常量时，返回报错。

  • start或end非BIGINT类型常量时，返回报错。

  • 除separator外，如果任一参数值为NULL，返回NULL。

• 示例

  • 示例1：依照分隔符,拆分字符串a,b,c,d，返回指定部分的子串。命令示例如下。

    --返回a。
    select split_part('a,b,c,d', ',', 1);
    --返回a,b。
    select split_part('a,b,c,d', ',', 1, 2);

  • 示例2：start的值大于切分后实际的分段数。命令示例如下。

    --返回空串。
    select split_part('a,b,c,d', ',', 10);

  • 示例3：separator不存在于str中。命令示例如下。

    --返回a,b,c,d。
    select split_part('a,b,c,d', ':', 1);
    --返回空串。
    select split_part('a,b,c,d', ':', 2);

  • 示例4：separator为空串。命令示例如下。

    --返回a,b,c,d。
    select split_part('a,b,c,d', '', 1);

  • 示例5：end的值大于切分后实际的分段数。命令示例如下。

    --返回b,c,d。
    select split_part('a,b,c,d', ',', 2, 6);

  • 示例6：除separator外，任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select split_part('a,b,c,d', ',', null);

SUBSTR

• 命令格式

  string substr(string <str>, bigint <start_position>[, bigint <length>])

• 命令说明

  返回字符串str从start_position开始，长度为length的子串。

• 参数说明

  • str：必填。STRING类型。如果输入为BIGINT、DECIMAL、DOUBLE或DATETIME类型，则会隐式转换为STRING类型后参与运算。

  • start_position：必填。BIGINT类型，默认起始位置为1。

    • 当start_position为0时，与起始位置为1时相同。

  • length：可选。BIGINT类型，表示子串的长度值。值必须大于0。

• 返回值说明

  返回STRING类型。返回规则如下：

  • str非STRING、BIGINT、DECIMAL、DOUBLE或DATETIME类型时，返回报错。

  • length非BIGINT类型或值小于等于0时，返回报错。

  • 当length被省略时，返回到str结尾的子串。

  • str、start_position或length值为NULL时，返回NULL。

• 示例

  • 示例1：返回字符串abc从指定位置开始，指定长度的子串。命令示例如下。

    --返回bc。
    select substr('abc', 2);
    --返回b。
    select substr('abc', 2, 1);
    --返回bc。
    select substr('abc',-2 , 2);

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select substr('abc', null);

SUBSTRING

• 命令格式

  string substring(string|binary <str>, int <start_position>[, int <length>])

• 命令说明

  返回字符串str从start_position开始，长度为length的子串。

• 参数说明

  • str：必填。STRING或BINARY类型。

  • start_position：必填。INT类型，起始位置为1。当start_position为0时，返回空串。当start_position为负数时，表示开始位置是从字符串的结尾往前倒数，最后一个字符是-1，依次往前倒数。

  • length：可选。BIGINT类型，表示子串的长度值。值必须大于0。

• 返回值说明

  返回STRING类型。返回规则如下：

  • str非STRING或BINARY类型时，返回报错。

  • length非BIGINT类型或值小于等于0时，返回报错。

  • 当length被省略时，返回到str结尾的子串。

  • str、start_position或length值为NULL时，返回NULL。

• 示例

  • 示例1：返回字符串abc从指定位置开始，指定长度的子串。命令示例如下。

    --返回bc。
    select substring('abc', 2);
    --返回b。
    select substring('abc', 2, 1);
    --返回bc。
    select substring('abc',-2,2);
    --返回ab。
    select substring('abc',-3,2);
    --返回001。
    substring(bin(2345), 2, 3);

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select substring('abc', null, null);

SUBSTRING_INDEX

• 命令格式

  string substring_index(string <str>, string <separator>, int <count>)

• 命令说明

  截取字符串str第count个分隔符之前的字符串。如果count为正，则从左边开始截取。如果count为负，则从右边开始截取。

• 参数说明

  • str：必填。STRING类型。待截取的字符串。

  • separator：必填。STRING类型的分隔符。

  • count：必填。INT类型。指定分隔符位置。

• 返回值说明

  返回STRING类型。如果任一输入参数值为NULL，返回NULL。

• 示例

  • 示例1：截取字符串https://help.aliyun.com。命令示例如下。

    --返回https://help.aliyun。
    select substring_index('https://help.aliyun.com', '.', 2);
    --返回aliyun.com。
    select substring_index('https://help.aliyun.com', '.', -2);

  • 示例2：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select substring_index('https://help.aliyun.com', null, 2);

TO_CHAR

• 命令格式

  string to_char(boolean <value>)
  string to_char(bigint <value>)
  string to_char(double <value>)
  string to_char(decimal <value>)

• 命令说明

  将BOOLEAN、BIGINT、DECIMAL或DOUBLE类型值转换为对应的STRING类型表示。

• 参数说明

  value：必填。BOOLEAN、BIGINT、DECIMAL或DOUBLE类型。

• 返回值说明

  返回STRING类型。返回规则如下：

  • value非BOOLEAN、BIGINT、DECIMAL或DOUBLE类型时，返回报错。

  • value值为NULL时，返回NULL。

• 示例

  • 示例1：将如下值转换为STRING类型表示。命令示例如下。

    --返回字符串123。
    select to_char(123);
    --返回字符串TRUE。
    select to_char(true);
    --返回字符串1.23。
    select to_char(1.23);

  • 示例2：输入参数为NULL。命令示例如下。

    --返回NULL。
    select to_char(null);

TOLOWER

• 命令格式

  string tolower(string <source>)

• 命令说明

  将字符串source中的大写字符转换为对应的小写字符。

• 参数说明

  source：必填。STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算。目前只支持英文字符。

• 返回值说明

  返回STRING类型。返回规则如下：

  • source非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • source值为NULL时，返回NULL。

• 示例

  • 示例1：将字符串中的大写字符转换为小写字符。命令示例如下。

    --返回abcd。
    select tolower('aBcd');
    --返回中国fighting。
    select tolower('中国Fighting');

  • 示例2：输入参数为NULL。命令示例如下。

    --返回NULL。
    select tolower(null);

TOUPPER

• 命令格式

  string toupper(string <source>)

• 命令说明

  将字符串source中的小写字符转换为对应的大写字符。

• 参数说明

  source：必填。STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型，则会隐式转换为STRING类型后参与运算。目前只支持英文字符。

• 返回值说明

  返回STRING类型。返回规则如下：

  • source非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • source值为NULL时，返回NULL。

• 示例

  • 示例1：将字符串中的小写字符转换为大写字符。命令示例如下。

    --返回ABCD。
    select toupper('aBcd');
    --返回中国FIGHTING。
    select toupper('中国Fighting');

  • 示例2：输入参数为NULL。命令示例如下。

    --返回NULL。
    select toupper(null);

TRANSLATE

• 命令格式

  string translate(string|varchar <str1>, string|varchar <str2>, string|varchar <str3>)

• 命令说明

  将str1出现在str2中的每个字符替换成str3中相对应的字符。无匹配则不替换。

• 返回值说明

  返回STRING类型。如果任一输入参数值为NULL，返回NULL。

• 示例

  • 示例1：将字符串ababab出现在abab中的每个字符替换成cd中的相应字符。命令示例如下。

    --返回cdcdcd。
    select translate('ababab','abab','cd');

  • 示例2：将字符串ababab出现在abab中的每个字符替换成cdefg中的相应字符。命令示例如下。

    --返回cdcdcd。
    select translate('ababab','abab','cdefg');

  • 示例3：任一输入参数为NULL。命令示例如下。

    --返回NULL。
    select translate('ababab','cd',null);

TRIM

• 命令格式

  string trim(string <str>[,<trimChars>])
  string trim([BOTH] [<trimChars>] from <str>)

• 命令说明

  从str的左右两端去除字符：

  • 如果未指定trimChars，则默认去除空格字符。

  • 如果指定了trimChars，则以trimChars中包含的字符作为一个集合，从str的左右两端去除尽可能长的所有字符都在集合trimChars中的子串。

• 参数说明

  • str：必填。STRING类型。待去除左右两端字符的字符串。如果输入为BIGINT、DECIMAL、DOUBLE或DATETIME类型，则会隐式转换为STRING类型后参与运算。

  • trimChars：可选。String类型。待去除的字符。

• 返回值说明

  返回为STRING类型。返回规则如下：

  • str非STRING、BIGINT、DOUBLE、DECIMAL或DATETIME类型时，返回报错。

  • str或trimChars值为NULL时，返回NULL。

• 示例

  • 示例1：去除字符串 yxTxyomxx 的左右空格。命令示例如下。

    --返回字符串yxTxyomxx。
    select trim(' yxTxyomxx ');
    --等效于如下语句。
    select trim(both from ' yxTxyomxx ');
    select trim(from ' yxTxyomxx ');

  • 示例2：去除字符串yxTxyomxx左右两端所有字符都在集合xy中的子串。

    --返回Txyom，只要左右两端遇到x或者y就会被去掉。
    select trim('yxTxyomxx', 'xy');
    --等效于如下语句。
    select trim(both 'xy' from 'yxTxyomxx');
    select trim('xy' from 'yxTxyomxx');

  • 示例3：输入参数为NULL。命令示例如下。

    --返回NULL。
    select trim(null);
    select trim('yxTxyomxx', null);