Re: Help with text(decimal) to hex conversion

Hi all,

The method suggested so far doesn't actually work.

I'm trying to convert a text datatype to hex. The maximum hex returned will be 20 octets. I'm hoping to do it within PostgreSQL so I can call it like a function (to_hex).

psql> select column from table limit 5;

---------------------

 4849018213204493635

 4939764883475860925

 1006304053701792827

 1007718049538635498

 1010517297675790156

(5 rows)

psql> select to_hex(column_name::bigint) from tablelimit 5;

      to_hex

------------------

 434b2c5fab740543

 448d91fd51870dbd

 df71c3488ee543b

 dfc223a187ff6ea

 e061420d75a674c

(5 rows)

I also tried the following suggestions.

psql> select encode(column::bytea,'hex') from table limit 1;

                 encode

----------------------------------------

 31303037373138303439353338363335343938

psql> select convert_from(decode('31303037373138303439353338363335343938', 'hex'), 'utf8');

    convert_from

---------------------

 1007718049538635498