hive函数值translate之个人理解

desc function extended translate;

translate(input, from, to) - translates the input string by replacing the characters present in the from string with the corresponding characters in the to string

translate(string input, string from, string to) is an equivalent function to translate in PostGreSQL. It works on a character by character basis on the input string (first parameter). A character in the input is checked for presence in the from string (second parameter). If a match happens, the character from to string (third parameter) which appears at the same index as the character in from string is obtained. This character is emitted in the output string instead of the original character from the input string. If the to string is shorter than the from string, there may not be a character present at the same index in the to string. In such a case, nothing is emitted for the original character and it's deleted from the output string.

翻译 translate函数是和postgresql中的translate函数一样的功能，它会逐个的检查input字符串每个字符，如果在input中的某个字符在from的字符串中存在，则替换为在to字符串中的相同坐标下的字符。

如果to字符串比from短，那么在from字符串中的某个字符可能在to字符串中找不到相应的字符，那么就会在原字符串input中删除这个字符

For example,

translate('abcdef', 'adc', '19') returns '1b9ef' replacing 'a' with '1', 'd' with '9' and removing 'c' from the input string

translate('a b c d', ' ', '') return 'abcd' removing all spaces from the input string

If the same character is present multiple times in the input string, the first occurence of the character is the one that's considered for matching. However, it is not recommended to have the same character more than once in the from string since it's not required and adds to confusion.

For example,

translate('abcdef', 'ada', '192') returns '1bc9ef' replaces 'a' with '1' and 'd' with '9' ignoring the second occurence of 'a' in the from string mapping it to '2'

个人理解

translate(input, from, to)

input即是要被替换的整个字符

from这里也是你想要替换的字符，注意这里的字符不是字符串

to 即想用哪些字符替换或者删除

1如果 from 字符串长度=to的字符串长度translate("input"，"abcdef-abcdef"，"123456") ， 替换不是说把abcdef替换成123456！！！而是把a替换成1，把b替换成2，把c替换成3，把d替换成4

    select TRANSLATE('abcdef-abcdef','abcd','1234'),--1234ef-1234ef
            TRANSLATE('abcdef-abcdef','ab','12'),   --12cdef-12cdef
            TRANSLATE('abcdef-abcdef','ad','14'),    --1bc4ef-1bc4ef
            TRANSLATE('abcdef-abcdef','da','41')    --1bc4ef-1bc4ef

2如果 from 字符串长度>to的字符串长度 ，例如TRANSLATE('abcdef-abcdef','adbc','123')    意思是把 a替换为1，b替换为2，c替换为3，d删除掉
select TRANSLATE('abcdef-abcdef','abcd','123'), --123ef-123ef
 TRANSLATE('abcdef-abcdef','adbc','123') --132ef-132ef

3特殊情况

--from里有重复字符 比如abca，1231，重复的对应to的替换不会起作用

 select TRANSLATE ('abcdaabbaaabbb','aa','12')--1bcd11bb111bbb  说明 第二个a没用因为是从from里检查的如果有就不会看下一个了

-- from长度<to的长度，不报错但是to里面长的字符没有意义

 select TRANSLATE ('abcdaabbaaabbb','a','123')--1bcd11bb111bbb