Convert Hex to decimal on table column in vertica

Moderator: NorbertKrupa

Post Reply
santoshv82
Newbie
Newbie
Posts: 1
Joined: Wed Jul 22, 2015 4:36 pm

Convert Hex to decimal on table column in vertica

Post by santoshv82 » Wed Jul 22, 2015 5:37 pm

Hi Experts,

I need to convert a string field from HEX to Decimal value in vertica DB.
The length of the number is about '03EA053BAF20' which needs to be converted to '1050694586' and updated in the same column in the table.
I need to do this for a table with data about 100M.

Can you suggest the best way to achieve this?

Regards

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Convert Hex to decimal on table column in vertica

Post by id10t » Wed Jul 22, 2015 8:12 pm

Hi!
santoshv82 wrote:Can you suggest the best way to achieve this?
Can you explain meaning of "best way"? What is it? I don't know. Is it Performance/runtime? Or implementation time? Or complexity? Or price(for proprietary solution)? If you can not define your problem, so you do not understand it. What is your limitations? What is your requirements?

If exists "THE BEST WAY", so it will be "THE ONLY WAY" and no other ways - go on it, use it(exists only ONE way - "THE BEST ONE").

santoshv82 wrote:and updated in the same column in the table.
Bad idea, for best performance you should define updated column as some NUMERIC data type(hex is VARCHAR) and so projections should be modified too.


Your math is awesome:

Code: Select all

For  me: HEX(1050694586) = 03EA053BA
For you: HEX(1050694586) = 03EA053BAF20
...
interesting
santoshv82 wrote:I need to convert a string field from HEX to Decimal value in vertica DB.
Can you explain how to convert HEX to DECIMAL. For example - what is HEX value for 1.02?
A decimal may be a terminating decimal, which has a finite fractional part (e.g. 15.600); a repeating decimal, which has an infinite (non-terminating) fractional part made up of a repeating sequence of digits (e.g. 5.8144); or an infinite decimal, which has a fractional part that neither terminates nor has an infinitely repeating pattern (e.g. 3.14159265...). Decimal fractions have terminating decimal representations, whereas irrational numbers have infinite decimal representations.
---
PS
Best runtime performance strategy(but bad implementation in general - updated column should be INT):
  • CREATE TABLE <stg> LIKE <orig> INCLUDING PROJECTIONS;
  • INSERT /*+direct*/ INTO <stg> SELECT ..., HEX_TO_INTEGER(hex_col) FROM <orig>;
  • DROP TABLE <orig>;
  • ALTER TABLE <stg> RENAME TO <orig>;
  • profit;
Example:
[/b]
daniel=> select * from orig;
h
-----------
03EA053BA
(1 row)


daniel=> create table stg like orig including projections;
CREATE TABLE


daniel=> insert /*+direct*/ into stg(h) select hex_to_integer(h) from orig;
OUTPUT
--------
1
(1 row)


daniel=> drop table orig;
DROP TABLE


daniel=> alter table stg rename to orig;
ALTER TABLE


daniel=> select * from orig;
h
------------
1050694586
(1 row)

Post Reply

Return to “Vertica SQL Functions”