EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Hive Tutorial Hive String Functions
Secondary Sidebar
Hive Tutorial
  • Basics
    • Hive JDBC Driver
    • What is a Hive
    • Hive Architecture
    • Hive Installation
    • How To Install Hive
    • Hive Versions
    • Hive Commands
    • Hive Data Types
    • Hive Built-in Functions
    • Hive Function
    • Hive String Functions
    • Date Functions in Hive
    • Hive Table
    • Hive Drop Table
    • Hive Show Tables
    • Hive Group By
    • Hive Order By
    • Hive Cluster By
    • Joins in Hive
    • Hive Inner Join
    • Map Join in Hive
    • Hive nvl
    • Hive UDF
    • Dynamic Partitioning in Hive
    • HiveQL
    • HiveQL Queries
    • HiveQL Group By
    • Partitioning in Hive
    • Bucketing in Hive
    • Views in Hive
    • Indexes in Hive
    • External Table in Hive
    • Hive TimeStamp
    • Hive Database
    • Hive Interview Questions
    • Hive insert into

Related Courses

Hive Certification Course

Hadoop Course Training

All in One Data Science Course

Hive String Functions

By Swati TawdeSwati Tawde

Hive String Function

Introduction to Hive String Function

The string is a sequence of characters. In hive, string functions are used to perform different operations like reversing sting, converting into upper and lower case, removing spaces, etc. Hive string functions look like SQL string functions. Let us look at those string functions in detail to understand the concept easily.

Hive String Functions

Below are some hive string functions with some examples.

1. Repeat

This hive String function is used to repeat given string with N number of time.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Repeat(string str, n);

e.g:

Repeat('Apple',3);

Output: Apple Apple Apple

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,354 ratings)

2. Reverse

This Reverse function will reverse the given string input and gives the result, as shown below.

Syntax:

Reverse(string str);

E.g.:

ReverseE('Apple');

Output: elppA

3. Trim

This Trim function is used to remove all the spaces from the string and gives new string with no spaces.

Syntax:

Trim(string str);

E.g.:

Trim('Apple');

Output:

Apple

4. Rtrim

This Rtrim function is used to remove all the spaces from the right-hand side of the string and gives new string.

Syntax:

RTRIM(string str);

E.g.:

RTRIM('Apple');

Output:

Apple

5. LTRIM

This LTRIM function is used to remove all the spaces from the left-hand side of the string and gives new string.

Syntax:

LTRIM(stringstr);

E.g.:

LTRIM('Apple');

Output:

Apple

6. LOWER or LCASE

This string function is used to converts all the characters of the given string into the lower letter case.

Syntax:

LOWER(string str);or LCASE(string str);

E.g.:

LOWER('ApPle'); or LCASE('ApPle');

Output:

apple

7. UPPER or UCASE

This UPPER function is used to convert all the characters of the given string into the Upper Letter Case.

Syntax:

UPPER(string str);or UCASE(string str);

E.g.:

UPPER('apple'); or UCASE('apple');

Output:

APPLE

8. CONCAT

All the strings are integrated with the CONCAT Function.

Syntax:

CONCAT(stringstr1,stringstr2,stringstr3….)

E.g.:

CONCAT( 'Apple' , '-' , 'eat' );

Output:

‘Apple-eat’

9. CONCAT_WS

CONCAT function and CONCAT_WS function, both have the same properties. In this CONCAT_WS  function, you can provide delimiter which can be used in between the strings to CONCAT

E.g.:

CONCAT_WS( '-' , 'apple' , 'eat' );

Output:

‘apple-eat’

10. LENGTH

This LENGTH function is used to get the number of characters that are present in the given string.

Syntax:

LENGTH(string str);

E.g.:

LENGTH( 'Apple' );

Output:

5

11. SPACE

This SPACE function is used to give the specified number of Space in string

Syntax:

SPACE(intnum_of_spaces);

E.g.:

SPACE(5);

Output:

‘     ‘

12. SPLIT

The SPLIT function splits the string around the pattern pat and returns an array of strings. You can specify regular expressions as patterns.

Syntax:

SPLIT(string str, string pat)

Example:

SPLIT('hive:Hadoop',':'

Output:

[“hive”,”Hadoop”]

13. LPAD

The LPAD function is used to give the string with a length of line characters left-padded with pad character.

Syntax:

LPAD(string str,int len,string pad)

E.g:

LPAD(‘Hadoop’,8,’H’);

Output:

hhhadoop

14. RPAD

The RPAD function is used to give the string with a length of len characters right-padded with pad character.

Syntax:

RPAD(string str, int len, string pad)

Output:

hadooppp

15. INSTRING

This function is used to give the position of the first occurrence of a substring in str. If one of the arguments of the string is null then it gives NULL as an output. If sub str is missing or cannot be found in str then it will return 0. The index value of the first character in str is 1.

Syntax:

INSTR(string str, string substring);

E.g:

INSTR('Rachel','el');

Output:

4

16. Locate

This function is used to give the position of the first occurrence of a substring in the string after position pos.

Syntax:

LOCATE(string substring, string str[,int pos]);

E.g:

LOCATE('el','Rachel',2);

Output:

4

17. Translate

This function is used to translate the characters of a given string with corresponding characters mentioned in the to string. If any argument is NULL in a given string, then it will give NULL as an output.

Syntax:

TRANSLATE(string|char|varchar input, string|char|varchar from, string|char|varchar to);

E.g:

TRANSLATE('Make sure u knew that code','e','o');

Output:

Mako, suro u know that codo

18. ASCII(string str);

The ASCII function is used to give the ASCII value of the first character of a given string.

Example1:

ASCII('hadoop') ;

Output:

104

Example2:

ASCII('A') ;

Output:

65

19. PRINTF

Returns the input format according to PRINTF style format strings.

Syntax:

PRINTF(Obj...args);

20. regxp_replace

Syntax:

regxp_replace(string A, string B, String C)

This function returns the string which is the output of replacing all substrings in B which matches the Java regular syntax with C.

21. From_unix time

Syntax:

from_unixtime(int Unix time)

This function converts the number of seconds from the Unix epoch to a string representing the timestamp of the current system time zone. for example, a timestamp is in the format as follows (2019-01-01 00:00:00 UTC) then this function will convert it to the format of “2019-01-01 00:00:00”

E.g:

from_unixtime(int 2011-01-06 12:00:00 UTC)

Output:

2011-01-06 12:00:00

22. get_json_object

Syntax:

get_json_object(string json_string, string path)

Based on the specified JSON path, this function extracts the JSON object from a JSON string and returns a JSON string of the extracted JSON object. If JSON strings invalid then it will return NULL.

23. To_Date

Syntax:

to_date(string timestamp)

This function will return the date part of a timestamp string.

E.g:

to_date("2019-03-04 00:04:12)

Output:

2019-03-04

Recommended Articles

This has been a guide to Hive String Functions. Here we discussed the basic concept and various string functions used in Hive with example and output. You can also go through our other Suggested Articles to learn more –

  1. PostgreSQL String Functions
  2. Hive Interview Questions
  3. JavaScript String Functions
  4. Examples Of VB.Net String Functions
  5. How To Use VBScript String Functions
  6. Components of Hive Architecture
  7. Hive Order By
  8. Hive Installation
Popular Course in this category
Hadoop Training Program (20 Courses, 14+ Projects, 4 Quizzes)
  20 Online Courses |  14 Hands-on Projects |  135+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Hive Training (2 Courses, 5+ Projects)4.9
All in One Data Science Bundle (360+ Courses, 50+ projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more