Re: Convert from hex to string - Mailing list pgsql-general

On 11/25/2015 08:56 AM, Francisco Olarte wrote:
> Hello Yuriy...
>
> On Wed, Nov 25, 2015 at 4:47 PM, Yuriy Rusinov <yrusinov@gmail.com> wrote:
>> I have to transform string, encoded to hexadecimal to text, but if I try to
>> select encode('Qt is great!', 'hex'); I receive valid and correct results
>>
>> 517420697320677265617421
>>
>> but if I try to select decode ('517420697320677265617421', 'hex'), I
>> receive the same string, such as
>> '\x517420697320677265617421, which way I have to do for valid convert
>> to/from hexadecimal ?
>
> I seem to recall having answered this a very short time ago, but maybe
> it was in the spanish list.
>
> decode/encode are for converting bytes to a string. You need to
> convert the string to bytes in a controlled way first ( bear in mind
> there are implicit conversions ).
>
> What you want is, given a text:
>
> 1.- Convert it to a bytea, in a controlled encoding: convert_to(string
> text, dest_encoding name) => bytea
> 2.- Then encode the bytes in hex: encode(data bytea, format text) => text
>
> then, to revert it you:
>
> 3.- Decode the hex string to bytes: decode(string text, format text) => bytea
> 4.- Convert the bytea, in a controlled encoding, to text:
> convert_from(string bytea, src_encoding name) => text
>
> As you see, they are nicelly paired. I see another response which just
> does encode , decode+convert_from. This works because the database
> does implicit conversions, but I would not recomend it. I cannot try
> it because all my databases are UTF-8 but I feel Adrians example would
> not work if your database encoding is NOT UTF-8 ( and you use any char
> outside of ascii range ).

To follow up:

postgres@postgres=# CREATE DATABASE latin1_db ENCODING 'LATIN1' LC_CTYPE
'C' LC_COLLATE 'C' TEMPLATE template0;
CREATE DATABASE

postgres@latin1_db=# \l latin1_db
                            List of databases
    Name    |  Owner   | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-------------------
  latin1_db | postgres | LATIN1   | C       | C     |


postgres@postgres=# \c latin1_db
You are now connected to database "latin1_db" as user "postgres".

postgres@latin1_db=# \encoding
UTF8

postgres@latin1_db=# select encode('é', 'hex');
  encode
--------
  e9
(1 row)

postgres@latin1_db=# select decode('e9', 'hex');
  decode
--------
  \xe9
(1 row)

postgres@latin1_db=# select convert_from('\xe9', 'latin1');
  convert_from
--------------
  é
(1 row)

>
> Look at the docs of the functions, section 9.4 table 9.7 int the 9.4.5 manual.
>
> If you do it this way, you can also choose the encoding, ie, if you
> know your data is latin1 you can convert from/to it and save a few
> bytes, or you can convert to/from utf8 an insure you can represent
> anything. Then you can encode/decode the bytes in whatever sutis you,
> hex, as in yuour eample or base64 if you need to save a few bytes.
>
> Types are there for a reason.
>
> Francisco Olarte.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com