Hive LOCATE Function Explained

In Hive, the locate function is used to search for a specified substring within a string and return the position of the first occurrence of the substring. Its syntax is as follows:

LOCATE(substring, string)

In this case, “substring” is the sub-string to be searched for within the string.

For example, suppose there is a string ‘hello world’, you can use the locate function to find the position of the substring ‘world’ in that string.

SELECT LOCATE('world', 'hello world');

The search will return a result of 7, indicating that the substring ‘world’ first appears in ‘hello world’ at the 7th character.