字符串函数

更新时间: 2023-09-15 17:46:08

您可以在SQL中使用字符串函数对指定字符串进行灵活处理。本文为您提供SQL支持的字符串函数的命令格式、参数说明及示例,指导您使用字符串函数完成开发。

SQL支持的字符串函数如下。

函数

功能

ASCII

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

CHAR_MATCHCOUNT

计算A字符串出现在B字符串中的字符个数。

CHR

将指定ASCII码转换成字符。

CONCAT

将字符串连接在一起。

CONCAT_WS

将参数中的所有字符串按照指定的分隔符连接在一起。

FIND_IN_SET

在以逗号分隔的字符串中查找指定字符串的位置。

FORMAT_NUMBER

将数字转化为指定格式的字符串。

INITCAP

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

INSTR

计算A字符串在B字符串中的位置。

KEYVALUE

将字符串拆分为Key-Value对,并将Key-Value对分开,返回Key对应的Value。

LENGTH

计算字符串的长度。

LENGTHB

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

LOCATE

在字符串中查找另一指定字符串的位置。

LPAD

将字符串向左补足到指定位数。

LTRIM

去除字符串的左端字符。

REGEXP_COUNT

计算字符串从指定位置开始,匹配指定规则的子串数。

REGEXP_EXTRACT

将字符串按照指定规则拆分为组后,返回指定组的字符串。

REGEXP_INSTR

返回字符串从指定位置开始,与指定规则匹配指定次数的子串的起始或结束位置。

REGEXP_REPLACE

将字符串中,与指定规则在指定次数匹配的子串替换为另一字符串。

REGEXP_SUBSTR

返回字符串中,从指定位置开始,与指定规则匹配指定次数的子串。

REPEAT

返回将字符串重复指定次数后的结果。

REPLACE

将字符串中与指定字符串匹配的子串替换为另一字符串。

REVERSE

返回倒序字符串。

RPAD

将字符串向右补足到指定位数。

RTRIM

去除字符串的右端字符。

SPACE

生成空格字符串。

SPLIT_PART

按照分隔符拆分字符串,返回指定部分的子串。

SUBSTR

返回STRING类型字符串从指定位置开始,指定长度的子串。

SUBSTRING

返回STRING或BINARY类型字符串从指定位置开始,指定长度的子串。

SUBSTRING_INDEX

截取字符串指定分隔符前的字符串。

TO_CHAR

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

TOLOWER

将字符串中的英文字符转换为小写形式。

TOUPPER

将字符串中的英文字符转换为大写形式。

TRANSLATE

将A出现在B中的字符串替换为C字符串。

TRIM

去除字符串的左右两端字符。

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中。

  • 参数说明

    str1str2:必填。STRING类型,必须为有效的UTF-8字符串。如果对比过程中发现有无效字符(非UNICODE编码),则返回负值。

  • 返回值说明

    返回BIGINT类型。str1str2值为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>[,...])
  • 命令说明

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

  • 参数说明

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

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

  • 返回值说明

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

  • 示例

    • 示例1:连接字符串aabcabcde。命令示例如下。

      --返回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类型的分隔符。

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

  • 返回值说明

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

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

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

  • 示例

    • 示例1:将字符串namehanmeimei通过:连接。命令示例如下。

      --返回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中无法匹配到str1str1中包含逗号(,)时,返回0。

    • str1str2值为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<0expr2>340时,会返回报错。

    • expr1expr2值为空或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。表示str2str1中第nth_appearance次匹配的位置。如果nth_appearance为其他类型或小于等于0,则返回报错。

  • 返回值说明

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

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

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

    • str1str2start_positionnth_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类型。待拆分的字符串。

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

    • key:必填。STRING类型。将字符串按照split1split2拆分后,返回key值对应的Value。

  • 返回值说明

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

    • split1split2值为NULL时,返回NULL。

    • strkey值为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);

      没有指定split1split2,默认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。

    • strsubstr值为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类型时,返回报错。

    • strtrimChars值为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。

    • sourcepatternstart_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的子串。

    • sourcepatterngroupid值为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>]]])
  • 命令说明

    计算字符串sourcestart_position开始,与patternoccurrence次匹配的子串的起始或结束位置。

  • 参数说明

    • 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_positionoccurrence非BIGINT类型或小于等于0时,返回报错。

    • sourcepatternstart_positionoccurrencereturn_option值为NULL时,返回NULL。

  • 示例

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

      --返回14。
      select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2);
    • 示例2:计算字符串i love www.taobao.com3开始,与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不匹配,返回原字符串。

    • sourcepatternoccurrence值为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_positionoccurrence非BIGINT类型或小于等于0时,返回报错。

    • sourcepatternstart_positionoccurrencereturn_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为空时,返回报错。

    • strn值为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类型时,返回报错。

    • strtrimChars值为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类型常量时,返回报错。

    • startend非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>])
  • 命令说明

    返回字符串strstart_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结尾的子串。

    • strstart_positionlength值为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>])
  • 命令说明

    返回字符串strstart_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结尾的子串。

    • strstart_positionlength值为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>)
  • 命令说明

    截取字符串strcount个分隔符之前的字符串。如果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类型时,返回报错。

    • strtrimChars值为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);

阿里云首页 物联网平台 相关技术圈