Hive Common function __ function

This article is reproduced from: http://blackproof.iteye.com/blog/2108353

String functions

String length function: Length

Syntax: Length (String a)
return value: int
Description: Returns the length of the string a
example:
hive> Select Length (' ABCEDFG ') from dual;
7

String reversal function: Reverse

Syntax: Reverse (String a)
return value: String
Description: Returns the reverse result of the string A
example:
hive> Select reverse (' ABCEDFG ') from dual;
Gfdecba

string concatenation function: concat

Syntax: concat (String A, String B ...)
return value: String
Description: Returns the result of the input string concatenation, supporting any input string
example:
hive> Select concat (' abc ', ' Def ', ' GH ') from dual;
Abcdefgh

Delimited string Join function: Concat_ws

Syntax: Concat_ws (String SEP, String A, String B ...)
return value: String
Description: Returns the result of the input string connection, Sep represents the separator between each string
:
hive> Select Concat_ws (', ', ' abc ', ' Def ', ' GH ') from Dual;
Abc,def,gh

String intercept function: substr,substring

Syntax: substr (string A, int start), substring (string a, int start)
return value: String
Description: Return string A from start position to end of
example:
hive> Select substr (' ABCDE ', 3) from dual;
CDE
hive> Select substring (' ABCDE ', 3) from dual;
CDE
hive>  Select substr (' ABCDE ', -1) from dual;  (Same as Oracle)
E

String intercept function: substr,substring

Syntax: substr (string A, int start, int len), substring (string A, int start, int len)
return Value: String
Description: Returns A starting position from start, long Example of a string of Len
:
hive> Select substr (' ABCDE ', 3,2) from dual;
CD
hive> Select substring (' ABCDE ', 3,2) from dual;
CD
hive>select substring (' ABCDE ', -2,2) from dual;
De

String capitalization function: Upper,ucase

Syntax: Upper (String a) UCase (string a)
return value: String
Description: Returns the uppercase format of the string a
example:
hive> Select Upper (' absed ') from dual;
absed
hive> Select UCase (' absed ') from dual;
Absed

String to lowercase function: lower,lcase

Syntax: Lower (String a) LCase (string a)
return value: String
Description: Returns the lowercase format of the string A for
example:
hive> Select lower (' absed ') from dual;
absed
hive> Select LCase (' absed ') from dual;
Absed

Go to the space function: Trim

Syntax: Trim (string A)
return value: String
Description: Remove the space on both sides of the string for
example:
hive> Select Trim (' abc ') from dual;
Abc

Left Go space function: LTrim

Syntax: LTrim (string A)
return value: String
Description: Remove the space to the left of the string for
example:
hive> Select LTrim (' abc ') from dual;
Abc

Right go. Space function: RTrim

Syntax: RTrim (string A)
return value: String
Description: Remove the space to the right of the string for
example:
hive> select RTrim (' abc ') from dual;
Abc

Regular expression parsing function: Regexp_extract

Where the index is positioned according to the regular string ()

Syntax: Regexp_extract (string subject, string pattern, int index)
return value: String
Description: Splits the string subject according to the rules of the pattern regular expression, Returns the character specified by index. Note that in some cases you want to use an escape character for
example:
hive> Select Regexp_extract (' Foothebar ', ' foo (. *?) (bar) ', 1) from dual;
the hive> Select Regexp_extract (' Foothebar ', ' foo (. *?) (bar) ', 2) from dual;
Bar
hive> Select Regexp_extract (' Foothebar ', ' foo (. *?) (bar) ', 0) from dual;
Foothebar

function parse_url, parsing URL string

Parse_url (URL, parttoextract[, key])-extracts a part from a URL
resolves the URL string, and the Parttoextract option contains [Host,path,query,ref, Protocol,file,authority,userinfo].

For example:
* parse_url (' http://facebook.com/path/p1.php?query=1 ', ' HOST ') returns ' Facebook.com ' 
* parse_url (' http ://facebook.com/path/p1.php?query=1 ', ' path ') return to '/path/p1.php ' 
* parse_url (' http://facebook.com/path/p1.php? Query=1 ', ' QUERY ') returns ' Query=1 ',
you can specify a key to return specific parameters, such as
* Parse_url (' http://facebook.com/path/p1.php?query=1 ', ' Query ', ' query ') returns ' 1 ',

* parse_url (' http://facebook.com/path/p1.php?query=1#Ref ', ' ref ') returns ' ref ' 
* parse_ URL (' http://facebook.com/path/p1.php?query=1#Ref ', ' PROTOCOL ') returns ' HTTP '

JSON parsing function: Get_json_object

Syntax: Get_json_object (string json_string, String path)

return value: String
Description: Parses the string json_string of JSON, returning the content specified by path. Returns null if the JSON string entered is invalid.
Example:
hive> Select  get_json_object (' {store ':
>   {"Fruit": \[{"Weight": 8, "type": "Apple"} , {"Weight": 9, "type": "Pear"}],
>    "Bicycle": {"Price": 19.95, "Color": "Red"}
>   },
> "  Email": "Amy@only_for_json_udf_test.net",
>  "owner": "Amy"
>}
> ', ' $.owner ') from dual;
Amy

Use instance:

Select Get_json_object (' {store ': {"fruit": \["AA", "BB", "CC"}, "owner": "Amy"} ', ' $.store.fruit[0] ') from test_msg Limit 1;

Space String function: Spaces

Syntax: space (int n)

return value: String
Description: Returns an example of a string of length n
:
hive> Select Space (a) from dual;
Hive> Select Length (space) from dual;
10

Repeating string function: Repeat

Syntax: Repeat (string str, int n)

return value: String
Description: Returns an example of the STR string after repeated n times
:
hive> Select repeat (' abc ', 5) from dual;
Abcabcabcabcabc

First character ASCII function: ASCII

Syntax: ASCII (string str)

return value: Int
Description: return string str first character ASCII code
example:
hive> Select ASCII (' ABCDE ') from dual;
97

Left replenishment function: Lpad

Syntax: Lpad (string str, int len, string pad)

return value: String
Description: Use the pad to make the left complement to Len bit
example:
hive> Select Lpad (' abc ', ten, ' TD ') from dual;
Tdtdtdtabc

Unlike gp,oracle, pad cannot default

Right complement function: Rpad

Syntax: Rpad (string str, int len, string pad)

return value: String
Description: Use the pad to make a right complement to the Len bit
example:
hive> Select Rpad (' abc ', ten, ' TD ') from dual;
Abctdtdtdt

Split String function: Split

Syntax: Split (String str, string pat)

Return value:  array
Description: Splits str According to Pat String, returns the split string array for
example:
hive> Select Split (' Abtcdtef ', ' t ') from dual;
["AB", "CD", "EF"]

Collection Lookup function: Find_in_set

Syntax: Find_in_set (String str, string strlist)

return value: Int
Description: Returns the first occurrence of STR in strlist, Strlist is a comma-separated string. If the STR character is not found, it returns 0 for
example:
hive> Select Find_in_set (' ab ', ' Ef,ab,de ') from dual;
2
hive> Select Find_in_set (' at ', ' Ef,ab,de ') from dual;
0