I'm sanitising personal data in a large database with a series of Regular Expressions. In many cases the sensitive data is in a colon-delimited field within a CSV file.Here is a sample:(142406, 202, '45759:2940:2016-04-12-21-24-08:300:2016-06-21:2016-04-11:1:Windows/Doors:', 2761, 4041931, 0, '1460492649'),(142404, 203, '43833:1:1:1:1:4:1:1:2016-03-24', 1310, 4041377, 0, '1460491641'),(142405, 204, '45759:Mr.:Bob:Thingy:123 Lovely Avenue:Anice Town:Acounty:TT1 1TT:01234123123:bob@example.com:2016-04-12-21-24-08', 2761, 4041931, 0, '1460492649'),(142391, 203, '45462:1:1:1:1:5:0:1:2016-04-06', 1441, 4042573, 0, '1460477197'),(142392, 204, '45755:Mrs.:Sandra:Wotsits:456 Pleasant Street:Agreat City:Anothercounty:TT2 2TT:01234456456:sandra@example.com:2016-04-12-17-08-13', 2869, 4030970, 0, '1460477293'),This is what I need to, and do, end up with:(142406, 202, '45759:2940:2016-04-12-21-24-08:300:2016-06-21:2016-04-11:1:Windows/Doors:', 2761, 4041931, 0, '1460492649'),(142404, 203, '43833:1:1:1:1:4:1:1:2016-03-24', 1310, 4041377, 0, '1460491641'),(142405, 204, '45759:Mr.:Dennis:Chalkley:123 Street:Atown:Acounty:TE5 5TT:01234123456:name@example.com:2016-04-12-21-24-08', 2761, 4041931, 0, '1460492649'),(142391, 203, '45462:1:1:1:1:5:0:1:2016-04-06', 1441, 4042573, 0, '1460477197'),(142392, 204, '45755:Mr.:Steven P:Kirk:123 Street:Atown:Acounty:TE5 5TT:01234123456:name@example.com:2016-04-12-17-08-13', 2869, 4030970, 0, '1460477293'),Note that the only things being replaced are the address details and the email address, and these only appear in lines with '204' in the second column. All other data needs to remain as is.I'm using these to achieve the above result:Search for:204, '([0-9]{5}):(.*?):(.*?):(.*?):(.*):(.*)',Replace with:204, '\1:\2:\3:\4:123 Street:Atown:Acounty:TE5 5TT:01234123456:name@example.com:\6',I'm PRETTY sure my 'Search' expression can be improved a lot. Please help.EDIT: Further info as requested in commentsI'm using a text editor to run the regexp (TextWrangler, Atom), and the 'Search' and 'Replace' examples I've included are what I'm entering in the relevant Find/Replace boxes.The source of this data is a MySQL dump, and these lines are a sampling from some INPUT statements.Eventually I plan to use this search and replacement expression in a PHP script or Stored Procedure, and will cross that bridge when I come to it.
Replacing address details in colon delimited string
strings;regex;csv