LEFT and RIGHT functions in Apache Impala

String functions are used to execute an input string operation and return the output string. There are many built-in string functions available on almost all RDBMS platforms and big data tools such as Hive and Impala. Names can differ slightly from each other, but the functionality is the same. For eg, the LEFT and RIGHT functions in SQL Server are similar to the STRLEFT and STRRIGHT functions in Cloudera’s Impala. However, the functions LEFT and RIGHT, which have the same name, are also accessible and perform the same function.

LEFT(STRING a, INT num_chars)
Purpose: Returns the leftmost characters of the string.

RIGHT(STRING a, INT num_chars)
Purpose: Returns the right-most characters of the string.

The LEFT() function extracts a number of characters from a string (starting from left). Similarly The RIGHT() function extracts a number of characters from a string (starting from right). The return type is ‘STRING’.

Instead of using SUBSTR function with 2 arguments, this is a straight forward function where we can extract the sub-string from a given input.

Examples:
SELECT LEFT(‘BigData & SQL’, 3);
Returns “Big”
SELECT LEFT(ename, 5) FROM empInfo;
Returns first 5 characters from the each employee name.
SELECT RIGHT(‘BigData & SQL’, 3);
Returns “SQL”
SELECT RIGHT(ename, 5) FROM empInfo;
Returns last 5 characters from the each employee name.