Converting long to string and to convert the unix dates in the string to regular dates

I have a field that’s of type long and it stores text in that field. The sample text is like this

959412525_mfer-sanj-ttss_Assigned Group: San Jose CA-CSSR Status changed to: Assigned Priority changed to: Minor_959412633_mfer-sanj- ttss_Sub reported to CSSR Paul that he is once again getting NDT all lines again.......he has our cell phone in his possession......REOPENED  TICKET #114625.....NDT PLEASE RE-INVESTIGATE.......thank you///Melissa-243 Assigned Group: Broadband NNOC-Tel TRAC_959412944_wsta-bbnn-ttrc_Status changed to: Working_959412954_wsta-  -ttrc_Assigned Person: Staats, Whitney_959418400_wsta-bbnn- ttrc_Checked in ACSR WO 113406 and remaining queues are not service affecting. VP State: IS Trbl Comms-Down. Cannot trip the LC. Checked in the switch and the provisioning is correct. Please assign a tech to investigate. Thanks Assigned Group: Northbay CA-Dispatch Status changed to: Assigned_959419446_sboy-dldb-disp_Please assign next available tech 5/27. Thanks Sharon_959441989_jmim-dldb-disp_willl assign premise tech***jm_959443576_jmim-dldb-disp_assigned to tech 5454**jm_959447358_jmim-dldb-disp_Assigned Person: Mims, Janie Status changed to: Working_959476518_jmim-dldb-disp_spoke with tech 5454 the original install was connected to a switched outlet which was causing it to short and die out 5:00-5:40***jm_959476684_jmim-dldb- disp_Assigned Group: San Jose CA-CSSR Status changed to: Resolved_959517504_mfer-sanj-ttss_viewed......closing per above verification/resolution per dispatch//Melissa-243 Status changed to: Closed_

The numbers are somehow stored surrounded with chr(3) or some non printable characters except for the beginning one. And these numbers are actually unix times (time since 1/1/1970).

So 1. Was the text in this field put in through some function that converted the string to long.

    2. How can I get the string back from this field with date in normal format that is January 1, 2000 12:30:00 A.M

    3. Is there any pattern replacement functions so that I can first find the chr(3) and replace with ascii character and then replace the numbers with dates. I would like to get the string through a single select statement if possible

Thanks for your help

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jun 24 2000 - 00:00:00 CDT