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

Can't this be shortened to:

aklaver@test=> select convert_to( 'é', 'latin1');
  convert_to
------------
  \xe9
(1 row)

aklaver@test=> select convert_from( '\xe9', 'latin1');
  convert_from
--------------
  é

since convert_to() outputs bytea and convert_from() accepts 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 ).

If you are doing all this in the same database I am not sure how the
above applies?

Would you not just use the database encoding for the src_encoding?

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