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
Convert Hex to decimal on table column in vertica
Moderator: NorbertKrupa
-
- Newbie
- Posts: 1
- Joined: Wed Jul 22, 2015 4:36 pm
Re: Convert Hex to decimal on table column in vertica
Hi!
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").
Your math is awesome:
PS
Best runtime performance strategy(but bad implementation in general - updated column should be INT):
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)
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?santoshv82 wrote:Can you suggest the best way to achieve this?
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").
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.santoshv82 wrote:and updated in the same column in the table.
Your math is awesome:
Code: Select all
For me: HEX(1050694586) = 03EA053BA
For you: HEX(1050694586) = 03EA053BAF20
...
interesting
Can you explain how to convert HEX to DECIMAL. For example - what is HEX value for 1.02?santoshv82 wrote:I need to convert a string field from HEX to Decimal value in vertica DB.
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)