LOCATE Function in Apache Hive & Apache Impala

The LOCATE function is also helpful when looking for a substring in a string, similar to the INSTR function.

• Note: The below specified function is common in both Apache Hive & Apache Impala.

Syntax: locate(string substr, string str[, int pos])
Returns the position of the first occurrence of substr in str after position pos.

Below are the examples.
SELECT LOCATE(‘gram’, ‘This is posted on instagram’);
Returns 23, the position of the substring ‘gram’ in the string.

SELECT LOCATE(‘st’, ‘This is posted on instagram’,1);
This returns 11, the position of ‘st’ in the string.

Hope you liked this post.

Please click on the follow button for latest updates.