Section 9:93. String Manipulation – Trimming and Padding

In Hive, trimming and padding functions can be used to manipulate string data.

1. TRIM: The TRIM function is used to remove leading and trailing whitespace or other specified characters from a string. The syntax for the TRIM function is as follows:

TRIM([BOTH | LEADING | TRAILING] trim_character FROM input_string)

• BOTH: Removes the specified characters from both the beginning and end of the input string.
• LEADING: Removes the specified characters from the beginning of the input string.
• TRAILING: Removes the specified characters from the end of the input string.
• trim_character: Specifies the characters to remove from the input string. If not specified, whitespace is removed.

SELECT TRIM(‘   Hello World   ‘); — Returns ‘Hello World’

SELECT TRIM(BOTH ‘x’ FROM ‘xxxHello Worldxxx’); — Returns ‘Hello World’

2. PAD: The LPAD and RPAD functions are used to pad a string with a specified character to a certain length. The syntax for the LPAD and RPAD functions are as follows:

LPAD(input_string, length, pad_character)

RPAD(input_string, length, pad_character)

• input_string: Specifies the input string to pad.
• length: Specifies the total length of the resulting string after padding.
• pad_character: Specifies the character to use for padding. If not specified, whitespace is used.

For example:

SELECT LPAD(‘Hello’, 8, ‘*’); — Returns ‘***Hello’

SELECT RPAD(‘Hello’, 8, ‘*’); — Returns ‘Hello***’