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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s