Substring_Index Function in Apache Hive

The aim of this post is to let you know how to use the SUBSTRING INDEX() function to get a substring from a string until the delimiter occurs.

Anything to the left of the final delimiter (counting from the left) is returned when the count is positive. If the count is negative, it returns everything to the right of the final delimiter (the count from the right).

Syntax: substring_index(string A, string delim, int count)

Examples:
SELECT SUBSTRING_INDEX(“www.big.data.n.sql.com”, “.”, 1);
Results: www
SELECT SUBSTRING_INDEX(“www.big.data.n.sql.com”, “.”, 2);
Results: http://www.big
SELECT SUBSTRING_INDEX(“www.big.data.n.sql.com”, “.”, 3);
Results: http://www.big.data
SELECT SUBSTRING_INDEX(“www.big.data.n.sql.com”, “.”, 4);
Results: http://www.big.data.n
SELECT SUBSTRING_INDEX(“www.big.data.n.sql.com”, “.”, 5);
Results: http://www.big.data.n.sql
SELECT SUBSTRING_INDEX(“www.big.data.n.sql.com”, “.”, 6);
Results: http://www.big.data.n.sql.com

Hope you find this article helpful.

Do follow for more interesting updates.