Re: Displaying text appears as hex data

Hello Thom,

On Tues., Feb 08, 2011, Thom Brown wrote:
>On 8 February 2011 13:43, Thom Brown wrote:
>> On 8 February 2011 13:19, Michael wrote:
>>> On Tues., Feb 08, 2011, Thom Brown wrote:
>>>>On 8 February 2011 12:45, Michael wrote:
>>>>> On Tues., Feb 08, 2011, Thom Brown wrote:
>>>>>>On 8 February 2011 10:39, Michael wrote:
>>>>>>> opensips=> select * from sip_trace;
>>>>>>>  id | time_stamp | callid | traced_user | msg | method | ...
>>>>>>>  1234 | 2011-02-03 | ... | | \x494e56495445207369703a...
>>>>>>>
>>>>>>> Others have said that when they use MySQL, the exact SQL command
>>>>>>> as above results in ASCII text rather than hexadecimal, and this
>>>>>>> is my goal as well.
>>>>>>>
>SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea,
>'SQL_ASCII'::name), method
>FROM sip_trace;
>
opensips=> SELECT id, time_stamp, callid, traced_user, \
convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace;
id | time_stamp | callid | traced_user | convert_from | method
-------+---------------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
30772 | 2011-02-08 15:04:34 | ab19ccbd8120-z92f7ean9o9c | | INVITE sip:num(at)name(dot)host(dot)tld;user=phone SIP/2.0\r +| INVITE
| | | | Via: SIP/2.0/TLS 192.168.100.123:1234;branch=asd84bK-rr8scd0jteop;rport\r +|
| | | | From: "Username Person One" <sip:Proxyuser(at)name(dot)host(dot)tld>;tag=thefromtag12\r +|

There's a ton of blanks after '\r' the carriage returns. Is there
any way to construct a SELECT statement using filters or something
that will neatly format the msg field?

Is there some stream editor similar function?

SELECT delwhitespace(s/convert_from(msg::bytea, 'SQL_ASCII'::name))

Sorry to be pesky, since you already answered the original question.
Thanks again for doing that.

Regards,
Michael