INSTR function in Apache Hive helps in finding the position of a substring in a string. It returns only the first first occurrence of the given input.
Returns null if either of the arguments are null and returns 0 if the substring could not be found in the string. By default, the first character in string has index 1.
Look at the following examples.
SELECT INSTR(‘This is posted on instagram’, ‘post’);
This returns 9 as the substring ‘post’ starts at 9th position.
SELECT INSTR(‘This is posted on instagram’, ‘i’);
This returns 3 as the given string ‘i’ found at 3rd position. Unlike in Oracle SQL, it accepts only two parameters in Hive and searches for the first occurrence only.
SELECT INSTR(‘This is posted on instagram’,’twitter’);
This returns 0 as the search keyword ‘twitter’ could not be found in the string.
Hope you liked this post.
Click on follow button to get updates on latest posts.
Big Data and SQL 
4 comments