本文为您介绍MaxCompute SQL支持的字符串函数。

CHAR_MATCHCOUNT

  • 命令格式
    BIGINT CHAR_MATCHCOUNT(STRING str1, STRING str2)
  • 命令说明

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

  • 参数说明

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

  • 返回值说明

    返回BIGINT类型。如果任一输入参数为NULL,则返回NULL。

  • 示例
    CHAR_MATCHCOUNT('abd','aabc') = 2
    -- str1中的两个字符串'a'和'b'出现在str2中。

CHR

  • 命令格式
    STRING CHR(BIGINT ascii)
  • 命令说明

    将指定ASCII码ascii转换成字符。

  • 参数说明

    ascii:BIGINT类型的ASCII值。参数范围是0~255,超过此范围会引发异常。如果输入为STRING、DOUBLE或DECIMAL类型,则会隐式转换为BIGINT类型后参与运算,其它类型会返回异常。

  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

CONCAT

  • 命令格式
    STRING CONCAT(STRING a, STRING b...)
  • 命令说明

    将参数中的所有字符串连接在一起。

  • 参数说明

    输入参数为STRING类型。如果输入参数为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。

  • 返回值类型

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

  • 示例
    CONCAT('ab','c') = 'abc'
    CONCAT() = NULL
    CONCAT('a', null, 'b') = NULL

GET_JSON_OBJECT

  • 命令格式
    STRING GET_JSON_OBJECT(STRING json,STRING path)
  • 命令说明

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

  • 参数说明
    • JSON:STRING类型,标准的JSON格式字符串。
    • path:STRING类型,表示在JSON中的path,以$开头。path详情请参见LanguageManual UDF
      • $:表示根节点。
      • .:表示子节点。
      • [][number]表示数组下标,数组格式为key[sub1][sub2][sub3]……
      • *Wildcard for [],返回整个数组。*不支持转义。
  • 返回值说明
    • 如果JSON为空或非法的JSON格式,则返回NULL。
    • 如果JSON合法,path也存在,则返回对应字符串。
    • 不支持一个Object中出现相同的Key,例如{a:1, a:0},可能导致无法解析。
    • 不支持Emoji表情字符串。
  • 示例一
    +----+
    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"
    }
    执行如下命令提取JSON对象中的信息。
    odps> SELECT GET_JSON_OBJECT(src_json.json, '$.owner') FROM src_json;
    amy
    odps> SELECT GET_JSON_OBJECT(src_json.json, '$.store.fruit\[0]') FROM src_json;
    {"weight":8,"type":"apple"}
    odps> SELECT GET_JSON_OBJECT(src_json.json, '$.non_exist_key') FROM src_json;
    NULL
    示例二
    GET_JSON_OBJECT('{"array":[["aaaa",1111],["bbbb",2222],["cccc",3333]]}','$.array[1][1]')= "2222"
    GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]') = "["h0","h1","h2"]"
    GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]') = "h1"

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。
    • nth_appearance:BIGINT类型,大于0。表示子串在字符串中的第nth_appearance次匹配的位置。如果nth_appearance为其它类型或小于等于0,则返回异常。
  • 返回值说明
    • 返回BIGINT类型。
    • 如果在str1中未找到str2,则返回0。
    • 如果任一输入参数为NULL,则返回NULL。
    • 如果str2为空串,则总能匹配成功,例如instr(‘abc’, ‘’)会返回1。
  • 示例
    INSTR('Tech on the net', 'e') = 2
    INSTR('Tech on the net', 'e', 1, 1) = 2
    INSTR('Tech on the net', 'e', 1, 2) = 11
    INSTR('Tech on the net', 'e', 1, 3) = 14

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类型,源及目标字符集。
    • 如果任一输入参数为NULL,则返回NULL。
  • 返回值说明

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

  • 示例
    IS_ENCODING('测试', 'utf-8', 'gbk') = true
    IS_ENCODING('測試', 'utf-8', 'gbk') = true
    -- GBK字库中有这两个繁体字。
    IS_ENCODING('測試', 'utf-8', 'gb2312') = false
    -- GB2312字库中不包括这两个字。

KEYVALUE

  • 命令格式
    KEYVALUE(STRING srcStr,STRING split1,STRING split2, STRING key)
    KEYVALUE(STRING srcStr,STRING key) //split1 = ";",split2 = ":"
  • 命令说明

    srcStr(源字符串)按split1分成Key-Value对,并按split2将Key-Value对分开,返回key所对应的Value。

  • 参数说明
    • srcStr:待拆分的字符串。
    • key:STRING类型。源字符串按照split1split2拆分后,返回key值对应的Value。
    • split1split2:用来作为分隔符的字符串,按照指定的两个分隔符拆分源字符串。如果表达式中没有指定这两项,默认split1";"split2":"。当某个被split1拆分后的字符串中有多个split2时,返回结果未定义。
  • 返回值说明
    • 返回STRING类型。
    • split1split2为NULL时,返回NULL。
    • srcStrkey为NULL或者没有匹配的key时,返回NULL。
    • 如果有多个Key-Value匹配,返回第一个匹配上的key对应的Value。
  • 示例一
    KEYVALUE('0:1\;1:2', 1) = '2'
    源字符串为“0:1\;1:2”,因为没有指定split1split2,默认split1";"split2":"
    经过split1拆分后,Key-Value对为0:1\,1:2。经过split2拆分后变为如下。
    0 1/  
    1 2
    返回key为1所对应的Value值2。
    示例二
    KEYVALUE("\;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\;","\;",":","tf") = "21910" value:21910。
    “\;decreaseStore:1\;xcard:1\;isB2C:1\;tf:21910\;cart:1\;shipping:2\;pf:0\;market:shoes\;instPayAmount:0\;”按照split1“\;”拆分后,得出的Key-Value对如下所示。
    decreaseStore:1,xcard:1,isB2C:1,tf:21910,cart:1,shipping:2,pf:0,market:shoes,instPayAmount:0 
    按照split2":"拆分后,结果如下所示。
    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类型。如果输入参数为NULL,则返回NULL;如果输入参数为非UTF-8编码格式,则返回-1。
  • 示例
    LENGTH('hi! 中国') = 6

LENGTHB

  • 命令格式
    BIGINT LENGTHB(STRING str)
  • 命令说明

    返回字符串str的以字节为单位的长度。

  • 参数说明
    • str:STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。
    • 返回BIGINT类型。如果输入参数为NULL,则返回NULL。
  • 示例
    LENGTHB('hi! 中国') = 10

MD5

  • 命令格式
    STRING MD5(STRING value)
  • 命令说明

    计算输入字符串value的MD5值。

  • 参数说明

    value:STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型参与运算,其它类型会返回异常。

  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

REGEXP_EXTRACT

  • 命令格式
    STRING REGEXP_EXTRACT(STRING source, STRING pattern[, BIGINT occurrence])
  • 命令说明

    将字符串source按照pattern正则表达式的规则拆分,返回第occurrencegroup的字符。

  • 参数说明
    • source:STRING类型,待搜索的字符串。
    • pattern:STRING类型常量,如果pattern为空串或者pattern中没有指定group,则返回异常。
    • occurrence:BIGINT类型常量,必须大于等于0,为其它类型或小于0时返回异常。不指定时默认为1,表示返回第一个group。如果occurrence等于0,则返回满足整个pattern的子串。
    说明 数据以UTF-8格式保存。对于中文字符可以用对应的16进制编码表示,中文字符编码的范围是[\x{4e00},\x{9fa5}]
  • 返回值说明

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

  • 示例
    regexp_extract('foothebar', 'foo(.*?)(bar)', 1) = the
    regexp_extract('foothebar', 'foo(.*?)(bar)', 2) = bar
    regexp_extract('foothebar', 'foo(.*?)(bar)', 0) = foothebar
    regexp_extract("阿里巴巴", "([\\x{4e00}-\\x{9fa5}]+)", 1) = 阿里巴巴
    regexp_extract('8d99d8', '8d(\\d+)d8') = 99
    -- 如果是在MaxCompute客户端上提交正则计算的SQL,需要使用两个"\"作为转义字符。
    regexp_extract('foothebar', 'foothebar')
    -- 异常返回,pattern中没有指定group。

REGEXP_INSTR

  • 命令格式
    BIGINT REGEXP_INSTR(STRING source, STRING pattern[,
    BIGINT start_position[, BIGINT nth_occurrence[, BIGINT return_option]]])
  • 命令说明

    返回字符串sourcestart_position开始,与pattern第n次(nth_occurrence)匹配的子串的起始、结束位置。

  • 参数说明
    • source:STRING类型,待搜索的字符串。
    • pattern:STRING类型常量,pattern为空串时返回异常。
    • start_position:BIGINT类型常量,搜索的开始位置。不指定时默认值为1,其它类型或值小于等于0时返回异常。
    • nth_occurrence:BIGINT类型常量,不指定时默认值为1,表示搜索第一次出现的位置。其它类型或值小于等于0时返回异常。
    • return_option:BIGINT类型常量,值为0或1,其它类型或不允许的值会返回异常。0表示返回匹配的开始位置,1表示返回匹配的结束位置。
  • 返回值说明

    返回BIGINT类型。return_option指定匹配的子串在source中的开始或结束位置。如果任一输入参数为NULL,则返回NULL。

  • 示例
    REGEXP_INSTR("i love www.taobao.com", "o[[:alpha:]]{1}", 3, 2) = 14

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,表示将第几次匹配替换成replace_string,为0时表示替换掉所有的匹配子串。为其它类型或小于0时,返回异常。可缺省,默认值为0。
  • 返回值说明
    • 返回STRING类型。当引用不存在的组时,不进行替换。
    • 如果任一输入参数为NULL,则返回NULL。
    • 如果replace_string为NULL且pattern有匹配,则返回NULL。
    • 如果replace_string为NULL但pattern不匹配,则返回原字符串。
  • 示例
    REGEXP_REPLACE("123.456.7890", "([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})",
    "(\\1)\\2-\\3", 0) = "(123)456-7890"
    REGEXP_REPLACE("abcd", "(.)", "\\1 ", 0) = "a b c d "
    REGEXP_REPLACE("abcd", "(.)", "\\1 ", 1) = "a bcd"
    REGEXP_REPLACE("abcd", "(.)", "\\2", 1) = "abcd"
    -- 因为pattern中只定义了一个组,引用的第二个组不存在。
    -- 请避免这样使用,引用不存在的组的结果未定义。
    REGEXP_REPLACE("abcd", "(.*)(.)$", "\\2", 0) = "d"
    REGEXP_REPLACE("abcd", "a", "\\1", 0) = "bcd"
    -- 因为在pattern中没有组的定义,所以\1引用了不存在的组,
    -- 请避免这样使用,引用不存在的组的结果未定义。

REGEXP_SUBSTR

  • 命令格式
    STRING REGEXP_SUBSTR(STRING source, STRING pattern[, BIGINT start_position[, BIGINT nth_occurrence]])
  • 命令说明

    start_position位置开始,source中第nth_occurrence次匹配指定模式pattern的子串。

  • 参数说明
    • source:STRING类型,搜索的字符串。
    • pattern:STRING类型常量,要匹配的模型,pattern为空串时返回异常。
    • start_position:BIGINT常量,必须大于0。为其它类型或小于等于0时抛返回异常。不指定时默认为1,表示从source的第一个字符开始匹配。
    • nth_occurrence:BIGINT常量,必须大于0。为其它类型或小于等于0时返回异常。不指定时默认为1,表示返回第一次匹配的子串。
  • 返回值说明

    返回STRING类型。如果任一输入参数为NULL,则返回NULL。没有匹配时也返回NULL。

  • 示例
    REGEXP_SUBSTR("I love aliyun very much", "a[[:alpha:]]{5}") = "aliyun"
    REGEXP_SUBSTR('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) = " have"
    REGEXP_SUBSTR('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 2) = " 2"

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。如果任一输入参数为NULL,则返回NULL。

  • 示例
    REGEXP_COUNT('abababc', 'a.c') = 1
    REGEXP_COUNT('abcde', '[[:alpha:]]{2}', 3) = 1

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,则返回start指定的段。
    • end:BIGINT类型常量,大于等于start,否则返回异常。返回段的截止编号,非常量或其他类型会返回异常。此参数可省略,缺省时表示最后一部分。
  • 返回值说明
    • 返回STRING类型。
    • 如果start的值大于切分后实际的分段数,例如字符串拆分完有6个片段,start大于6,返回空串。
    • 如果separator不存在于str中,且start指定为1,返回整个str。如果str为空串,则输出空串。
    • 如果separator为空串,则返回原字符串str
    • 如果end大于片段个数,按片段个数处理。
    • 如果任一输入参数为NULL,则返回NULL。
  • 示例
    SPLIT_PART('a,b,c,d', ',', 1) = 'a'
    SPLIT_PART('a,b,c,d', ',', 1, 2) = 'a,b'
    SPLIT_PART('a,b,c,d', ',', 10) = ''

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时,返回空串。当start_position为负数时,表示开始位置是从字符串的结尾往前倒数,最后一个字符是-1,往前数依次为-2、-3…,其它类型会返回异常。
    • length:BIGINT类型,表示子串的长度值。值大于0,其它类型或值小于等于0返回异常。
  • 返回值说明

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

    说明length被省略时,返回到str结尾的子串。
  • 示例
    SUBSTR("abc", 2) = "bc"
    SUBSTR("abc", 2, 1) = "b"
    SUBSTR("abc",-2,2) = "bc"
    SUBSTR("abc",-3) = "abc"

SUBSTRING

  • 命令格式
    STRING SUBSTRING(STRING|BINARY str, INT start_position[, INT length])
  • 命令说明

    返回字符串strstart_position开始,长度为length的子串。

  • 参数说明
    • str:STRING或BINARY类型。如果输入为其它类型,则返回NULL或报错。
    • start_position:INT类型,起始位置为1。当start_position为0时,返回空串。当start_position为负数时,表示开始位置是从字符串的结尾往前倒数,最后一个字符为-1,往前数依次为-2,-3…,其它类型返回异常。
    • length:BIGINT类型,表示子串的长度。值大于0,其它类型或值小于等于0返回异常。
  • 返回值说明

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

    说明length被省略时,返回到str结尾的子串。
  • 示例
    SUBSTRING('abc', 2) = 'bc'
    SUBSTRING('abc', 2, 1) ='"b'
    SUBSTRING('abc',-2,2) = 'bc'
    SUBSTRING('abc',-3,2) = 'ab'
    SUBSTRING(BIN(2345),2,3) = '001'

TOLOWER

  • 命令格式
    STRING TOLOWER(STRING source)
  • 命令说明

    输出英文字符串source对应的小写字符串。

  • 参数说明

    source:STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。

  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

  • 示例
    tolower("aBcd") = "abcd"
    tolower("哈哈Cd") = "哈哈cd"

TOUPPER

  • 命令格式
    STRING TOUPPER(STRING source)
  • 命令说明

    输出英文字符source串对应的大写字符串。

  • 参数说明

    source:STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。

  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

  • 示例
    toupper("aBcd") = "ABCD"
    toupper("哈哈Cd") = "哈哈CD"

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类型。如果输入参数为NULL,则返回NULL。

  • 示例
    TO_CHAR(123) = '123'
    TO_CHAR(true) = 'TRUE'
    TO_CHAR(1.23) = '1.23'
    TO_CHAR(null) = NULL

TRIM

  • 命令格式
    STRING TRIM(STRING str)
  • 命令说明

    去除字符串str的左右空格。

  • 参数说明

    str:STRING类型。如果输入为BIGINT、DECIMAL、DOUBLE或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型返回异常。

  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

LTRIM

  • 命令格式
    STRING LTRIM(STRING str)
  • 命令说明

    去除字符串str的左边空格。

  • 参数说明

    str:STRING类型。如果输入为BIGINT、DECIMAL、DOUBLE或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。

  • 返回值说明

    返回为STRING类型。如果输入参数为NULL,则返回NULL。

  • 示例
    DELECT LTRIM(' abc ') FROM dual; 
    --执行结果如下。
    +-----+
    | _c0 |
    +-----+
    | abc |
    +-----+

RTRIM

  • 命令格式
    STRING RTRIM(STRING str)
  • 命令说明

    去除字符串str的右边空格。

  • 参数说明

    str:STRING类型。如果输入为BIGINT、DECIMAL、DOUBLE或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。

  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

  • 示例
    SELECT RTRIM('a abc ') FROM dual; 
    --执行结果如下。 
    +-----+
    | _c0 |
    +-----+
    | a abc |
    +-----+

REVERSE

  • 命令格式
    STRING REVERSE(STRING str)
  • 命令说明

    返回倒序字符串。

  • 参数说明

    str:STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。

  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

  • 示例
    SELECT REVERSE('abcedfg') FROM dual;--执行结果如下。
    +-----+
    | _c0 |
    +-----+
    | gfdecba |
    +-----+

REPEAT

  • 命令格式
    STRING REPEAT(STRING str, BIGINT n)
  • 命令说明

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

  • 参数说明
    • str:STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。
    • n:BIGINT类型。长度不超过2 MB。如果为空,则返回异常。
  • 返回值说明

    返回STRING类型。

  • 示例
    SELECT REPEAT('abc',5) FROM lxw_dual; --返回abcabcabcabcabc。

ASCII

  • 命令格式
    BIGINT ASCII(STRING str)
  • 命令说明

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

  • 参数说明

    str:STRING类型。如果输入为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其它类型会返回异常。

  • 返回值说明

    返回BIGINT类型。

  • 示例
    SELECT ASCII('abcde') FROM dual; --返回97。

MaxCompute 2.0扩展函数

升级到MaxCompute 2.0后,产品扩展部分数学函数,新函数如果用到新数据类型时,在使用新函数的SQL前,需加以下set语句。
set odps.sql.type.system.odps2=true;
说明 请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

下文将为您详细介绍新扩展的字符串函数。

CONCAT_WS

  • 命令格式
    STRING CONCAT_WS(STRING SEP, STRING a, STRING b...)
    STRING CONCAT_WS(STRING SEP, ARRAY)
  • 命令说明

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

  • 参数说明
    • SEP:STRING类型的分隔符。如果不指定,则返回异常。
  • 返回值说明

    返回STRING类型。如果没有输入参数或者某个输入参数为NULL,则返回NULL。

  • 示例
    CONCAT_WS(':','name','hanmeimei')='name:hanmeimei'
    CONCAT_WS(':','avg',null,'34')=null

LPAD

  • 命令格式
    STRING LPAD(STRING a, INT len, STRING b)
  • 命令说明

    b字符串将a字符串向左补足到len位。

  • 参数说明
    • len:INT整型。
    • ab:STRING类型。
  • 返回值说明

    返回STRING类型。如果len小于a的位数,则返回a从左开始截取len位字符。如果len为0,则返回空。

  • 示例
    lpad('abcdefgh',10,'12')='12abcdefgh'
    lpad('abcdefgh',5,'12')='abcde'
    lpad('abcdefgh',0,'12') --返回空
升级到MaxCompute2.0后,产品扩展了部分日期函数。如果您用到的函数涉及新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),在使用扩展日期函数时,需要执行如下set语句开启新数据类型开关。
  • Session级别:如果使用新数据类型,您需要在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:Project Owner可根据需要对Project进行设置,等待10~15分钟后才会生效。命令如下。
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项,请参见数据类型版本说明

RPAD

  • 命令格式
    STRING RPAD(STRING a, INT len, STRING b)
  • 命令说明

    b字符串将a字符串向右补足到len位。

  • 参数说明
    • len:INT整型。
    • ab:STRING类型。
  • 返回值说明

    返回STRING类型。如果len小于a的位数,则返回a从左开始截取len位字符;如果len为0,则返回空。

  • 示例
    rpad('abcdefgh',10,'12')='abcdefgh12'
    rpad('abcdefgh',5,'12')='abcde'
    rpad('abcdefgh',0,'12') -- 返回空。
升级到MaxCompute2.0后,产品扩展了部分日期函数。如果您用到的函数涉及新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),在使用扩展日期函数时,需要执行如下set语句开启新数据类型开关。
  • Session级别:如果使用新数据类型,您需要在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:Project Owner可根据需要对Project进行设置,等待10~15分钟后才会生效。命令如下。
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项,请参见数据类型版本说明

REPLACE

  • 命令格式
    STRING REPLACE(STRING a, STRING OLD, STRING NEW)
  • 命令说明

    NEW字符串替换a字符串中与OLD字符串完全重合的部分并返回a

  • 参数说明

    参数均为STRING类型。

  • 返回值说明

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

  • 示例
    REPLACE('ababab','abab','12')='12ab'
    REPLACE('ababab','cdf','123')='ababab'
    REPLACE('123abab456ab',null,'abab')=null

SOUNDEX

  • 命令格式
    STRING SOUNDEX(STRING a)
  • 命令说明

    将普通字符串转换成soundex字符串。

  • 参数说明

    a:STRING类型。

  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

  • 示例
    SOUNDEX('hello')='H400'

SUBSTRING_INDEX

  • 命令格式
    STRING SUBSTRING_INDEX(STRING a, STRING sep, INT count)
  • 命令说明

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

  • 参数说明

    asep:STRING类型,count为INT类型。

  • 返回值说明

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

  • 示例
    SUBSTRING_INDEX('https://help.aliyun.com', '.', 2)='https://help.aliyun'
    SUBSTRING_INDEX('https://help.aliyun.com', '.', -2)='aliyun.com'
    SUBSTRING_INDEX('https://help.aliyun.com', null, 2)=null
升级到MaxCompute2.0后,产品扩展了部分日期函数。如果您用到的函数涉及新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),在使用扩展日期函数时,需要执行如下set语句开启新数据类型开关。
  • Session级别:如果使用新数据类型,您需要在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:Project Owner可根据需要对Project进行设置,等待10~15分钟后才会生效。命令如下。
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项,请参见数据类型版本说明

TRANSLATE

  • 命令格式
    STRING TRANSLATE(STRING|varchar str1, STRING|varchar str2, STRING|varchar str3)
  • 命令说明

    str1出现在str2中的字符串替换成str3中的字符串。

  • 返回值说明

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

  • 示例
    TRANSLATE('MaxComputer','puter','pute')='MaxCompute'
    TRANSLATE('aaa','b','c')='aaa'
    TRANSLATE('MaxComputer','puter',null)=null
升级到MaxCompute2.0后,产品扩展了部分日期函数。如果您用到的函数涉及新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY),在使用扩展日期函数时,需要执行如下set语句开启新数据类型开关。
  • Session级别:如果使用新数据类型,您需要在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:Project Owner可根据需要对Project进行设置,等待10~15分钟后才会生效。命令如下。
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项,请参见数据类型版本说明

URL_ENCODE

  • 命令格式
    STRING URL_ENCODE(STRING input[, STRING encoding])
  • 命令说明
    将输入字符串编码为application/x-www-form-urlencoded MIME格式。
    • a~z、A~Z保持不变。
    • 英文句点(.)、短划线(-)、星号(*)和下划线(_)保持不变。
    • 空格转为加号(+)。
    • 其余字符根据指定的encoding转为字节值,然后将每个字节值表示为%xy的格式,xy是该字符的十六进制表示方式。
  • 参数说明
    • input:要输入的字符串。
    • encoding:指定的编码格式,支持GBK或UTF-8等标准编码格式,不输入默认为UTF-8。
  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

  • 示例
    URL_ENCODE('示例for URL_ENCODE:// (fdsf)') = "%E7%A4%BA%E4%BE%8Bfor+URL_ENCODE%3A%2F%2F+%28fdsf%29"
    URL_ENCODE('Example for URL_ENCODE:// dsf(fasfs)', 'GBK') = "Example+for+URL_ENCODE+%3A%2F%2F+dsf%28fasfs%29"

URL_DECODE

  • 命令格式
    STRING URL_DECODE(STRING input[, STRING encoding])
  • 命令说明
    将输入字符串从application/x-www-form-urlencoded MIME格式转为正常字符串,是URL_ENCODE的逆过程。
    • a~z、A~Z保持不变。
    • 英文句点(.)、短划线(-)、星号(*)和下划线(_)保持不变。
    • 加号(+)转为空格。
    • %xy格式的序列转为对应的字节值,连续的字节值根据输入的encoding名称解码为对应的字符串。
    • 其余的字符保持不变。
    • 函数最终的返回值是UTF-8编码的字符串。
  • 参数说明
    • input:要输入的字符串。
    • encoding:指定的编码格式,支持GBK或UTF-8等标准编码格式,不输入默认为UTF-8。
  • 返回值说明

    返回STRING类型。如果输入参数为NULL,则返回NULL。

  • 示例
    URL_DECODE('%E7%A4%BA%E4%BE%8Bfor+URL_DECODE%3A%2F%2F+%28fdsf%29')= "示例for URL_DECODE:// (fdsf)"
    URL_DECODE('Example+for+URL_DECODE+%3A%2F%2F+dsf%28fasfs%29', 'GBK') = "Example for URL_DECODE:// dsf(fasfs)"

JSON_TUPLE

  • 命令格式
    STRING JSON_TUPLE(STRING json,STRING key1,STRING key2,...)
  • 命令说明

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

  • 参数说明
    • JSON:STRING类型,标准的JSON格式字符串。
    • key:STRING类型,用于描述在JSON中的path,一次可输入多个,不能以美元符号($)开头。
  • 返回值

    返回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的值如下。
    Table:school
    +------------+------------+
    | Id         | json       |
    +------------+------------+
    | 1          | {
                    "校名": "湖畔大学",
                    "地址":"杭州",
                    "SchoolRank": "00",
                    "Class1":{
                      "Student":[{
                        "studentId":1,
                        "scoreRankIn3Year":[1,2,[3,2,6]]
                      }, {
                        "studentId":2,
                        "scoreRankIn3Year":[2,3,[4,3,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]]}]} |
    +-------+-------+
    支持解析包含中文的JSON数据。
    SELECT JSON_TUPLE(school.json,"校名","地址") AS (item0,item1) FROM school;
    --返回值
    +-------+-------+
    | item0 | item1 |
    +-------+-------+
    | 湖畔大学 | 杭州 |
    +-------+-------+
    支持解析多层嵌套的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]] |
    +------------+-------+-------+
    支持解析包含多重嵌套数组的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     |
    +------------+-------+-------+