hive translate()函数的使用

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.
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'

先看下官方案例

select translate('abcdef', 'adc', '19') translate_exe
1b9ef

函数用法

translate(input,from,to)

• input：输入字符串
• from：需要匹配的字符
• to ：用哪些字符来替换被匹配到的字符

注意点：这里from的字符与to字符在位置上存在一 一对应关系，也就是from中每个位置上的字符用to中对应位置的字符替换。

场景1、from 和 to 长度一样，如translate("abcdef-abcdef","abcdef","123456");替换不是说把"abcdef"替换成"123456"，而是把a替换成1，把b替换成2，把c替换成3，把d替换成4，e替换成5，f替换成6

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，重复的字符a对应to的替换不会起作用

select TRANSLATE ('abcdaabbaaabbb','aa','12')--1bcd11bb111bbb