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