[jira] [Commented] (HIVE-9472) Implement 7 simple UDFs added to Hive

["Alexander Pivovarov (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-9472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14306233#comment-14306233 ] 

Alexander Pivovarov commented on HIVE-9472:
-------------------------------------------

hive trunk already has:
- LAST_DAY(d)
- NEXT_DAY(d, name)
- ADD_MONTHS (date, integer)

to get FIRST_DAY:
{code}
concat(substr(date,1,7), '-01')
{code}


> 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)