字符串函數 · Hadoop2.x

●▲● **`array split(string str, string pat)`** -- 按照 pat 字符串分割 str，返回分割后的字符串數組 ```sql hive> select split('abtcdtef','t') from test; ["ab","cd","ef"] ``` ●▲● **`string concat(string A, string B, ...)`** -- 拼接字符串，返回字符串AB ```sql select concat('abc', 'def', 'ghf'); +------------+--+ | _c0 | +------------+--+ | abcdefghf | +------------+--+ select concat(id, name) from test; +------------+--+ | _c0 | +------------+--+ | 1zhangsan | | 2lisi | +------------+--+ ``` ●▲● **`string concat_ws(string SEP, string A, string B, string ...)`** - -- 拼接字符串，返回字符串AB -- SEP： A與B的分割符 ```sql select concat_ws(',', "abc", "def", "gh"); +-------------+--+ | _c0 | +-------------+--+ | abc,def,gh | +-------------+--+ ``` ●▲● **`string substr(string A, int start)`** **`string substr(string A, int start, int len)`** **`string substring(string A, int start)`** **`string substring(string A, int start, int len)`** -- 截取字符串A ```sql select substr('abcde', 3); +------+--+ | _c0 | +------+--+ | cde | +------+--+ select substr('abcde', -1); +------+--+ | _c0 | +------+--+ | e | +------+--+ ``` ●▲● **`int instr(string str, string substr)`** -- 返回substr在str首次出現的位置 ```sql select instr("abcdef", "de"); +------+--+ | _c0 | +------+--+ | 4 | +------+--+ ``` ●▲● **int lenth(string A)`** -- 返回字符串A的長度 ```sql select length("abc"); +------+--+ | _c0 | +------+--+ | 3 | +------+--+ ``` ●▲● **`int locate(string substr, string str[, int pos])`** -- 返回substr在str中首次出現的位置，從pos開始查找 ```sql select locate('a', 'abcda'); +------+--+ | _c0 | +------+--+ | 1 | +------+--+ select locate('a', 'abcda', 2); +------+--+ | _c0 | +------+--+ | 5 | +------+--+ ``` ●▲● **`string upper(string A)`** **`string ucase(string A)`** -- 都是將字符串A轉換為大寫 ```sql select upper('abcdE'); +--------+--+ | _c0 | +--------+--+ | ABCDE | +--------+--+ select ucase('abcdE'); +--------+--+ | _c0 | +--------+--+ | ABCDE | +--------+--+ ``` ●▲● **`string lower(string A)`** **`string lcase(string A)`** -- 都是將字符串轉換為小寫 ```sql select lower('ABCDe'); +--------+--+ | _c0 | +--------+--+ | abcde | +--------+--+ select lcase('ABcD5'); +--------+--+ | _c0 | +--------+--+ | abcd5 | +--------+--+ ``` ●▲● **`string trim(string A)`** 去除字符串A兩邊的空格； `string ltrim(string A)` 去除字符串A左邊空格； `string rtrim(string A)`去除字符串A右邊空格； ```sql select trim(' ab c '); +-------+--+ | _c0 | +-------+--+ | ab c | +-------+--+ ``` ●▲● **`string regexp_replace(string A, string regx, string C)`** -- 將A中符合java正則表達式 regx 的部分用C替換 ```sql select regexp_replace('foobar', 'oo|ar', ''); +------+--+ | _c0 | +------+--+ | fb | +------+--+ ``` ●▲● **`string regexp_extract(string subject, string pattern, int index)`** -- 將subject按照pattern正則規則拆分，返回拆分后index位置的字符 ```sql select regexp_extract('foothebar','foo(.*?)(bar)', 2); +------+--+ | _c0 | +------+--+ | bar | +------+--+ ``` 注意：在有些情況下要使用轉義字符，下面的等號要用雙豎線轉義，這是 java 正則表達式的規則。 ```sql select data_field, regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa, regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1)as bbb, regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) asccc from pt_nginx_loginlog_st where pt = '2012-03-26' limit 2; ``` ●▲●`int ascii(string str)`查看str字符串中第一個字符的ascii值 ```sql select ascii('ab') as ascii; +--------+--+ | ascii | +--------+--+ | 97 | +--------+--+ select ascii(name) as ascii from test; +--------+--+ | ascii | +--------+--+ | 122 | | 108 | +--------+--+ ``` ●▲●`string base64(binary bin)`返回二進制bin的base64編碼格式 ```sql select base64(binary('test')); +-----------+--+ | _c0 | +-----------+--+ | dGVzdA== | +-----------+--+ select base64(binary(name)) from test; +---------------+--+ | _c0 | +---------------+--+ | emhhbmdzYW4= | | bGlzaQ== | +---------------+--+ ``` ●▲●`string concat_ws(string SEP, array<string>)` -- 將數組以字符串返回 -- SEP 字符串元素分割符 ```sql select concat_ws('|', array('a', 'b', 'cd')); +---------+--+ | _c0 | +---------+--+ | a|b|cd | +---------+--+ -- 列轉行 select user_id, concat_ws(",", collect_list(order_id)) as order_value from col_lie group by user_id limit 10; ``` ●▲●`string format_number(number x, int d)` -- 將x保留d位小數，四舍五入 ```sql select format_number(5.23456, 3); +--------+--+ | _c0 | +--------+--+ | 5.235 | +--------+--+ ``` ●▲●`map<string, string> str_to_map(text[, delimiter1, delimiter2])` -- 將字符串text按照給定的分割符轉換為map結構 ```sql select str_to_map('key1:value1,key2:value2'); +------------------------------------+--+ | _c0 | +------------------------------------+--+ | {"key1":"value1","key2":"value2"} | +------------------------------------+--+ select str_to_map('k1=v1,k2=v2', ',', '='); +------------------------+--+ | _c0 | +------------------------+--+ | {"k1":"v1","k2":"v2"} | +------------------------+--+ ``` ●▲●`string printf(string format, obj... args)` -- 將obj進行format格式化 ```sql select printf("%08X", 123); +-----------+--+ | _c0 | +-----------+--+ | 0000007B | +-----------+--+ ``` ●▲●`binary unbase64(string base64str)` -- 將base64str解碼為二進制 ```sql select base64(binary("hive")); +-----------+--+ | _c0 | +-----------+--+ | aGl2ZQ== | +-----------+--+ select unbase64('aGl2ZQ=='); +-------+--+ | _c0 | +-------+--+ | hive | +-------+--+ ``` ●▲● `string parse_url(string url, string partToExtract[, string keyToExtract])` -- 返回URL中指定的部分 -- partToExtract取值有`HOST`, `PATH`, `QUERY`,`REF`,`PROTOCOL`, `AUTHORITY`, `FILE`,`USERINFO`. ```sql select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST'); +---------------+--+ | _c0 | +---------------+--+ | facebook.com | +---------------+--+ select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k1'); +------+--+ | _c0 | +------+--+ | v1 | +------+--+ ``` ●▲●`string get_json_object(string json_str, string path)` -- 返回json_str中與path匹配的內容，如果沒有匹配返回NULL ```sql select get_json_object('{"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'); +------+--+ | _c0 | +------+--+ | amy | +------+--+ ``` ●▲●`string space(int n)`返回長度為 n 的字符串 ```sql hive> select space(10) from test; hive> select length(space(10)) from test; 10 ``` ●▲●`string repeat(string str, int n)`返回重復 n 次后的 str 字符串 ```sql hive> select repeat('abc',5) from test; abcabcabcabcabc ``` ●▲●`string lpad(string str, int len, string pad)` -- 將 str 進行用 pad 進行左補足到 len 位 ```sql hive> select lpad('abc',10,'td') from test; tdtdtdtabc ``` 注意:與 GP,ORACLE 不同,pad 不能默認 ●▲●`string rpad(string str, int len, string pad)` -- 將 str 進行用 pad 進行右補足到 len 位 ``` hive> select rpad('abc',10,'td') from test; abctdtdtdt ``` ●▲●`int find_in_set(string str, string strList)` -- 返回 str 在 strlist 第一次出現的位置,strlist 是用逗號分割的字符串。如果沒有找到該 str 字符,則返回 0 ```sql hive> select find_in_set('ab','ef,ab,de') from test; 2 hive> select find_in_set('at','ef,ab,de') from test; 0 ``` ●▲●`array<array<string>> sentences(string str, string lang, string locale)` -- 返回輸入 str 分詞后的單詞數組 ```sql hive> select sentences('hello word!hello hive,hi hive,hellohive') from test; OK [["hello","word"],["hello","hive","hi","hive","hello","hive"]] ``` ●▲●`array<struct<string, double>> ngrams(array<array<string>>, int N, intK, int pf)` -- 與 sentences()函數一起使用,分詞后,統計分詞結果中一起出現頻次最高的 TOP-K 結果 ```sql hive> SELECT ngrams(sentences('hello word!hellohive,hi hive,hello hive'),2,2) FROM test; [{"ngram":["hello","hive"],"estfrequency":2.0},{"ngram":["hive","hello"],"estfreq uency":1.0}] ``` 該查詢中，統計的是兩個詞在一起出現頻次最高的 TOP-2；結果中，hello 與 hive 同時出現 2 次 ●▲● ``` array<struct<string,double>> context_ngrams(array<array<string>>,array<string>, int K, int pf) ``` -- 與 sentences()函數一起使用,分詞后，統計分詞結果中與數組中指定的單詞一起出現(包括順序)頻次最高的 TOP-K 結果 ```sql hive> SELECT context_ngrams(sentences('helloword!hello hive,hi hive,hello hive') ,array('hello',null),3) FROM test; [{"ngram":["hive"],"estfrequency":2.0},{"ngram":["word"],"estfrequency":1.0}] ``` 該查詢中，統計的是與’hello’一起出現，并且在 hello 后面的頻次最高的TOP-3；結果中，hello 與 hive 同時出現 2 次，hello 與 word 同時出現 1 次。 ```sql hive> SELECT context_ngrams(sentences('hello word!hello hive,hi hive,hello hive') ,array(null,'hive'),3) FROM test; [{"ngram":["hello"],"estfrequency":2.0},{"ngram":["hi"],"estfrequency":1.0}] ``` 該查詢中，統計的是與’hive’一起出現，并且在 hive 之前的頻次最高的 TOP-3