Hive learning built-in conditions and string functions __ functions

Conditional Functions

The following table provides some of the conditional functions that hive supports.

return type	Name of function	Description
T	If (Boolean testcondition, T valuetrue, T Valuefalseornull)	If Testcondition is true, return valuetrue, otherwise return valuefalseornull
T	COALESCE (t v1, T v2, ...)	Returns the first non-null V, if all is null, returns null
T	Case a while B then C [when D then e]* [ELSE F] END	When A=b returns C, when A=d returns E, the other returns F
T	Case when A and B [When C then d]* [ELSE E] END	When A=true returns B, when C=true returns D, the other returns E

String Functions

The following table is a string function built into hive.

return type	Name of function	Description
Int	ASCII (String str)	Returns the value of the first string of STR
String	Base64 (binary bin)	Converting binary arguments to base64 strings
String	Concat (string|binary A, string|binary B ...)	Returns a string that connects A and b sequentially, such as: concat (' foo ', ' Bar ') returns ' Foobar '
Array<struct<string,double>>	Context_ngrams (ARRAY<ARRAY<STRING>>, array<string>, int K, int pf)	Returns the first k of text from a set of marked sentences
String	Concat_ws (String SEP, String A, String B ...)	Similar to Concat (), but uses a custom delimiter for Sep
String	Concat_ws (String SEP, array<string>)	Similar to Concat_ws (), but the argument is an array of strings
String	Decode (binary bin, string charset)	Decodes the first argument to a string using the specified character set, or null if any one of the arguments is null. The optional character sets are: ' Us_ascii ', ' iso-8859-1 ', ' UTF-8 ', ' utf-16be ', ' utf-16le ', ' UTF-16 '
Binary	Encode (string src, string charset)	Encodes the first argument to binary using the specified character set, or null if either argument is null
Int	Find_in_set (String str, string strlist)	Returns the position of the first occurrence of STR in strlist, strlist a comma-delimited string, or 0 if Str contains a comma, or null if any argument is null. such as: Find_in_set (' ab ', ' Abc,b,ab,c,def ') returns 3
String	Format_number (number x, int d)	The number x is formatted as ' #,###,###.## ', rounded to the D-bit decimal place, and the result is returned as a string. If d=0, the result does not include a decimal point or fractional fraction
String	Get_json_object (string json_string, String path)	Extracts a JSON object from a JSON string based on JSON path, returns a JSON string for the JSON object, and returns null if the input JSON string is invalid. The Json path can only have numbers, letters, and underscores, and does not allow uppercase and other special characters
Boolean	In_file (String str, string filename)	Returns True if Str appears as a positive line in filename
Int	InStr (String str, string substr)	Returns the position of the first occurrence of substr in Str. If any parameter is NULL, returns NULL if SUBSTR does not return 0 in Str. The position of the first character in STR is 1
Int	Length (String A)	Returns the length of a
Int	Locate (string substr, string str[, int pos])	Returns the position of the first occurrence of substr in the position pos after Str
String	Lower (String a) LCase (string a)	Returns the lowercase form of a string
String	Lpad (string str, int len, string pad)	Fill the left Str with a string pad, len
String	LTrim (String A)	Remove the space on the left side of string A, such as: LTrim (' foobar ') result is ' foobar '
Array<struct<string,double>>	Ngrams (array<array<string>>, int N, int K, int pf)	Returns the first K n-grams from a set of marked returns the top-k sentence
String	Parse_url (String urlstring, String parttoextract [, String keytoextract])	Returns the specified portion of the given URL, with valid values for parttoextract including Host,path, QUERY, REF, PROTOCOL, Authority,file, and UserInfo. For example: Parse_url (' http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1 ', ' HOST ') returns ' facebook.com '. When the second argument is QUERY, you can use the third parameter to extract the value of a specific parameter, for example: Parse_url (' http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1 ', ' QUERY ', ' K1 ') return ' v1 '
String	printf (String format, Obj ... args)	Format output of input parameters
String	Regexp_extract (string subject, string pattern, int index)	Uses the pattern to extract a string from a given string. such as: Regexp_extract (' Foothebar ', ' foo (. *?) (bar) ', 2) return ' bar ' sometimes requires the use of a predefined character class: Use ' \s ' as the second parameter will match s, ' s ' matches whitespace, and so on. The index of the parameter is the one in the Java regular Match method group () method
String	Regexp_replace (String initial_string, String PATTERN, string replacement)	Use replacement to replace a substring in the string initial_string that matches the pattern, for example: Regexp_replace ("Foobar", "Oo|ar", "") returns ' FB '
String	Repeat (string str, int n)	Repeat str n times
String	Reverse (String A)	Flip the string A
String	Rpad (string str, int len, string pad)	Use pad to length Len on the right side of Str
String	RTrim (String A)	Remove the space to the right of string A, such as: RTrim (' Foobar ') returns ' Foobar '
Array<array<string>>	Sentences (string str, string lang, string locale)	The natural language text is processed into words and sentences, and each sentence is split at the appropriate boundary, returning an array of words. The parameters lang and local are optional parameters, for example: sentences (' Hello there! How is it? ') Return (("Hello", "There"), ("How", "is", "you")
String	Space (int n)	A string that returns n spaces
Array	Split (String str, string pat)	Splitting a string str,pat a regular expression with Pat
Map<string,string>	Str_to_map (text[, Delimiter1, Delimiter2])	Divides the text into key-value pairs using two separators. The first delimiter divides the text into k-v pairs, and the second delimiter separates each k-v pair. The default first delimiter is "," and the second delimiter is =
String	SUBSTR (string|binary A, int start) substring (string|binary A, int start)	Returns a substring from position start until end
String	SUBSTR (string|binary A, int start, int len) substring (string|binary A, int start, int len)	Returns a substring from position start with Len length, such as: substr (' Foobar ', 4, 1) returns ' B '
String	Translate (string input, string from, string to)	Replace the character that appears in the from in input with the string in to, or null if any argument is null
String	Trim (String A)	Remove spaces at both ends of string a
Binary	Unbase64 (String str)	Converts a Base64 string to a binary
String	Upper (String a) UCase (string a)	Returns the uppercase form of string a