String functions

bigint char_matchcount(string str1, string str2)

• str1, str2: String type, must be effective UTF-8 strings. If invalid character is in matching process, return a negative value.
• Return value: Bigint type, Any NULL input, return NULL.

char_matchcount('abd','aabc') = 2
-- Two strings 'a', 'b' in str1 appear in str2.

string chr(bigint ascii)

• ascii: Bigint type ASCII value. If the input is ‘string’ or ‘double’, it is converted to ‘bigint’ by implicit conversion. If the input is other types, an exception is thrown.
• Return value: String type. The parameter value range is [0,255]. An exception is thrown if exceeding this range. If the input is NULL, return NULL.

string concat(string a, string b...)

• a, b… String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String by implicit conversion. If the input is other types, an exception is thrown.
• String: Return value: String type. If no parameter exists or a certain parameter is NULL, return NULL.

concat('ab','c') = 'abc'
concat() = NULL
concat('a', null, 'b') = NULL

STRING GET_JSON_OBJECT(STRING json,STRING path)

• json: String type, standard json format string.
• path: String type, describing the path in json, starting with a dollor sign ($). For a description of the new implementation, see JsonPath.
  • $ : Root object
  • . : Child operator
  • [] : Subscript operator for array
  • * : Wildcard for []
• String: Returns string type.

+----+
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"
}

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"

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

• str1: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String by implicit conversion. If the input is other types, an exception is thrown.
• str2: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String by implicit conversion. If the input is other types, an exception is thrown.
• start_position: Bigint type, for other types, an exception is thrown. It indicates from which character of str1 a search must be started from and the default starting position is the first character position 1. If it is less than 0, it causes abnormality.
• nth_appearance：bigint type, greater than 0, represents position of the second match of a substring in the string. If the chain is of a different type or less than or equal to 0, an exception is thrown.
• Return value: Bigint type.

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

boolean is_encoding(string str, string from_encoding, string to_encoding)

• str: String type, if the input is NULL, return NULL. The empty string can be assumed to be belonged to any character set.
• from_encoding, to_encoding: String type, source, destination character sets. If the input is NULL, return NULL.
• Return value: Boolean type. If ‘str’ can be converted successfully, return true, otherwise, return false.

is_encoding('test', 'utf-8', 'gbk') = true
is_encoding('test', 'utf-8', 'gbk') = true
-- These two traditional Chinese characters are in GBK stock in China.
is_encoding('test', 'utf-8', 'gb2312') = false
-- The grapheme inventory of ‘GB2312’ does not contain these two Chinese characters.

KEYVALUE(STRING srcStr,STRING split1,STRING split2, STRING key)
KEYVALUE(STRING srcStr,STRING key) //split1 = ";"，split2 = ":"

• srcStr: Source string to be split.
• key: Specified to return the nth string. After the source string is split by ‘split1’ and ‘split2’, return the corresponding value according to the specification of the ‘key’ value.
• split1, split2: Strings used as delimiters by which ‘srcStr’ is split. If these two parameters are not specified in the expression, the default value of ‘split1’ is ’;’ and that of ‘split2’ is ’:’. If a string that has been split by split1 and has multiple split2, the return result is not defined.

• String type.
• If ‘split1’ or ‘split2’ is NULL, return NULL.
• If ‘scrStr’ and ‘key’ are NULL or in case of no matched ‘key’, return NULL.
• If multiple ‘key-value’ matches, return the value corresponding to the first matched key.

keyvalue('0:1\;1:2', 1) = '2'
				

0 1/  
1 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\;”

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

bigint length(string str)

• str: String type. If the input is Bigint，Double，Decimal or Datetime, it is converted to String by implicit conversion. If the input is other types, an exception is thrown.
• Return value: Bigint type. If ‘str’ is NULL, return NULL. If ‘str’ is non UTF-8 coding format, return -1.

length('hi! China') = 6

bigint lengthb(string str)

lengthb('hi!  China') = 10

string md5(string value)

string regexp_extract(string source, string pattern[, bigint occurrence])

• source: String type, a string to be searched.
• pattern: A string type constant. If pattern is a null string, an exception is thrown. If ‘group’ is not specified in pattern, then also an exception is thrown.
• Occurrence: A bigint type constant, must be greater than 0 or equal to 0. If it is other type or less than 0, an exception is thrown. If not specified, the default value is 1, which indicates returning the first group. If ‘occurrence’ is equal to 0, then return substrings that satisfy the entire ‘pattern’.
• Return value: String type. Any input is NULL, return NULL.

regexp_extract('foothebar', 'foo(. *?)( bar)', 1) = the
regexp_extract('foothebar', 'foo(. *?)( bar)', 2) = bar
regexp_extract('foothebar', 'foo(. *?)( bar)', 0) = foothebar
regexp_extract('8d99d8', '8d(\\d+)d8') = 99
-- If regular SQL is submitted on MaxCompute, two "\" must be used as the shift character.
regexp_extract('foothebar', 'foothebar')
-- The exception is thrown. ‘group’ is not specified in ‘pattern’.

bigint regexp_instr(string source, string pattern[,
bigint start_position[, bigint nth_occurrence[, bigint return_option]]])

• source: String type, to be searched.
• pattern: A string type constant. If ‘pattern’ is null, an exception is thrown.
• start_position: Bigint type constant, the start position of search. If it is not specified, default value is 1. If it is other type or a value is less than or equal to 0, an exception is thrown.
• nth_occurrence: A bigint type constant. If not specified, the default value is 1. It appears at the first position, when searched. If it is less than or equal to 0 or other type, an exception is thrown.
• return_option: A bigint type constant. Its value is 0 or 1. If it is other type or an invalid value, an exception is thrown. 0 indicates returning the start position of the matched value. 1 indicates returning the end position of the matched value.
• Return value: Bigint type, the start or end position of a matched substring in source specified by return_option.

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

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

• source: String type, a string to be replaced.
• pattern: String type constant. The pattern to be matched. If it is null, an exception is thrown.
• replace_string: String type, the string after replacing matched pattern.
• occurrence: Bigint type constant, must be greater than or equal to 0. It indicates replacing nth matching to be replace_string. If it is 0, it indicates all matched substrings have been replaced. If it is other type or less than 0, an exception is thrown. It can be 0 by default.
• Return value: String type. When referencing a group which is not existent, do not replace the string. Returns NULL when the source, pattern, occurrence parameter is entered as null, returns NULL, replace_string is null, but pattern will not match, if the replace_string is null and the pattern is matched, returns the original string.

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"
-- Only a group is defined in pattern and the referenced second group is not existent.
-- Please avoid this. The result to reference nonexistent group is not defined.
regexp_replace("abcd", "(. *)(.)$", "\\2", 0) = "d"
regexp_replace("abcd", "a", "\\1", 0) = "bcd"
-- No group definition is in pattern, so '\1' references a nonexistent group,
 -- Please avoid this. The result to reference nonexistent group is not defined.

string regexp_substr(string source, string pattern[, bigint start_position[, bigint nth_occurrence]])

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"

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

• Source: String type, the string to be searched. If it is the other type, an exception is thrown.
• Pattern: String type constant, the pattern to be matched. If it is a null string or other data type, an exception is thrown.
• start_position: Bigint type constant, must be greater than 0. If it is other data type or a value which is less than or equal to 0, an exception is thrown. If not specified, default value is 1, which indicates a matched value from the first character of source.
• Return value: Bigint type. If matching does not exists, return 0. If any input parameter is null, return null.

regexp_count('abababc', 'a.c') = 1
regexp_count('abcde', '[[:alpha:]]{2}', 3) = 1

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

• str: String type, the string to be split. If it is Bigint, Double, Decimal or Datetime, it is converted to a String in an implicit conversion. If it is other data type, an exception is thrown.
• separator: A string type constant, the separator used to split the string. It can be a character or a string. If it is other data type, an exception is thrown.
• start: A bigint type constant, must be greater than 0. If it is not a constant or other data type, an exception is thrown. It indicates the start number of the return part (start from 1). If the end is not specified, returns the part specified by ‘start’.
• ‘end’: A bigint type constant, must be greater than or equal to ‘start’, otherwise an exception is thrown. It refers to the end number of the return part. If it is not a constant or is other data type, then also an exception is thrown. It can be excluded as it indicates the last part.

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) = ''

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

• ‘str’: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is thrown.
• The start_position:Bigint type starts at 1. Returns empty strings when start_position is 0.When start_position is negative, the starting position is counted backwards from the end of the string, the last character is -1, and the previous number is -2,-3 and so on. Other types throw exceptions.
• length: Bigint type, must be greater than 0. If it is other type or less than 0, an exception is thrown. This parameter indicates the length of a child string.
• Return value: String type. If the input is NULL, return NULL.

substr("abc", 2) = "bc"
substr("abc", 2, 1) = "b"
substr("abc",-2,2)="bc"
substr("abc",-3)="abc"

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

• str: String or Binary type, returns NULL or throws an exception for the other type
• ‘start_position’: Int type, starting at 1. Returns empty strings when start_position is 0.When start_position is negative, the starting position is counted backwards from the end of the string, the last character is-1, and the previous number is in turn-2,-3 and so on. Other types throw exceptions.
• length: Bigint type, must be greater than 0. If it is other type or less than 0, an exception is thrown. This parameter indicates the length of the child string.
• Return value: String type. If the input is NULL, return NULL.

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'

string tolower(string source)

• Source: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is throwm.
• Return Value: String type. If the input is NULL, return NULL.

tolower("aBcd") = "abcd"
tolower("Haha Cd") = "haha cd"

string toupper(string source)

• Source: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is throwm.
• Return Value: String type. If the input is NULL, return NULL.

toupper("aBcd") = "ABCD"
toupper("HahaCd") = "HAHACD"

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

• Value: Boolean, Bigint or Double type is acceptable. If it is other data type, an exception is thrown. For formatted output of the datetime type, see another function TO_CHAR that has the same name.
• Return value: String type. If the input is NULL, return NULL.

to_char(123) = '123'
to_char(true) = 'TRUE'
to_char(1.23) = '1.23'
to_char(null) = NULL

string trim(string str)

• ‘str’: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is throwm.
• Return value: String type. If the input is NULL, return NULL.

string ltrim(string str)

• ‘str’: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is throwm.
• Return value: String type. If the input is NULL, return NULL.

select ltrim(' abc ') from dual;  
Returns: 
+-----+
| _c0 |
+-----+
| abc |
+-----+

string rtrim(string str)

• ‘str’: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is throwm.
• Return value: String type. If the input is NULL, return NULL.

select rtrim('a abc ') from dual; 
Returns:  
+-----+
| _c0 |
+-----+
| a abc |
+-----+

STRING REVERSE(string str)

• str: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is throwm.
• Return value: String type. If the input is NULL, return NULL.

select reverse('abcedfg') from dual;
Returns: 
+-----+
| _c0 |
+-----+
| gfdecba |
+-----+

STRING SPACE(bigint n)

• n: Bigint type. The length cannot exceed 2 MB. If it is NULL, an exception is thrown.
• Return value: String type.

select length(space(10)) from dual; ----Returns 10.   
select space(400000000000) from dual; ----Error, the length exceeds 2 MB.

STRING REPEAT(string str, bigint n)

• ‘str’: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is throwm.
• n: Bigint type. The length does not exceed 2 MB. If it is NULL, an exception is thrown.
• Return value: String type.

select repeat('abc',5) from lxw_dual; 
Returns:abcabcabcabcabc

Bigint ASCII(string str)

• str: String type. If the input is Bigint, Double, Decimal or Datetime, it is converted to String in an implicit conversion. If it is other data type, an exception is throwm.
• Return value: Bigint type.

select ascii('abcde') from dual;
Returns:97

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

string concat_ws(string SEP, string a, string b...)
string concat_ws(string SEP, array)

• SEP: String-type delimiter. If not specified, an exception is returned.

concat_ws(':','name','hanmeimei')='name:hanmeimei'
concat_ws(':','avg',null,'34')=null

string lpad(string a, int len, string b)

• len: Int-type integer.
• a/b…: String type.

lpad('abcdefgh',10,'12')='12abcdefgh'
lpad('abcdefgh',5,'12')='abcde'
lpad('abcdefgh',0,'12') Returns a blank result

string rpad(string a, int len, string b)

• len: Int-type integer.
• a/b…: String type.

rpad('abcdefgh',10,'12')='abcdefgh12'
rpad('abcdefgh',5,'12')='abcde'
rpad('abcdefgh',0,'12') Returns a blank result

string replace(string a, string OLD, string NEW)

replace('ababab','abab','12')='12ab'
replace('ababab','cdf','123')='ababab'
replace('123abab456ab',null,'abab')=null

string soundex(string a)

soundex('hello')='H400'

string substring_index(string a, string SEP, int count))