[ https://issues.apache.org/jira/browse/HIVE-9472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14298066#comment-14298066 ]
Xiaobing Zhou commented on HIVE-9472: ------------------------------------- This work is split to separate JIRA(s) for tracking. SEE ALSO 'relates to' links. > Implement 7 simple UDFs added to Hive > ------------------------------------- > > Key: HIVE-9472 > URL: https://issues.apache.org/jira/browse/HIVE-9472 > Project: Hive > Issue Type: Bug > Components: UDF > Affects Versions: 0.14.0 > Reporter: Xiaobing Zhou > Assignee: Xiaobing Zhou > > PROBLEM: Hive doesn't not have the following built-in functions. > 1) MONTHS_BETWEEN(d1,d2) > 2) LAST_DAY(d) > 3) FIRST_DAY(d) > 4) ADD_MONTHS (date, integer) > 5) CHR() > 6) REPLACE(char, search_string, replacement_string) > 7) TRANSLATE(expr, from_string, to_string) > Without these, not able to do certain analysis. > USE CASE: > 1) MONTHS_BETWEEN(d1,d2) > 2) LAST_DAY(d) > 3) FIRST_DAY(d) > 4) ADD_MONTHS (date, integer) > ADD_MONTHS returns the date date plus integer months. If date is the last day > of the month or if the resulting month has fewer days than the day component > of date, then the result is the last day of the resulting month. Otherwise, > the result has the same day component as date. > 5) CHR() > CHR returns the character having the binary equivalent to n (where n is > between 0 and 255)as a VARCHAR2 value > 6) REPLACE(char, search_string, replacement_string) > REPLACE returns char with every occurrence of search_string replaced with > replacement_string. > SELECT REPLACE('JACK and JUE','J','BL') "Changes"; > Changes > -------------- > BLACK and BLUE > 7) TRANSLATE(expr, from_string, to_string) > TRANSLATE returns expr with all occurrences of each character in from_string > replaced by its corresponding character in to_string. > SELECT TRANSLATE('2KR 229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', > '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX-') (CHAR(26)) "License"; > License > ------- > 9XX-999 -- This message was sent by Atlassian JIRA (v6.3.4#6332)