Home > mailing lists

Thread: Help with text(decimal) to hex conversion

Help with text(decimal) to hex conversion

From

Wei Shan

Date:

21 March 2016, 13:29:56

Hi all,

There's a column that has datatype of text. The conversion will work fine for some of the rows.

psql> select to_hex(data::bigint)from table limit 5;

to_hex

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

499602d2

4fa83d1136d920ef

2e1b71785c8e11c

53ff4c2824860fb8

However, for some records, the original data is too large for casting.

ERROR: value "14481874327766585215" is out of range for type bigint

Any idea how to overcome this? to_hex function only accepts int or bigint.

Thanks!

Regards,
Ang Wei Shan

Re: Help with text(decimal) to hex conversion

From

Gavin Flower

Date:

21 March 2016, 23:52:49

On 21/03/16 21:30, Wei Shan wrote:
> Hi all,
>
> There's a column that has datatype of text. The conversion will work
> fine for some of the rows.
>
> /psql> select to_hex(data::bigint)from table limit 5;/
> /      to_hex/
> /------------------/
> / 499602d2/
> / 499602d2/
> / 4fa83d1136d920ef/
> / 2e1b71785c8e11c/
> / 53ff4c2824860fb8/
>
>
> However, for some records, the original data is too large for casting.
>
> /ERROR:  value "14481874327766585215" is out of range for type bigint/
>
> Any idea how to overcome this? to_hex function only accepts int or bigint.
>
> Thanks!
> --
> Regards,
> Ang Wei Shan
If feasible to use Java, then you could use the BigInteger class to
convert the Hex string into a number, see:

https://docs.oracle.com/javase/8/docs/api/java/math/BigInteger.html

BigInteger bigInteger = new BigInteger(hexString, 16);


Cheers,
Gavin

Re: Help with text(decimal) to hex conversion

From

Szymon Lipiński

Date:

22 March 2016, 00:05:16

On 21 March 2016 at 09:30, Wei Shan <weishan.ang@gmail.com> wrote:

Hi all,

There's a column that has datatype of text. The conversion will work fine for some of the rows.

psql> select to_hex(data::bigint)from table limit 5;
to_hex
------------------
499602d2
499602d2
4fa83d1136d920ef
2e1b71785c8e11c
53ff4c2824860fb8

However, for some records, the original data is too large for casting.

ERROR: value "14481874327766585215" is out of range for type bigint

Any idea how to overcome this? to_hex function only accepts int or bigint.

Thanks!
--
Regards,
Ang Wei Shan

Hi,

try this function:

create or replace function text_to_hex(t text) returns text as $$

return hex(int('14481874327766585215'))[2:-1]

$$ language plpythonu;

select text_to_hex('14481874327766585215');

text_to_hex

──────────────────

c8f9f3f90c604f7f

(1 row)

regards Szymon Lipiński

Re: Help with text(decimal) to hex conversion

From

Merlin Moncure

Date:

22 March 2016, 01:44:31

On Mon, Mar 21, 2016 at 3:30 AM, Wei Shan <weishan.ang@gmail.com> wrote:
> Hi all,
>
> There's a column that has datatype of text. The conversion will work fine
> for some of the rows.
>
> psql> select to_hex(data::bigint)from table limit 5;
>       to_hex
> ------------------
>  499602d2
>  499602d2
>  4fa83d1136d920ef
>  2e1b71785c8e11c
>  53ff4c2824860fb8
>
>
> However, for some records, the original data is too large for casting.
>
> ERROR:  value "14481874327766585215" is out of range for type bigint
>
> Any idea how to overcome this? to_hex function only accepts int or bigint.

postgres=# select encode('foo', 'hex');
 encode
────────
 666f6f
(1 row)

postgres=# select convert_from(decode('666f6f', 'hex'), 'utf8');
 convert_from
──────────────
 foo
(1 row)

merlin

Re: Help with text(decimal) to hex conversion

From

Wei Shan

Date:

22 March 2016, 21:38:56

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

Thanks!

On 22 March 2016 at 06:44, Merlin Moncure <mmoncure@gmail.com> wrote:

On Mon, Mar 21, 2016 at 3:30 AM, Wei Shan <weishan.ang@gmail.com> wrote:
> Hi all,
>
> There's a column that has datatype of text. The conversion will work fine
> for some of the rows.
>
> psql> select to_hex(data::bigint)from table limit 5;
> to_hex
> ------------------
> 499602d2
> 499602d2
> 4fa83d1136d920ef
> 2e1b71785c8e11c
> 53ff4c2824860fb8
>
>
> However, for some records, the original data is too large for casting.
>
> ERROR: value "14481874327766585215" is out of range for type bigint
>
> Any idea how to overcome this? to_hex function only accepts int or bigint.

postgres=# select encode('foo', 'hex');
encode
────────
666f6f
(1 row)

postgres=# select convert_from(decode('666f6f', 'hex'), 'utf8');
convert_from
──────────────
foo
(1 row)

merlin

Regards,
Ang Wei Shan

Re: Help with text(decimal) to hex conversion

From

"David G. Johnston"

Date:

23 March 2016, 01:10:48

Please don't top-post.

On Tue, Mar 22, 2016 at 11:38 AM, Wei Shan <weishan.ang@gmail.com> wrote:

Hi all,

The method suggested so far doesn't actually work.

What "method"(s) have you tried?- all you show below are "to_hex(...::bigint)"

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).

PostgreSQL, from what I can tell, cannot natively (i.e, in SQL or pl/pgsql) support any number larger than bigint's maximum value. If you need to handle something larger you must keep the representation as a string and provide that string to a programming language number library that can. Supposedly both Java and Python can.

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)

column != column_name ... and again you must not cast to bigint.

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

This is Row #4 in your data...but you are simply encoding the textual representation of something that looks like a number, then decoding it again.

SELECT encode('16'::bytea, 'hex'); a change of numeric base would give you "F", not "3136"

David J.

Re: Help with text(decimal) to hex conversion

From

Merlin Moncure

Date:

23 March 2016, 03:44:03

On Tuesday, March 22, 2016, Wei Shan <weishan.ang@gmail.com> wrote:

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

Why did you cast to bytea? Remove that.

merlin