文档

字符串函数

更新时间:

当您需要对存储在表中的字符串数据进行截取、拼接、转化、比较、搜索等操作,您可以使用MaxCompute支持的字符串函数对指定字符串进行灵活处理。本文为您介绍MaxCompute支持的字符串函数的命令格式、参数说明及示例。

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

函数

功能

ASCII

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

CHAR_MATCHCOUNT

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

CHR

将指定ASCII码转换成字符。

CONCAT

将字符串连接在一起。

CONCAT_WS

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

DECODE

将字符串按照指定编码格式解码。

ENCODE

将字符串按照指定编码格式编码。

FIND_IN_SET

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

FORMAT_NUMBER

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

FROM_CHARSET

将指定编码格式的二进制数据转换为UTF-8格式的字符串。

FROM_JSON

根据给定的JSON字符串和输出格式信息,返回ARRAY、MAP或STRUCT类型。

GET_JSON_OBJECT

在一个标准JSON字符串中,按照指定方式抽取指定的字符串。

INITCAP

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

INSTR

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

IS_ENCODING

判断字符串是否可以从指定的A字符集转换为B字符集。

JSON_TUPLE

在一个标准的JSON字符串中,按照输入的一组键抽取各个键指定的字符串。

KEYVALUE

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

KEYVALUE_TUPLE

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

LENGTH

计算字符串的长度。

LENGTHB

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

LOCATE

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

LPAD

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

LTRIM

去除字符串的左端字符。

MASK_HASH

返回基于字符串表达式计算的Hash值。

MD5

计算字符串的MD5值。

PARSE_URL

对URL进行解析返回指定部分的信息。

PARSE_URL_TUPLE

对URL进行解析返回多个部分的信息。

REGEXP_COUNT

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

REGEXP_EXTRACT

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

REGEXP_EXTRACT_ALL

在字符串中查找所有出现的正则表达式匹配的子字符串,并把找到的字符串以数组形式返回。

REGEXP_INSTR

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

REGEXP_REPLACE

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

REGEXP_SUBSTR

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

REPEAT

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

REPLACE

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

REVERSE

返回倒序字符串。

RPAD

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

RTRIM

去除字符串的右端字符。

SOUNDEX

将普通字符串替换为SOUNDEX字符串。

SPACE

生成空格字符串。

SPLIT

按照分隔符分割字符串后返回数组。

SPLIT_PART

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

SUBSTR

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

SUBSTRING

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

SUBSTRING_INDEX

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

TO_CHAR

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

TO_JSON

将指定的复杂类型输出为JSON字符串。

TOLOWER

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

TOUPPER

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

TRANSLATE

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

TRIM

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

URL_DECODE

将字符串从application/x-www-form-urlencoded MIME格式转换为常规字符。

URL_ENCODE

将字符串编码为application/x-www-form-urlencoded MIME格式。

注意事项

升级到MaxCompute 2.0后,产品扩展了部分函数。如果您用到的函数涉及新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),在使用扩展函数时,需要执行如下语句开启新数据类型开关:

  • Session级别:如果使用新数据类型,您需要在SQL语句前加上语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。

  • Project级别:Project Owner可根据需要对Project进行设置,等待10~15分钟后才会生效。命令如下。

    setproject odps.sql.type.system.odps2=true;

    setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项,请参见数据类型版本说明

使用限制

以下函数只支持英文字符的转换:

  • TRIM/RTRIM/LTRIM:trimChars只支持英文字符。

  • REVERSE:在Hive模式下只支持英文字符。

  • SOUNDEX:仅转换英文字符。

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

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

  • INITCAP:将字符串中每个单词首字母(英文字符)转化为大写形式,其余为小写。

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

  • 命令格式

    array<T> concat(array<T> <a>, array<T> <b>[,...])
    string concat(string <str1>, string <str2>[,...])
  • 命令说明

    • 输入为ARRAY数组:将多个ARRAY数组中的所有元素连接在一起,生成一个新的ARRAY数组。

    • 输入为字符串:将多个字符串连接在一起,生成一个新的字符串。

  • 参数说明

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

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

  • 返回值说明

    • 返回ARRAY类型。如果任一输入ARRAY数组为NULL,返回结果为NULL。

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

  • 示例

    • 示例1:连接ARRAY数组array(10, 20)array(20, -20)。命令示例如下。

      --返回[10, 20, 20, -20]。
      select concat(array(10, 20), array(20, -20));
    • 示例2:ARRAY数组元素包含NULL。命令示例如下。

      --返回[10, NULL, 20, -20]。
      select concat(array(10, null), array(20, -20));
    • 示例3:任一ARRAY数组为NULL。命令示例如下。

      --返回NULL。
      select concat(array(10, 20), null);
    • 示例4:连接字符串aabcabcde。命令示例如下。

      --返回aabcabcde。
      select concat('aabc','abcde');
    • 示例5:输入为空。命令示例如下。

      --返回NULL。
      select concat();
    • 示例6:任一字符串输入为NULL。命令示例如下。

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

CONCAT_WS

  • 命令格式

    string concat_ws(string <separator>, string <str1>, string <str2>[,...])
    string concat_ws(string <separator>, array<string> <a>)
  • 命令说明

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

  • 参数说明

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

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

    • a:必填。ARRAY数组。数组中的元素为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');
    • 示例3:将ARRAY数组array('name', 'hanmeimei')中的元素通过:连接。命令示例如下。

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

DECODE

  • 命令格式

    string decode(binary <str>, string <charset>)
  • 命令说明

    str按照charset格式进行解码。

  • 参数说明

    • str:必填。BINARY类型,待解码的字符串。

    • charset:必填。STRING类型,编码格式。取值范围为:UTF-8、UTF-16、UTF-16LE、UTF-16BE、ISO-8859-1、US-ASCII。

      说明

      目前ISO-8859-1和US-ASCII编码格式只支持对英文进行编码或解码,暂不支持中文。

  • 返回值说明

    返回STRING类型。strcharset值为NULL时,返回NULL。

  • 示例

    • 示例1:将字符串“中文样例”按照UTF-8格式编码。命令示例如下。

      --编解码
      select decode(encode("中文样例","UTF-8"), "UTF-8");
      --返回
      +-----+
      | _c0 |
      +-----+
      | 中文样例 |
      +-----+
    • 示例2:任一输入参数为NULL。命令示例如下。

      --返回结果为NULL。
      select decode(encode("中文样例","UTF-8"), null);
      | _c0 |
      +-----+
      | NULL |
      +-----+

ENCODE

  • 命令格式

    binary encode(string <str>, string <charset>)
  • 命令说明

    str按照charset格式进行编码。

  • 参数说明

    • str:必填。STRING类型。待重新编码的字符串。

    • charset:必填。STRING类型。编码格式。取值范围为:UTF-8、UTF-16、UTF-16LE、UTF-16BE、ISO-8859-1、US-ASCII。

  • 返回值说明

    返回BINARY类型。strcharset值为NULL时,返回NULL。

  • 示例

    • 示例1:将字符串abc按照UTF-8格式编码。命令示例如下。

      --返回abc。
      select encode("abc", "UTF-8");
    • 示例2:将字符串abc按照UTF-16BE格式编码。命令示例如下。

      --返回=00a=00b=00c。
      select encode("abc", "UTF-16BE");
    • 示例3:任一输入参数为NULL。命令示例如下。

      --返回结果为NULL。
      select encode("abc", null);

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

FROM_CHARSET

  • 命令格式

    STRING FROM_CHARSET(binary <source>, string <source_charset>, [string <mode>])
  • 命令说明

    FROM_CHARSET支持将某些非UTF-8编码的二进制数据转化为UTF-8字符串,方便后续计算的使用。

  • 参数说明

    • source:必填,BINARY类型,待转换的二进制(Bytes)数据。

    • source_charset:必填,STRING类型,source参数原本的二进制数据编码格式。取值范围为:UTF-8UTF-16UTF-16LEUTF-16BEISO-8859-1US-ASCIIGB2312GBKGB18030

    • mode:可选,STRING类型,将source的二进制(Bytes)数据转换成指定编码格式字符串时,若遇到无法对应的字符时,采取的处理模式。取值说明如下:

      • NONE(默认值):不做任何处理,提示报错。

      • TRANSLIT:使用指定编码格式中类似的字符替换。

      • IGNORE:忽略报错的内容,然后继续完成命令。

  • 返回值说明

    返回UTF-8编码格式的STRING类型数据。当参数为NULL或空字符串时,返回规则如下:

    • 任意一个输入参数为NULL时,返回结果为NULL。

    • 任意一个输入参数为空字符串时,返回报错。

  • 示例

    • 示例1:将UTF-8编码的二进制数据转换为UTF-8字符串。

      SELECT FROM_CHARSET(unhex('e58aa0e6b2b9e9949fe696a4e68bb70a'),'UTF-8', 'TRANSLIT');

      返回结果如下

      +------------+
      | _c0        |
      +------------+
      | 加油锟斤拷
           |
      +------------+
    • 示例2:将GBK编码的二进制数据转换为UTF-8字符串。

      SELECT FROM_CHARSET(unhex('b9feb9febac3a4ce'), 'GBK');

      返回结果如下。

      +------------+
      | _c0        |
      +------------+
      | 哈哈好の       |
      +------------+
    • 示例3:当输入参数为NULL时,返回结果为NULL。

      SELECT FROM_CHARSET(unhex('b9feb9febac3a4ce'), null);

      返回结果如下。

      +------------+
      | _c0        |
      +------------+
      | NULL       |
      +------------+

FROM_JSON

  • 命令格式

    from_json(<jsonStr>, <schema>)
  • 命令说明

    根据JSON字符串jsonStrschema信息,返回ARRAY、MAP或STRUCT类型。

  • 参数说明

    • jsonStr:必填。输入的JSON字符串。

    • schema:必填。写法与建表语句的类型一致。例如array<bigint>map<string, array<string>>struct<a:int, b:double, `C`:map<string,string>>

      说明

      STRUCT的Key区分大小写。此外,STRUCT类型还有一种写法a bigint, b double,等同于struct<a:bigint, b:double>

      JSON数据类型与MaxCompute数据类型的对应关系如下。

      JSON数据类型

      MaxCompute数据类型

      OBJECT

      STRUCT、MAP、STRING

      ARRAY

      ARRAY、STRING

      NUMBER

      TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、STRING

      BOOLEAN

      BOOLEAN、STRING

      STRING

      STRING、CHAR、VARCHAR、BINARY、DATE、DATETIME

      NULL

      所有类型

      说明

      对于OBJECT和ARRAY类型,会采用尽可能解析的方式,不匹配的类型会忽略。为了便于使用,所有的JSON类型都可以转换为MaxCompute的STRING类型。同时您需要注意,对应FLOAT、DOUBLE、DECIMAL三种数据类型无法保证小数的精度,如果需要确保精度可以先用STRING类型取出数据,再转换为对应数值类型。

  • 返回值说明

    返回ARRAY、MAP或STRUCT类型。

  • 示例

    • 示例1:将指定JSON字符串以指定格式输出。命令示例如下。

      --返回{"a":1,"b":0.8}。
      select from_json('{"a":1, "b":0.8}', 'a int, b double');
      --返回{"time":"26/08/2015"}。
      select from_json('{"time":"26/08/2015"}', 'time string');
      --返回{"a":1,"b":0.8}。
      select from_json('{"a":1, "b":0.8}', 'a int, b double, c string');
      --返回[1,2,3]。
      select from_json('[1, 2, 3, "a"]', 'array<bigint>');
      --返回{"d":"v","a":"1","b":"[1,2,3]","c":"{}"}。
      select from_json('{"a":1,"b":[1,2,3],"c":{},"d":"v"}', 'map<string, string>');
    • 示例2:使用map_keys函数和from_json函数实现类似JSON_KEYS的作用,获取JSON字符串中所有key的集合。命令示例如下。

      --返回["a","b"]。
      select map_keys(from_json('{"a":1,"b":2}','map<string,string>'));

GET_JSON_OBJECT

使用说明

GET_JSON_OBJECT函数的作用是在一个标准JSON字符串中,按照JSON PATH抽取指定的字符串。当前函数的入参支持两种类型:

  • 入参为JSON类型:基于最新支持的JSON数据类型,采用更为规范的JSON PATH。

  • 入参为STRING类型:原有的JSON PATH解析方式。

入参类型不同时函数的使用方式和注意事项不同,本文为您展示入参分别为JSON和STRING类型时,GET_JSON_OBJECT函数的使用方法。

说明
  • 新JSON类型所使用的JSON PATH与原有的JSON PATH规范不同,可能存在兼容性问题。

  • GET_JSON_OBJECT不支持JSON PATH的正则语法。

入参为JSON类型

  • 命令格式

    string get_json_object(json <json>, string <json_path>)
  • 命令说明

    在一个标准JSON字符串中,按照JSON PATH抽取指定的字符串。

  • 参数说明

    • json:必填,待处理的JSON数据。

    • json_path:必填,需要返回的值的JSON路径。

  • 返回值说明

    返回STRING类型。

  • 示例

    • 示例1:从JSON中获取key为a的value值。

      select get_json_object(json '{"a":1, "b":2}', '$.a');

      返回结果:

      +-----+
      | _c0 |
      +-----+
      | 1   |
      +-----+
    • 示例2:从JSON中获取key为c的value值。

      select get_json_object(json '{"a":1, "b":2}', '$.c');

      返回结果:

      +-----+
      | _c0 |
      +-----+
      | NULL |
      +-----+
    • 示例3:JSON Path非法时,返回NULL。

      select get_json_object(json '{"a":1, "b":2}', '$invalid_json_path');

      返回结果:

      +-----+
      | _c0 |
      +-----+
      | NULL |
      +-----+

入参为STRING类型

  • 命令格式

    string get_json_object(string <json>, string <path>)
  • 命令说明

    在一个标准JSON字符串中,按照path抽取指定的字符串。每次调用该函数时,都会读一次原始数据,因此反复调用可能影响性能和产生费用。您可以通过get_json_object,结合UDTF,轻松转换JSON格式日志数据,避免多次调用函数,详情请参见利用MaxCompute内建函数及UDTF转换JSON格式日志数据

  • 参数说明

    • json:必填。STRING类型。标准的JSON格式对象,格式为{Key:Value, Key:Value,...}。如果遇到英文双引号("),需要用两个反斜杠(\\)进行转义。如果遇到英文单引号('),需要用一个反斜杠(\)进行转义。

    • path:必填。STRING类型。表示在json中的path,以$开头。更多path信息,请参见LanguageManual UDF。相关最佳实践案例,请参见JSON数据从OSS迁移至MaxCompute。不同字符的含义如下:

      • $:表示根节点。

      • .['']:表示子节点。MaxCompute支持用这两种字符解析JSON对象,当JSON的Key本身包含.时,可以用['']来替代。

      • [][number]表示数组下标,从0开始。

      • *Wildcard for [],返回整个数组。*不支持转义。

  • 限制条件

    ['']取数只在新版本支持,您需要添加设置Flag的语句set odps.sql.udf.getjsonobj.new=true;

  • 返回值说明

    • 如果json为空或非法的json格式,返回NULL。

    • 如果json合法,path也存在,则返回对应字符串。

    • 您可以通过在Session级别设置odps.sql.udf.getjsonobj.new属性来控制函数的返回方式:

      • 当设置set odps.sql.udf.getjsonobj.new=true;时,函数返回行为采用了保留原始字符串的方式进行输出。

        推荐您使用此配置,函数返回行为更标准,处理数据更方便,性能更好。如果MaxCompute项目有使用JSON保留字符转义行为的存量作业,建议保留原有行为方式,避免因未验证而直接使用该行为产生错误或正确性问题。函数返回行为规则如下:

        • 返回值仍是一个JSON字符串,可以继续当做JSON来解析,而不再需要额外使用replaceregexp_replace等函数替换反斜线。

        • 一个JSON对象中可以出现相同的Key,可以成功解析。

          --返回1。
          select get_json_object('{"a":"1","a":"2"}', '$.a');
        • 支持Emoji表情符号对应的编码字符串。但DataWorks暂不支持输入Emoji表情符号,仅支持通过数据集成等工具直接将Emoji表情符号对应的编码字符串写入MaxCompute,再用get_json_object函数处理。

          --返回Emoji符号。
          select get_json_object('{"a":"<Emoji符号>"}', '$.a');
        • 输出结果按照JSON字符串的原始排序方式输出。

          --返回{"b":"1","a":"2"}。
          select get_json_object('{"b":"1","a":"2"}', '$');
      • 当设置set odps.sql.udf.getjsonobj.new=false;时,函数返回行为采用了JSON保留字符转义的方式进行输出。函数返回行为规则如下:

        • 换行符(\n)、引号(")等JSON保留字符使用字符串'\n''\"'显示。

        • 一个JSON对象中不可以出现相同的Key,可能导致无法解析。

          --返回NULL。
          select get_json_object('{"a":"1","a":"2"}', '$.a');
        • 不支持解析Emoji表情符号编码的字符串。

          --返回NULL。
          select get_json_object('{"a":"<Emoji符号>"}', '$.a');
        • 输出结果按照字典排序方式输出。

          --返回{"a":"2","b":"1"}。
          select get_json_object('{"b":"1","a":"2"}', '$');
      说明

      自2021年1月21日及之后新创建的MaxCompute项目中,get_json_object函数的返回行为默认为保留原始字符串。2021年1月21日之前创建的MaxCompute项目中,get_json_object函数的返回行为默认为JSON保留字符转义。您可以通过以下示例判断MaxCompute项目中get_json_object函数采用了哪种行为,执行命令如下:

      select get_json_object('{"a":"[\\"1\\"]"}', '$.a');
      --JSON保留字符转义的行为返回:
      [\"1\"]
      
      --保留原始字符串的行为返回:
      ["1"]

      您可以通过申请链接或搜索(钉钉群号:11782920)加入MaxCompute开发者社区钉群联系MaxCompute技术支持团队,将您的项目中的get_json_object函数返回行为切换为保留原始字符串的行为,避免在Session级别频繁设置属性。

  • 示例

    • 示例1:提取JSON对象src_json.json中的信息。命令示例如下。

      --JSON对象src_json.json的内容。
      +----+
      json
      +----+
      {"store":
      {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
      "bicycle":{"price":19.95,"color":"red"}
      },
      "email":"amy@only_for_json_udf_test.net",
      "owner":"amy"
      }
      --提取owner字段信息,返回amy。
      select get_json_object(src_json.json, '$.owner') from src_json;
      --提取store.fruit字段第一个数组信息,返回{"weight":8,"type":"apple"}。
      select get_json_object(src_json.json, '$.store.fruit[0]') from src_json;
      --提取不存在的字段信息,返回NULL。
      select get_json_object(src_json.json, '$.non_exist_key') from src_json;
    • 示例2:提取数组型JSON对象的信息。命令示例如下。

      --返回2222。
      select get_json_object('{"array":[["aaaa",1111],["bbbb",2222],["cccc",3333]]}','$.array[1][1]');
      --返回["h0","h1","h2"]。
      set odps.sql.udf.getjsonobj.new=true;
      select get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');
      --返回["h0","h1","h2"]。
      set odps.sql.udf.getjsonobj.new=false;
      select get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh');
      --返回h1。
      select get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]');
    • 示例3:提取带有.的JSON对象中的信息。命令示例如下。

      --创建一张表。
      create table mf_json (id string, json string);
      --向表中插入数据,Key带.。
      insert into table mf_json (id, json) values ("1", "{
      \"China.beijing\":{\"school\":{\"id\":0,\"book\":[{\"title\": \"A\",
      \"price\": 8.95},{\"title\": \"B\",\"price\": 10.2}]}}}");
      --向表中插入数据,Key不带.。
      insert into table mf_json (id, json) values ("2", "{
      \"China_beijing\":{\"school\":{\"id\":0,\"book\":[{\"title\": \"A\",
      \"price\": 8.95},{\"title\": \"B\",\"price\": 10.2}]}}}");
      --取id的值,查询key为China.beijing,返回0。由于包含.,只能用['']来解析。
      select get_json_object(json, "$['China.beijing'].school['id']") from mf_json where id =1;
      --取id的值,查询key为China_beijing,返回0。查询方法有如下两种。
      select get_json_object(json, "$['China_beijing'].school['id']") from mf_json where id =2;
      select get_json_object(json, "$.China_beijing.school['id']") from mf_json where id =2;
    • 示例4:JSON输入为空或非法格式。命令示例如下。

      --返回NULL。
      select get_json_object('','$.array[1][1]');
      --返回NULL。
      select get_json_object('"array":["aaaa",1111],"bbbb":["cccc",3333]','$.array[1][1]');
    • 示例5:JSON字符串涉及转义。命令示例如下。

      set odps.sql.udf.getjsonobj.new=true;
      --返回"1"。
      select get_json_object('{"a":"\\"1\\"","b":"2"}', '$.a'); 
      --返回'1'。
      select get_json_object('{"a":"\'1\'","b":"2"}', '$.a'); 

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

IS_ENCODING

  • 命令格式

    boolean is_encoding(string <str>, string <from_encoding>, string <to_encoding>)
  • 命令说明

    判断输入的字符串str是否可以从指定的一个字符集from_encoding转为另一个字符集to_encoding。也可以用于判断输入是否为乱码,通常您可以将from_encoding设为UTF-8,to_encoding设为GBK。

  • 参数说明

    • str:必填。STRING类型。空字符串可以被认为属于任何字符集。

    • from_encodingto_encoding:必填。STRING类型,源及目标字符集。

  • 返回值说明

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

    • 如果str能够成功转换,则返回True,否则返回False。

    • strfrom_encodingto_encoding值为NULL时,返回NULL。

  • 示例

    • 示例1:判断字符测试測試是否可以从utf-8字符集转为gbk字符集。命令示例如下。

      --返回true。
      select is_encoding('测试', 'utf-8', 'gbk');
      --返回true。
      select is_encoding('測試', 'utf-8', 'gbk');
    • 示例2:判断字符測試是否可以从utf-8字符集转为gb2312字符集。命令示例如下。

      --GB2312字库中不包括这两个字,返回false。
      select is_encoding('測試', 'utf-8', 'gb2312');
    • 示例3:任一输入参数为NULL。命令示例如下。

      --返回NULL。
      select is_encoding('测试', null, 'gbk');

JSON_TUPLE

  • 命令格式

    string json_tuple(string <json>, string <key1>, string <key2>,...)
  • 命令说明

    用于一个标准的JSON字符串中,按照输入的一组键(key1,key2,...)抽取各个键指定的字符串。

  • 参数说明

    • json:必填。STRING类型,标准的JSON格式字符串。

    • key:必填。STRING类型,用于描述在JSON中的path,一次可输入多个,不能以美元符号($)开头。MaxCompute支持用.['']这两种字符解析JSON,当JSON的Key本身包含.时,可以用['']来替代。

  • 返回值

    返回STRING类型。

    说明
    • 如果JSON为空或者为非法的JSON格式,返回NULL。

    • 如果键Key为空或者不合法(JSON中不存在)返回NULL。

    • 如果JSON合法,键Key也存在,则返回对应字符串。

    • 支持包含中文的JSON数据解析。

    • 支持多层嵌套的JSON数据解析。

    • 支持包含多重嵌套的数组的JSON数据解析。

    • 解析行为和设置了set odps.sql.udf.getjsonobj.new=true;后的GET_JSON_OBJECT的行为保持一致。在需要对同一个JSON字符串多次解析的情况下,相比于多次调用GET_JSON_OBJECT,JSON_TUPLE可以一次输入多个Key,且JSON字符串只被解析一次,效率更高。

    • JSON_TUPLE是UDTF,在需要选取其他列时应配合Lateral View使用。

  • 示例

    --创建一张表school。
    create table school (id string, json string);
    --向表中插入数据。
    insert into school (id, json) values ("1", "{
    \"school\": \"湖畔大学\", \"地址\":\"杭州\", \"SchoolRank\": \"00\", 
    \"Class1\":{\"Student\":[{\"studentId\":1,\"scoreRankIn3Year\":[1,2,[3,2,6]]}, 
    {\"studentId\":2,\"scoreRankIn3Year\":[2,3,[4,3,1]]}]}}");
    • 示例1:提取JSON对象信息。命令示例如下。

      select json_tuple(school.json,"SchoolRank","Class1") as (item0, item1) from school;
      --等效于如下语句。
      select get_json_object(school.json,"$.SchoolRank") item0,get_json_object(school.json,"$.Class1") item1 from school;
      
      --返回结果如下。
      +-------+-------+
      | item0 | item1 |
      +-------+-------+
      | 00    | {"Student":[{"studentId":1,"scoreRankIn3Year":[1,2,[3,2,6]]},{"studentId":2,"scoreRankIn3Year":[2,3,[4,3,1]]}]} |
      +-------+-------+
    • 示例2:使用['']提取JSON对象信息。命令示例如下。

      select json_tuple(school.json,"school","['Class1'].Student") as (item0, item2) from school where id=1;
      --返回结果如下。
      +-------+-------+
      | item0 | item2 |
      +-------+-------+
      | 湖畔大学 | [{"studentId":1,"scoreRankIn3Year":[1,2,[3,2,6]]},{"studentId":2,"scoreRankIn3Year":[2,3,[4,3,1]]}] |
    • 示例3:支持解析包含中文的JSON数据。命令示例如下。

      select json_tuple(school.json,"校名","地址") as (item0,item1) from school;
      --返回结果如下。
      +-------+-------+
      | item0 | item1 |
      +-------+-------+
      | 湖畔大学 | 杭州 |
      +-------+-------+
    • 示例4:支持解析多层嵌套的JSON数据。命令示例如下。

      select sc.Id, q.item0, q.item1 
      from school sc lateral view json_tuple(sc.json,"Class1.Student[*].studentId","Class1.Student[0].scoreRankIn3Year") q as item0,item1;
      
      --返回结果如下。
      +------------+-------+-------+
      | id         | item0 | item1 |
      +------------+-------+-------+
      | 1          | [1,2] | [1,2,[3,2,6]] |
      +------------+-------+-------+
    • 示例5:支持解析包含多重嵌套数组的JSON数据。命令示例如下。

      select sc.Id, q.item0, q.item1
      from school sc lateral view json_tuple(sc.json,"Class1.Student[0].scoreRankIn3Year[2]","Class1.Student[0].scoreRankIn3Year[2][1]") q as item0,item1;
      --返回结果如下。
      +------------+-------+-------+
      | id         | item0 | item1 |
      +------------+-------+-------+
      | 1          | [3,2,6] | 2     |
      +------------+-------+-------+

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。

KEYVALUE_TUPLE

  • 命令格式

    KEYVALUE_TUPLE(str, split1, split2, key1, key2, ..., keyN)
  • 命令说明

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

  • 参数说明

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

    • split1split2:必填。STRING类型。用于作为分隔符的字符串,按照指定的两个分隔符拆分源字符串。当某个被split1拆分后的字符串中有多个split2时,返回结果未定义。

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

  • 返回值说明

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

    • split1split2值为NULL时,返回NULL。

    • strkey值为NULL或没有匹配的key时,返回NULL。

  • 示例

    --创建表
    create table mf_user (
    user_id string,
    user_info string
    );
    --插入数据
    insert into mf_user values('1','age:18;genda:f;address:abc'),('2','age:20;genda:m;address:bcd');
    --查询
    SELECT user_id,
    KEYVALUE(user_info,';',':','age') as age,
    KEYVALUE(user_info,';',':','genda') as genda,
    KEYVALUE(user_info,';',':','address') as address
    FROM mf_user;
    --等同于使用KEYVALUE查询
    SELECT user_id,
    age,
    genda,
    address
    FROM mf_user LATERAL VIEW KEYVALUE_TUPLE(user_info,';', ':','age','genda','address') ui AS age,genda,address;

    结果如下所示。

    +------------+------------+------------+------------+
    | user_id    | age        | genda      | address    |
    +------------+------------+------------+------------+
    | 1          | 18         | f          | abc        |
    | 2          | 20         | m          | bcd        |
    +------------+------------+------------+------------+

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位。此函数为MaxCompute 2.0扩展函数。

  • 参数说明

    • 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);

MASK_HASH

  • 命令格式

    mask_hash(<expr>)
  • 命令说明

    返回基于字符串表达式(expr)计算的Hash值。Hash值相同证明表达式的值相同。

  • 参数说明

    expr:必填。需要计算Hash值的字符串表达式。支持STRING、CHAR、VARCHAR、BINARY类型。

  • 返回值说明

    返回字符串表达式计算出的Hash值, 固定长度64字节; 对所有非字符串类型的表达式返回null, 与Hive兼容。

  • 示例

    --对abc返回hash值
    select mask_hash("abc");
    --返回
    +------------+
    | _c0        |
    +------------+
    | ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad |
    +------------+
    
    --对其他类型输入值,返回NULL
    select mask_hash(100);
    --返回
    +------------+
    | _c0        |
    +------------+
    | NULL       |
    +------------+

MD5

  • 命令格式

    string md5(string <str>)
  • 命令说明

    计算字符串str的MD5值。

  • 参数说明

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

  • 返回值说明

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

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

    • str值为NULL时,返回NULL。

  • 示例

    • 示例1:计算字符串Tech on the net的MD5值。命令示例如下。

      --返回ddc4c4796880633333d77a60fcda9af6。
      select md5('Tech on the net');
    • 示例2:输入参数为NULL。命令示例如下。

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

PARSE_URL

  • 命令格式

    string parse_url(string <url>, string <part>[, string <key>])
  • 命令说明

    url解析后,按照part提取信息。

  • 参数说明

    • url:必填。STRING类型。URL链接。无效URL链接会返回报错。

    • part:必填。STRING类型。取值包含:HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE和USERINFO,不区分大小写。

    • key:可选。当part取值为QUERY时,根据key值取出对应的Value值。

  • 返回说明

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

    • urlpartkey值为NULL时,返回NULL。

    • part取值不符合要求时,返回报错。

  • 示例

    --返回example.com。
    select parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'HOST');
    --返回/over/there/index.dtb。
    select parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'PATH');
    --返回animal。
    select parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'QUERY', 'type');
    --返回nose。
    select parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'REF');
    --返回file。
    select parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'PROTOCOL');
    --返回 username:password@example.com:8042。
    select parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'AUTHORITY');
    --返回username:password。
    select parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'USERINFO');

PARSE_URL_TUPLE

  • 命令格式

    string parse_url_tuple(string <url>, string <key1>, string <key2>,...)
  • 命令说明

    url解析后,按照输入的一组键key1key2等抽取各个键指定的字符串。该功能与PARSE_URL类似,但它可以同时提取多个键对应的字符串,性能更优。

  • 参数说明

    • url:必填。STRING类型。URL链接。无效URL链接会返回报错。

    • key1key2:必填。STRING类型。指定要抽取的键。取值范围如下:

      • HOST:获取主机地址,可以为域名或IP地址。

      • PATH:获取网络资源在服务器中的路径。

      • QUERY:查询字符串,指代待查询的内容。

      • REF:获取URL注释。鼠标悬停至URL链接上时显示的内容。

      • PROTOCOL:获取协议类型。

      • AUTHORITY:获取服务器的域名或IP地址、端口号和用户鉴权信息(例如用户名、密码)。

      • FILE:获取网络资源在服务器中的路径和待查询的内容,即由PATH和QUERY组成。

      • USERINFO:用户鉴权信息。

      • QUERY:<KEY>:获取查询字符串中指定字段(Key)的取值。

      不区分大小写。不在该范围内的取值会返回报错。

  • 返回说明

    返回STRING类型。urlkey值为NULL时,返回报错。

  • 示例

    抽取file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose中各个键对应的字符串。命令示例如下。

    select parse_url_tuple('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'HOST', 'PATH', 'QUERY', 'REF', 'PROTOCOL', 'AUTHORITY', 'FILE', 'USERINFO', 'QUERY:type', 'QUERY:name') as (item0, item1, item2, item3, item4, item5, item6, item7, item8, item9);

    返回结果如下。

    +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
    | item0      | item1      | item2      | item3      | item4      | item5      | item6      | item7      | item8      | item9      |
    +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
    | example.com | /over/there/index.dtb | type=animal&name=narwhal | nose       | file       | username:password@example.com:8042 | /over/there/index.dtb?type=animal&name=narwhal | username:password | animal     | narwhal    |
    +------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+

REGEXP_COUNT

  • 命令格式

    bigint regexp_count(string <source>, string <pattern>[, bigint <start_position>])
  • 命令说明

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

  • 参数说明

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

    • pattern:必填。STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范,请参见RLIKE字符匹配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>])
    说明

    REGEXP_EXTRACT函数在Hive兼容的数据类型版本中遵循Java regex规范。而在1.0和2.0数据类型版本中,则遵循MaxCompute的规范。

  • 命令说明

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

  • 参数说明

    • source:必填。STRING类型,待拆分的字符串。

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

    • 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。在MaxCompute客户端上提交正则计算的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_EXTRACT_ALL

  • 命令格式

    array<T> regexp_extract_all(string <source>, string <pattern>[,bigint <group_id>])
  • 命令说明

    在字符串中查找所有出现的正则表达式匹配模式的子字符串,并把找到的字符串以数组返回。

  • 参数说明

    • source:必填。STRING类型。待分析的字符串。

    • pattern:必填。STRING类型。待匹配的模型,可以是STRING类型常量或正则表达式。更多正则表达式编写规范,请参见RLIKE字符匹配

    • group_id:可选。BIGINT类型。返回指定组的所有匹配结果,必须大于等于0。如果不指定该值, 默认返回group_id1的所有匹配结果;如果等于0pattern将被当作一个整体进行匹配。

  • 返回值说明

    返回ARRAY类型。如果指定group_id,返回该group_id匹配的所有结果组成的数组;如果不指定group_id, 则返回group_id1的所有匹配结果组成的数组。

  • 示例

    • 不指定group_id默认返回第一个group_id匹配到的结果。

      SELECT regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)');

      结果如下。

      +------------+
      | _c0        |
      +------------+
      | [100,300] |
      +------------+
    • 指定group_id值为2,返回第二个group_id匹配到的结果。

      SELECT regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)',2);

      结果如下。

      +------------+
      | _c0        |
      +------------+
      | [200,400] |
      +------------+

REGEXP_INSTR

  • 命令格式

    bigint regexp_instr(string <source>, string <pattern>[,bigint <start_position>[, bigint <occurrence>[, bigint <return_option>]]])
  • 命令说明

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

  • 参数说明

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

    • pattern:必填。STRING类型常量或正则表达式。待匹配的模型。更多正则表达式编写规范,请参见RLIKE字符匹配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>])
    说明

    REGEXP_REPLACE函数在Hive兼容的数据类型版本中遵循Java regex规范。而在1.0和2.0数据类型版本中,则遵循MaxCompute的规范。

  • 命令说明

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

  • 参数说明

    • source:必填。STRING类型,待替换的字符串。

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

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

      说明
      • 如果replace_string为空字符串函数将删除匹配pattern的字符串后返回。

      • replace_string中可以包含后向引用\n,表示插入与pattern中第n个捕获组匹配的子字符串,其中n的取值范围为1~9,\0表示整个pattern。在使用时需要对反斜线进行转义:\\1,或者使用原始字符串(Raw String):R'(\1)'。

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

  • 返回值说明

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

    • 当引用不存在的组时,其结果未定义。

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

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

    • sourcepatternoccurrence值为NULL时,返回NULL。

  • 示例

    • 示例1:将字符串按照指定规则进行替换。命令示例如下:

      --返回Abcd。
      select regexp_replace("abcd", "a", "A", 0);
      --返回bcd。
      select regexp_replace("abcd", "a", "", 0);
      --返回19700101。
      select regexp_replace("1970-01-01", "-", "", 0);
      --返回abc。
      select regexp_replace("a1b2c3", "[0-9]", "", 0);
      --返回a1b2c。
      select regexp_replace("a1b2c3", "[0-9]", "", 3);
    • 示例2:将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);
    • 示例3:将abcd字符串中与指定规则匹配的字符串进行替换。命令示例如下。

      --返回a b c d 。
      select regexp_replace('abcd', '(.)', '\\1 ', 0);
      --返回a bcd。
      select regexp_replace('abcd', '(.)', '\\1 ', 1);
      --返回d。
      select regexp_replace("abcd", "(.*)(.)$", "\\2", 0);
    • 示例4:假设表url_set中列名为URL的数据格式为www.simple@xxx.com,且每行的xxx完全不同,现需要将列中www后的所有内容都替换掉。命令示例如下。

      --返回结果为wwwtest。
      select regexp_replace(url,'(www)(.*)','wwwtest',0) from url_set;
    • 示例5:任一输入参数为NULL。命令示例如下。

      --返回NULL。
      select regexp_replace('abcd', '(.)', null, 0);
    • 示例6:引用不存在的组。命令示例如下。

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

REGEXP_SUBSTR

  • 命令格式

    string regexp_substr(string <source>, string <pattern>[, bigint <start_position>[, bigint <occurrence>]])
  • 命令说明

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

  • 参数说明

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

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

    • 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。此函数为MaxCompute 2.0扩展函数。

  • 参数说明

    • 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位。此函数为MaxCompute 2.0扩展函数。

  • 参数说明

    • 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');

SOUNDEX

  • 命令格式

    string soundex(string <str>)
  • 命令说明

    将普通字符串转换为SOUNDEX字符串。

  • 参数说明

    str:必填。STRING类型。待转换的字符串。此函数为MaxCompute 2.0扩展函数。

  • 返回值说明

    返回STRING类型。str值为NULL时,返回NULL。

  • 示例

    • 示例1:将字符串hello转换为SOUNDEX字符串。命令示例如下。

      --返回H400。
      select soundex('hello');
    • 示例2:输入参数为NULL。命令示例如下。

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

SPACE

  • 命令格式

    string space(bigint <n>)
  • 命令说明

    生成空格字符串,长度为n

  • 参数说明

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

  • 返回值说明

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

    • n为空时,返回报错。

    • n值为NULL时,返回NULL。

  • 示例

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

SPLIT

  • 命令格式

    split(<str>, <pat>, [<trimTailEmpty>])
  • 参数说明

      • str:必填。STRING类型。指被分割的字符串。

      • pat:必填。STRING类型的分隔符。支持正则表达式。更多正则表达式信息,请参见RLIKE字符匹配

      • trimTailEmpty: 可选参数,默认值为true,设置为false保留末尾空字符串 (Hive兼容)。

  • 返回值说明

    返回ARRAY数组。数组中的元素为STRING类型。

  • 示例

    --返回["a"," b"," c"]
    select split("a, b, c", ",");
    
    --默认不返回空字符串
    select split("a, b, c,,", ",");
    --返回结果
    +------------+
    | _c0        |
    +------------+
    | ["a"," b"," c"] |
    +------------+
    
    --如果需要返回空字符串
    select split("a, b, c,,", ",", false);
    --返回结果
    +------------+
    | _c0        |
    +------------+
    | ["a"," b"," c","",""] |
    +------------+

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。

      • Hive兼容数据类型版本:当start_position为0时,与起始位置为1时相同。

      • 1.0和2.0数据类型版本:当start_position为0时,返回NULL。

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

      重要
      • 如果setproject odps.function.strictmode=false时:length值小于0时,会返回空白。

      • 如果setproject odps.function.strictmode=true时:length值小于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为负,则从右边开始截取。此函数为MaxCompute 2.0扩展函数。

  • 参数说明

    • 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);

TO_JSON

  • 命令格式

    string to_json(<expr>)
  • 命令说明

    将给定的复杂类型expr,以JSON字符串格式输出。

  • 参数说明

    expr:必填。ARRAY、MAP、STRUCT复杂类型。

    说明

    如果输入为STRUCT类型(struct<key1:value1, key2:value2):

    • 转换为JSON字符串时,Key会全部转为小写。

    • value如果为NULL,则不输出value本组的数据。例如value2为NULL,则key2:value2不会输出到JSON字符串。

  • 返回值说明

    返回JSON格式的字符串。

  • 示例

    • 示例1:将指定复杂类型以指定格式输出。命令示例如下。

      --返回{"a":1,"b":2}。
      select to_json(named_struct('a', 1, 'b', 2));
      --返回{"time":"26/08/2015"}。
      select to_json(named_struct('time', "26/08/2015"));
      --返回[{"a":1,"b":2}]。
      select to_json(array(named_struct('a', 1, 'b', 2)));
      --返回{"a":{"b":1}}。
      select to_json(map('a', named_struct('b', 1)));
      --返回{"a":1}。
      select to_json(map('a', 1));
      --返回[{"a":1}]。
      select to_json(array((map('a', 1))));
    • 示例2:输入为STRUCT类型的特殊情况。命令示例如下。

      --返回{"a":"B"}。STRUCT类型转换为JSON字符串时,key会全部转为小写。
      select to_json(named_struct("A", "B"));
      --返回{"k2":"v2"}。NULL值所在组的数据,不会输出到JSON字符串。
      select to_json(named_struct("k1", cast(null as string), "k2", "v2"));

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中相对应的字符。无匹配则不替换。此函数为MaxCompute 2.0扩展函数。

  • 返回值说明

    返回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);

URL_DECODE

  • 命令格式

    string url_decode(string <input>[, string <encoding>])
  • 命令说明

    将输入字符串从application/x-www-form-urlencoded MIME格式转为常规字符串,是url_encode的逆过程。编码规则如下:

    • a~z、A~Z保持不变。

    • 英文句点(.)、短划线(-)、星号(*)和下划线(_)保持不变。

    • 加号(+)转为空格。

    • %xy格式的序列转换为对应的字节值,连续的字节值根据输入的encoding名称解码为对应的字符串。

    • 其余的字符保持不变。

  • 参数说明

    • input:必填。STRING类型。要输入的字符串。

    • encoding:可选。指定编码格式,支持GBK或UTF-8等标准编码格式,不输入默认为UTF-8。

  • 返回值说明

    返回STRING类型UTF-8编码的字符串。inputencoding值为NULL时,返回NULL。

  • 示例

    --返回示例for url_decode:// (fdsf)。
    select url_decode('%E7%A4%BA%E4%BE%8Bfor+url_decode%3A%2F%2F+%28fdsf%29');
    --返回Example for URL_DECODE:// dsf(fasfs)。
    select url_decode('Example+for+url_decode+%3A%2F%2F+dsf%28fasfs%29', 'GBK');

URL_ENCODE

  • 命令格式

    string url_encode(string <input>[, string <encoding>])
  • 命令说明

    将输入字符串编码为application/x-www-form-urlencoded MIME格式。编码格式如下:

    • a~z、A~Z保持不变。

    • 英文句点(.)、短划线(-)、星号(*)和下划线(_)保持不变。

    • 空格转为加号(+)。

    • 其余字符根据指定的encoding转为字节值,然后将每个字节值表示为%xy的格式,xy是该字符的十六进制表示方式。

  • 参数说明

    • input:必填。STRING类型。要输入的字符串。

    • encoding:可选。指定编码格式,支持GBK或UTF-8等标准编码格式,不输入默认为UTF-8。

  • 返回值说明

    返回STRING类型。inputencoding值为NULL时,返回NULL。

  • 示例

    --返回%E7%A4%BA%E4%BE%8Bfor+url_encode%3A%2F%2F+%28fdsf%29。
    select url_encode('示例for url_encode:// (fdsf)');
    --返回Example+for+url_encode+%3A%2F%2F+dsf%28fasfs%29。
    select url_encode('Example for url_encode:// dsf(fasfs)', 'GBK');

相关文档

当以上函数不能满足您的业务需求,您可以自行编写代码逻辑创建自定义函数(UDF),以满足多样化业务需求,请参见MaxCompute UDF概述