SQL进阶--3__如何用hiveSQL删除字符串中多余的字符【详解TRANSLATE()函数】

最新推荐文章于 2024-09-19 16:34:12 发布

原创

最新推荐文章于 2024-09-19 16:34:12 发布 · 3.7k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#sql #hive #hadoop #odps

translate（）函数详解

0-需求
1-数据分析
2-总结

0-需求

删除某个字段中不需要的字符，如某字段为字符串，我需要删除字符串中所有的元音字符，怎么删除？

这里举例：给出样例字符串"ABCDEFGHIOUUFEBCAADDEEII"，我需要删除该字符串中的元音字符，也就是出现在该字段中包含"AEIOU"任意一个字符都需要删除掉。

1-数据分析

该需求的难点是需要去匹配"AEIOU"中的每一个字符，匹配出来后再进行删除，而每一个字符串中却包含了元音字符的任意组合，呈现出一定的无规律性，感觉无从下手，面对这种无规律性，存在随机组合的匹配模式的删除，我们采用数据库中提供的字符替换函数translate()函数。

translate()函数用法如下：

hive> desc function extended translate;
OK
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 ou