On 8 February 2011 14:30, Michael <postgresql@xxxxxxxxxxxx> wrote: > > 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@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', ''); -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
