[Hive] Hive字符串函数

String Functions in Hive

The string functions in Hive are listed below:

ASCII( string str )

The ASCII function converts the first character of the string into its numeric ascii value.

Example1: ASCII('abc') returns 97
Example2: ASCII('A') returns 65

CONCAT( string str1, string str2… )

The CONCAT function concatenates all the stings.

Example: CONCAT('hadoop','-','hive') returns 'hadoop-hive'

CONCAT_WS( string delimiter, string str1, string str2… )

The CONCAT_WS function is similar to the CONCAT function. Here you can also provide the delimiter, which can be used in between the strings to concat.

Example: CONCAT_WS('-','hadoop','hive','i','love','u') returns 'hadoop-hive-i-love-u'

FIND_IN_SET( string search_string, string source_string_list )

The FIND_IN_SET function searches for the search string in the source_string_list and returns the position of the first occurrence in the source string list. Here the source string list should be comma delimited one. It returns 0 if the first argument contains comma.

Example: FIND_IN_SET('ha','hao,mn,hc,ha,hef') returns 4

LENGTH( string str )

The LENGTH function returns the number of characters in a string.

Example: LENGTH('hive') returns 4

LOWER( string str ), LCASE( string str )

The LOWER or LCASE function converts the string into lower case letters.

Example: LOWER('HiVe') returns 'hive'

UPPER( string str ), UCASE( string str )

The UPPER or UCASE function converts the string into upper case letters.

Example: UPPER('HiVe') returns 'HIVE'

LPAD( string str, int len, string pad )

The LPAD function returns the string with a length of len characters left-padded with pad.

Example: LPAD('hive',6,'v') returns 'vvhive'

RPAD( string str, int len, string pad )

The RPAD function returns the string with a length of len characters right-padded with pad.

Example: RPAD('hive',6,'v') returns 'hivevv'

LTRIM( string str )

The LTRIM function removes all the leading spaces from the string.

Example: LTRIM('   hive') returns 'hive'

RTRIM( string str )

The RTRIM function removes all the tailing spaces from the string.

Example: LTRIM('hive   ') returns 'hive'

REPEAT( string str, int n )

The REPEAT function repeats the specified string n times.

Example: REPEAT('hive',2) returns 'hivehive'

REVERSE( string str )

The REVERSE function gives the reversed string

Example: REVERSE('hive') returns 'evih'

SPACE( int number_of_spaces )

The SPACE function returns the specified number of spaces.

SPACE(4) returns '    '

SPLIT( string str, string pat )

The SPLIT function splits the string around the pattern pat and returns an array of strings. You can specify regular expressions as patterns.

Example: SPLIT('hive:hadoop',':') returns ["hive","hadoop"]

The string functions in Hive are listed below:

SUBSTR( string source_str, int start_position [,int length] ), SUBSTRING( string source_str, int start_position [,int length] )

The SUBSTR or SUBSTRING function returns a part of the source string from the start position with the specified length of characters. If the length is not given, then it returns from the start position to the end of the string.

Example1: SUBSTR('hadoop',4) returns 'oop'
Example2: SUBSTR('hadoop',4,2) returns 'oo'

TRIM( string str )

The TRIM function removes both the tailing and leading spaces from the string.

Example: TRIM('   hive   ') returns 'hive'