how to strip a transaction ID out of a string of characters?
I am migrating data from one system to another and the return from PayPal, in the old system, is being saved into one large text field in the following format. I've changed some details to protect the customer.
{"paypal":{"mc_gross":"65.00","invoice":"de5a339d-ad63-d700-ae2f8b2e-2ed1e56b","protection_eligibility":"Eligible","address_status":"confirmed","payer_id":"PAYERIDHERE","tax":"0.00","address_street":"ADDRESSHERE","payment_date":"04:45:45 Mar 18, 2011 PDT","payment_status":"Completed","charset":"windows-1252","address_zip":"ZIPHERE","first_name":"Nathon","mc_fee":"2.41","address_country_code":"GB","address_name":"Nathon Jones","notify_version":"3.1","custom":"","payer_status":"verified","business":"info@nathonjones.com","address_country":"United Kingdom","address_city":"ADDRESSHERE","quantity":"1","payer_email":"info@nathonjones.com","verify_sign":"ACUe-E7Hjxmeel8FjYAtjnx-yjHAAUeeQSTCtgMMk39U1x4d7hFYcDP7","txn_id":"2MP34045EJ881612V","payment_type":"instant","last_name":"Jones","address_state":"Wigtownshire","receiver_email":"BUSINESSEMAILHERE","payment_fee":"","receiver_id":"Q5Z48PSZEPVC6","txn_type":"web_accept","item_name":"Medical certificate","mc_currency":"GBP","item_number":"1","residence_country":"GB","transaction_subject":"Medical certificate","handling_amount":"0.00","payment_gross":"","shipping":"0.00","merchant_return_link":"Return to Website"},"server":{"REDIRECT_STATUS":"200","HTTP_ACCEPT":"image\/jpeg, application\/x-ms-application, image\/gif, application\/xaml+xml, image\/pjpeg, application\/x-ms-xbap, application\/x-shockwave-flash, application\/vnd.ms-excel, application\/vnd.ms-powerpoint, application\/msword, *\/*","HTTP_ACCEPT_LANGUAGE":"en-GB","HTTP_USER_AGENT":"Mozilla\/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident\/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; OfficeLiveConnector.1.5; OfficeLivePatch.1.3; .NET4.0C; Tablet PC 2.0; InfoPath.2)","CONTENT_TYPE":"application\/x-www-form-urlencoded","HTTP_ACCEPT_ENCODING":"gzip, deflate","HTTP_HOST":"www.sportsmedicalcertificates.co.uk","CONTENT_LENGTH":"1040","HTTP_CONNECTION":"Keep-Alive","HTTP_CACHE_CONTROL":"no-cache","HTTP_COOKIE":"symfony=485f236d39a1bf48c680c7797640721d","PATH":"\/bin:\/usr\/bin:\/sbin:\/usr\/sbin:\/usr\/local\/bin:\/usr\/local\/sbin","SERVER_SIGNATURE":"","SERVER_SOFTWARE":"Apache","SERVER_NAME":"www.sportsmedicalcertificates.co.uk","SERVER_ADDR":"109.123.87.124","SERVER_PORT":"80","REMOTE_ADDR":"86.17.233.9","DOCUMENT_ROOT":"\/home\/medical\/public_html","SERVER_ADMIN":"webmaster@sportsmedicalcertificates.co.uk","SCRIPT_FILENAME":"\/home\/medical\/public_html\/index.php","REMOTE_PORT":"49711","REDIRECT_URL":"\/paypal\/success","GATEWAY_INTERFACE":"CGI\/1.1","SERVER_PROTOCOL":"HTTP\/1.1","REQUEST_METHOD":"POST","QUERY_STRING":"","REQUEST_URI":"\/paypal\/success","SCRIPT_NAME":"\/index.php","PHP_SELF":"\/index.php","REQUEST_TIME":1300448754,"argv":[],"argc":0}}
The new system has separate fields for lots of this data but, in particular, I need to recover the "txn_id".
Is there a script or query that would remove everything either side of "txn_id":"2MP34045EJ881612V" to simply leave 2MP34045EJ881612V?
Thank you.
Regards
NJ
