On 1/27/07, Michael Fuhr <mike@xxxxxxxx> 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
