Re: encode, lower and 0x8a

From: "Michael Artz" <mlartz(at)gmail(dot)com>
To: "Michael Fuhr" <mike(at)fuhr(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: encode, lower and 0x8a
Date: 2007-01-29 17:52:33
Message-ID: e9c163070701290952k7add2d0y971228720fedd85c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 1/27/07, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> On Thu, Jan 25, 2007 at 02:28:38PM -0500, Michael Artz wrote:
> > Perhaps my understanding of the 'encode' function is incorrect, but I
> > was under the impression that I could do something like:
> >
> > SELECT lower(encode(bytes, 'escape')) FROM mytable;
> >
> > as it sounded like (from the manual) that 'encode' would return valid
> > ASCII, with all the non-ascii bytes hex escaped.
...snip...
> > I have the sneaking suspicion that I am missing something, so please
> > correct me if I am wrong. If I am wrong, is there a better way to
> > lowercase all the ascii characters in a bytea string?
>
> What are you trying to do? What is the binary data and why are you
> treating it (or part of it) as though it's text? Do you want the
> end result to be text with escape sequences or do you want to convert
> it back to bytea?

The data are binary strings culled from network traffic. Most of the
"binary strings" are just ascii strings, however not all of them are,
and some of the ascii strings have binary characters embedded in them.
In this case, I was displaying the string to a user via a web
browser, in which case I wanted the output to be escaped. The reason
for the 'lower()' was to enable the case-insensitive sort, so actually
the offending line of the program would look something more like:

SELECT encode(bytes, 'escape') FROM mytables ORDER BY
lower(encode(bytes, 'escape'))

> Something like this might work:
>
> SELECT lower(textin(byteaout(bytes))) FROM mytable;
>
> To turn the result back into bytea:
>
> SELECT decode(lower(textin(byteaout(bytes))), 'escape') FROM mytable;

That seems to work correctly, however I missed the functions textin'
and 'byteaout' in the docs ... are they documented somewhere?

Thanks,
-Mike

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2007-01-29 17:53:47 Re: Limit on number of users in postgresql?
Previous Message Tino Wildenhain 2007-01-29 16:59:33 Re: PG Email Client