Hive padding leading zeroes

sql hive bigdata
Muthu Palaniappan picture Muthu Palaniappan · Jul 28, 2014 · Viewed 29.6k times · Source

I need the output of a string column in my table as 13 length char, irrespective of whatever length it is, i need to stuff the remaining chars with 0...

I tried to use the following code in my hive query, but failed to get the desired output

right('0000000000000' + ProductID, 13)

Any help? Thanks

Answer

Jason Rosendale picture Jason Rosendale · Jul 28, 2014

Hive has built-in lpad and rpad functions. In your case you could use:

lpad(ProductId, 13, "0")

Or, if you might need to truncate to 13 characters, you could wrap this in the "right" function.

Related questions

How to get array/bag of elements from Hive group by operator?

I want to group by a given field and get the output with grouped fields. Below is an example of what I am trying to achieve:- Imagine a table named 'sample_table' with two columns as below:- F1 …

Read More
Error Message: TOK_ALLCOLREF is not supported in current context - while Using DISTINCT in HIVE

I'm using the simple command: SELECT DISTINCT * FROM first_working_table; in HIVE 0.11, and I'm receiving the following error message: FAILED: SemanticException TOK_ALLCOLREF is not supported in current context. Does anyone know why this is happening? How can we …

Read More
Hive insert query like SQL

I am new to hive, and want to know if there is anyway to insert data into hive table like we do in SQL. I want to insert my data into hive like INSERT INTO tablename VALUES (value1,value2..) I …

Read More