[HIVE-3558] UDF LEFT(string,position) to HIVE

• Type: New Feature
• Status: Resolved
• Priority: Minor
• Resolution: Won't Fix
• Affects Version/s: 0.9.0
• Component/s: UDF
• Assignee: Unassigned
• Reporter: Aruna Babu
• Created: 09/Oct/12 11:51
• Updated: 08/Feb/17 05:45
• Resolved: 08/Feb/17 05:45

Description

Introduction

UDF (User Defined Function) to obtain the left most 'n' characters from a string in HIVE.

Relevance

Current releases of Hive lacks a function which would returns the leftmost len characters from the string str, or NULL if any argument is NULL.
The function LEFT(string,length) would return the leftmost 'n' characters from the string , or NULL if any argument is NULL which would be useful while using HiveQL. This would find its use in all the technical aspects where the concept of strings are used.

Functionality :-

Function Name: LEFT(string,length)

Returns the leftmost length characters from the string or NULL if any argument is NULL.
Example: hive>SELECT LEFT('https://www.irctc.co.in',5);
-> 'https'

Usage :-
Case 1: To query a table to find details based on an https request
Table :-Transaction
Request_id|date|period_id|url_name

0001|01/07/2012|110001|https://www.irctc.co.in

0002|02/07/2012|110001|https://nextstep.tcs.com

0003|03/07/2012|110001|https://www.hdfcbank.com

0005|01/07/2012|110001|http://www.lmnm.co.in

0006|08/07/2012|110001|http://nextstart.com

0007|10/07/2012|110001|https://netbanking.icicibank.com

0012|21/07/2012|110001|http://www.people.co.in

0026|08/07/2012|110001|http://nextprobs.com

00023|25/07/2012|110001|https://netbanking.canarabank.com

Query : select * from transaction where LEFT(url_name,5)='https';

Result :-
0001|01/07/2012|110001|https://www.irctc.com
0002|02/07/2012|110001|https://nextstep.tcs.com
0003|03/07/2012|110001|https://www.hdfcbank.com
0007|10/07/2012|110001|https://netbanking.icicibank.com
00023|25/07/2012|110001|https://netbanking.canarabank.com