Hello Thom, On Tues., Feb 08, 2011, Thom Brown wrote: >On 8 February 2011 14:30, Michael <postgresql@xxxxxxxxxxxx> wrote: >> 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@xxxxxxxxxxxxx;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@xxxxxxxxxxxxx>;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. >> >Well, you could always try: > >SELECT translate(convert_from(msg::bytea, 'SQL_ASCII'::name), E'\r\n', ''); > Thanks alot that really helps, I'll figure the rest out from here. Regards, Michael -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
