Re: Help with text(decimal) to hex conversion

Поиск
Список
Период
Сортировка
От Wei Shan
Тема Re: Help with text(decimal) to hex conversion
Дата
Msg-id CAFe9ZTo=3vRUOOxWatWfg7k0JO5d_xLNiqQ+HNtceg-EqKA97Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Help with text(decimal) to hex conversion  (Merlin Moncure <mmoncure@gmail.com>)
Ответы Re: Help with text(decimal) to hex conversion  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Help with text(decimal) to hex conversion  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-novice
Дерево обсуждения
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

В списке pgsql-novice по дате отправления:

Предыдущее
От: Paul Linehan
Дата:
Сообщение: Re: Tricky SQL problem - retrieve information_schema info and make use of it.
Следующее
От: Paul Linehan
Дата:
Сообщение: Re: Tricky SQL problem - retrieve information_schema info and make use of it.