Hive内嵌字符处理函数：字符函数concat_ws，locate,trim,lpad,rpad等

以你之姓@ 2022-05-30 06:42 204阅读 0赞

### 1.Hive内嵌字符串处理函数1 ###

<table> 
 <thead> 
 <tr> 
 <td style="vertical-align:middle;"> Return Type </td> 
 <th> Name(Signature) </th> 
 <th> Description </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> int&nbsp;&nbsp; </td> 
 <td> ascii(string str) </td> 
 <td> Returns the numeric value of the first &nbsp;character of str. 返回str中首个ASCII字符串的整数值 </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> base64(binary bin) &nbsp; &nbsp; unbase64(string str) </td> 
 <td> Converts the argument from binary to a base 64 string (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2482" rel="nofollow">0.12.0</a>).. 将二进制bin转换成64位的字符串 &nbsp; Converts the argument from a base 64 string to BINARY. (As of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2482" rel="nofollow">0.12.0</a>.). 将64位的字符串转换二进制值 </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> concat(string|binary A, string|binary B...) </td> 
 <td> Returns the string or bytes resulting from concatenating the strings or bytes passed in as parameters in order. For example, concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings.. 对二进制字节码或字符串按次序进行拼接 尖叫提示： 常用，字符串拼接可以有多个参数，同时拼接多个字符串，但无法指定拼接连接符，功能类似于“+”。 hive&gt; select concat('a','bc','+','de') as a, 、 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; concat('1','2') from a ; &nbsp;&nbsp; 结果： &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; abc+de ,12 &nbsp; </td> 
 </tr> 
 <tr> 
 <td> array &lt;struct&lt;string,double&gt;&gt; </td> 
 <td> context_ngrams(array&lt;array&lt;string&gt;&gt;, array&lt;string&gt;, int K, int pf) </td> 
 <td> Returns the top-k contextual N-grams from a set of tokenized sentences, given a string of "context". See&nbsp;<a href="https://cwiki.apache.org/confluence/display/Hive/StatisticsAndDataMining" rel="nofollow">StatisticsAndDataMining</a>&nbsp;for more information.. 与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列，具体看<a href="https://cwiki.apache.org/confluence/display/Hive/StatisticsAndDataMining" rel="nofollow">StatisticsAndDataMining</a>(这里的解释更易懂) </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> concat_ws(string SEP, string A, string B...)，这个也是聚合函数UDAF </td> 
 <td> Like concat() above, but with custom separator SEP.. 与concat()类似，但使用指定的分隔符喜进行分隔 尖叫提示： 同样可以进行字符串拼接，但是可以在第一个参数指定拼接连接符，而concat没有办法连接符，功能是concat的补充，如果指定第一个参数为‘’空字符串，功能和concat一样。 hive&gt;select concat_ws('','a','bc','+','de') a, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; concat_ws(',','a','b') &nbsp;&nbsp;&nbsp; j结果： &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; abc+de&nbsp;&nbsp;&nbsp;&nbsp; a,b </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> concat_ws(string SEP, array&lt;string&gt;) 遍历数组 </td> 
 <td> Like concat_ws() above, but taking an array of strings. (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2203" rel="nofollow">0.9.0</a>). 拼接Array中的元素并用指定分隔符进行分隔 尖叫提示： 其实就是concat_ws可以拼接遍历数组元素成字符串，这个concat干不了。这个很常用。尤其配合collect_set等使用。 hive&gt;select concat_ws('@',company_used_name) from a 比如表中字段company_used_name为数组，内容如下： ["铁岭市宏基砖业有限公司","铁岭市华隆装饰有限责任公司","铁岭宏太粮油有限公司"] 结果如下： 铁岭市宏基砖业有限公司@铁岭市华隆装饰有限责任公司@铁岭宏太粮油有限公司 &nbsp; </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> decode(binary bin, string charset) </td> 
 <td> Decodes the first argument into a String using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2482" rel="nofollow">0.12.0</a>.). 使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任意输入参数为NULL都将返回NULL </td> 
 </tr> 
 <tr> 
 <td> binary </td> 
 <td> encode(string src, string charset) </td> 
 <td> Encodes the first argument into a BINARY using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null. (As of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2482" rel="nofollow">0.12.0</a>.). 使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任一输入参数为NULL都将返回NULL </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> find_in_set(string str, string strList) 其实用的也不多 </td> 
 <td> Returns the first occurance of str in strList where strList is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument contains any commas. For example, find_in_set('ab', 'abc,b,ab,c,def') returns 3.. 返回以逗号分隔(必须是逗号)的字符串中str出现的位置，如果参数str为包含逗号或查找失败将返回0，如果任一参数为NULL将返回NULL回. &nbsp; hive&gt;select find_in_set('abc','dabc,ab,c,abcd,abc,babcd'), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; find_in_set('abc,','dabc,ab,c,abcd,abc,babcd'), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; find_in_set(null,'dabc,ab,c,abcd,abc,babcd'), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; find_in_set( 'de','dabc,ab,c,abcd,abc,babcd') &nbsp;&nbsp; &nbsp; &nbsp; 结果：&nbsp;&nbsp; &nbsp;&nbsp; 5&nbsp; 0&nbsp; null 0 </td> 
 </tr> 
 <tr> 
 <td> string </td> 
 <td> format_number(number x, int d) </td> 
 <td> Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. (As of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2694" rel="nofollow">0.10.0</a>; bug with float types fixed in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-7257" rel="nofollow">Hive 0.14.0</a>, decimal type support added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-7279" rel="nofollow">Hive 0.14.0</a>). 将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数 &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td style="width:198px;"> int </td> 
 <td style="width:270px;"> length(string A) </td> 
 <td style="width:436px;"> Returns the length of the string.. 返回字符串的长度 返回值: int，注意如果字符串是null值，则返回null值，如果为‘’，则返回0 举例： hive&gt; select length('abcedfg') from lxw_dual; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7 </td> 
 </tr> 
 <tr> 
 <td style="width:198px;"> int </td> 
 <td style="width:270px;"> locate(string substr, string str[, int pos]) []这里的参数是可选择的，可选可不选 注意第一个参数才是substr </td> 
 <td style="width:436px;"> Returns the position of the first occurrence of substr in str after position pos.. 查找字符串str中的pos位置后字符串substr第一次出现的位置 尖叫提示：如果locate，只有两个字符串参数，表示返回子字符串在后面父字符串中的首次出现位置，没有则返回0，若有参数为null，则返回null。如果有第三个int参数，则表示子串从父串的第几个字符开始查找。同样返回首个出现位置。如果没有第三个参数，功能跟instr一样。 hive&gt; select&nbsp; locate('abc','dfdabc,ab,abcdfd@abc'),--4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; locate('abc','dfdabc,ab,abcdfd@abc',4),--4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; locate('abc','dfdabc,ab,abcdfd@abc',5),--11 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; locate('abcee','dfdabc,ab,abcdfd@abc'),--0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; locate(null,'dfdabc,ab,abcdfd@abc')--null from&nbsp; FDM_SSA.T_PLPLPSS_PAC_REPAY_APPLY_PAY_ED a&nbsp; wher &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:198px;">&nbsp;</td> 
 <td style="width:270px;"> instr(string str, string substr) 注意第二个参数才是substr </td> 
 <td style="width:436px;"> Returns the position of the first occurrence of&nbsp;<code>substr</code>&nbsp;in&nbsp;<code>str</code>. Returns&nbsp;<code>null</code>&nbsp;if either of the arguments are&nbsp;<code>null</code>&nbsp;and returns&nbsp;<code>0</code>&nbsp;if&nbsp;<code>substr</code>&nbsp;could not be found in&nbsp;<code>str</code>. Be aware that this is not zero based. The first character in&nbsp;<code>str</code>&nbsp;has index 1.. 尖叫提示： 查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的，与locate类似,但是注意instr的参数第二个是substr,跟locate不一样。 hive&gt; select instr('abc','afdababcdddabceee'), --0 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; instr('afdababcdddabceee','abc')&nbsp; --6 &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:198px;"> string </td> 
 <td style="width:270px;"> lower(string A) lcase(string A) </td> 
 <td style="width:436px;"> Returns the string resulting from converting all characters of B to lower case. For example, lower('fOoBaR') results in 'foobar'.. 将字符串A的所有字母转换成小写字母 hive&gt; select lower('abSEd') from lxw_dual; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;absed hive&gt; select lcase('abSEd') from lxw_dual; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;absed &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:198px;"> string </td> 
 <td style="width:270px;"> lpad(string str, int len, string pad) 一般用来补足长度后用来截取字符串。 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rpad(string str, int len, string pad) </td> 
 <td style="width:436px;"> Returns str, left-padded with pad to a length of len.. 从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分 hive&gt; select lpad('abc',10,'td') from lxw_dual; tdtdtdtabc &nbsp; &nbsp; Returns str, right-padded with pad to a length of len.. 从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分 </td> 
 </tr> 
 <tr> 
 <td style="width:198px;"> string </td> 
 <td style="width:270px;"> ltrim(string A) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; trim(string A) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rtrim(string A) </td> 
 <td style="width:436px;"> Returns the string resulting from trimming spaces from the beginning(left hand side) of A. For example, ltrim(' foobar ') results in 'foobar '.. hive&gt; select ltrim(' abc ') from lxw_dual; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;abc &nbsp; &nbsp; Returns the string resulting from trimming spaces from both ends of A. For example, trim(' foobar ') results in 'foobar'. 将字符串A前后出现的空格去掉 &nbsp; &nbsp; Returns the string resulting from trimming spaces from the end(right hand side) of A. For example, rtrim(' foobar ') results in ' foobar'.. 去掉字符串后面出现的空格 &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:198px;">string</td> 
 <td style="width:270px;"> upper(string A) ucase(string A) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; initcap(string A) </td> 
 <td style="width:436px;"> Returns the string resulting from converting all characters of A to upper case. For example, upper('fOoBaR') results in 'FOOBAR'.. 将字符串A中的字母转换成大写字母 hive&gt; select upper('abSEd') from lxw_dual; &nbsp;&nbsp;&nbsp;&nbsp;ABSED hive&gt; select ucase('abSEd') from lxw_dual; &nbsp;&nbsp;&nbsp;&nbsp;ABSED &nbsp; Returns string, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by whitespace.&nbsp;(As of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-3405" rel="nofollow">1.1.0</a>.). 将字符串A转换第一个字母大写其余字母的字符串 &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:198px;"> string &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int </td> 
 <td style="width:270px;"> space(int n) &nbsp; &nbsp; &nbsp; soundex(string A) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; levenshtein(string A, string B) </td> 
 <td style="width:436px;"> Returns a string of n spaces.. 返回n个空格 &nbsp; &nbsp; Returns soundex code of the string&nbsp;(as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9738" rel="nofollow">1.2.0</a>). For example, soundex('Miller') results in M460.. 将普通字符串转换成soundex字符串 &nbsp; &nbsp; &nbsp; Returns the Levenshtein distance between two strings&nbsp;(as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9556" rel="nofollow">1.2.0</a>). For example, levenshtein('kitten', 'sitting') results in 3.. 计算两个字符串之间的差异大小 &nbsp;如：levenshtein('kitten', 'sitting') = 3 </td> 
 </tr> 
 </tbody> 
</table>