Hive instr function

Instr is a string function which returns the position of the first occurrence of substring in the input string.

Instr syntax in Hive

This function takes two arguments. The first argument is the input string value. The second argument denotes the sub string which we want to search on the input string.

• If any of the argument is null , it will return the output as NULL.
• It returns 0 if the substring value could not be found in the input string value.
• Also note that the index of the string is not zero based. The first character in string has index 1. Based on this, It will return the position value of the substring.

To search the sub string in another string value, both the arguments should be in the String format. We can give either string value or the column name of the table in the instr function.

Example 1 : Get the position of the sub string

We have a customer_details table in Hive. The table contains the customer id, name and email_address.

Lets find the position of ‘@’ string from the email_address column using Instr function

As we mentioned that the string index is starts from 1, the instr function returned the position of ‘@’ string as below.

Example 2 : Use instr value as starting position in substring

Since the instr function returns the position of the string, it can be used in substring function. Let’s write a query to extract domain name from the email_address.

Instr function is mentioned inside the substring function. It returns the position of ‘@’ character from the email_address. Then the instr return value is considered as starting position of the string for substring function.

Hence substring function returns the string value from ‘@’ character. Since we didn’t give the length value in substring function, it returns the remaining characters from the starting position.

Remove character @ from domain name

In order to remove the starting character ‘@’ from the domain name, we need to increase the starting position. Lets add + 1 in the instr function to get the required output

Example 3 : Use instr value as length in substring

In previous example, we have taken the domain name from the email address. Now we want to extract only the user name from email address.

For this, starting position of the sub string is 1. Then the instr return value can be used as length of the sub string. Let’s write a query to get user name.

We got the user name with @ character. Since the instr function returned the position of ‘@’ character, it is considered as length of the substring.

In order to remove the ‘@’ character from the user name, we need to decrease the instr return value. Lets use -1 with instr function to get the required output.

Since we subtracted the position of ‘@’ character with -1 ,we got the user name without ‘@’ character. For these example, we have used single character in the instr function. But we can give the string value with more than one character also.

Example 4 : Instr return value if the string is not found

Incase the given string value is not found in the input string, the instr function will return the 0. Lets use ‘$’ in the instr function.

Example 5 : Instr returns null if input string is null

If the input string is null when we use instr function, it will also return the null value as below.

Example 6 : Instr function with relational operator

Since instr function returns the integer value , it can be validated by relational operators such as >,<,=,>=,<=.

Also the instr function can be used with conditional statements such as case and if.

Recommended Articles

• String functions in Hive with examples