Hi all,
been trying to extract dates from text fields, the date is not neccessarily in the same place or same format in each record. Below is my trial-and-error attempt.
I want to replace each dot with a forward-slash and ensure that day and month are always two digits and the year is 4 digits.
I can do it for each of the 8 formats present in the text field , but how do I combine them into one solution. Going around in cricles so need a helping hand.
is there a more simpler way of doing this using regular expressions, where I won't have to know which format the date is in before transformation . the result will be put into another field in the record. ?
thx
been trying to extract dates from text fields, the date is not neccessarily in the same place or same format in each record. Below is my trial-and-error attempt.
I want to replace each dot with a forward-slash and ensure that day and month are always two digits and the year is 4 digits.
Code:
testData1 = "Engine cut on approach and undershot practice forced landing Lords Bridge Cambs. 13.11.42 DBF" SearchExpr1 ="([0-9]{2})[.]([0-9]{2})[.]([0-9]{2})" testsplit1 = regex_split(testData1 ,SearchExpr1) testresult1 = crlf_to_comma(testsplit1) trace.writeln("1) "+Testresult1+" = "+padl(word(testresult1,1,","),2,"0")+"/"+padl(word(testResult1,2,","),2,"0")+"/19"+word(testresult1,3,",")) testData2 = "Engine cut on approach and undershot practice forced landing Lords Bridge Cambs. 1.11.42 DBF" SearchExpr2 = "([0-9])[.]([0-9]{2})[.]([0-9]{2})" testsplit2 = regex_split(testData2 ,SearchExpr2) testresult2 = crlf_to_comma(testsplit2) trace.writeln("2) "+Testresult2 + " = "+padl(word(testresult2,1,","),2,"0")+"/"+padl(word(testResult1,2,","),2,"0")+"/19"+word(testresult2,3,",")) testData3 = "Engine cut on approach and undershot practice forced landing Lords Bridge Cambs. 1.1.42 DBF" SearchExpr3 = "([0-9])[.]([0-9])[.]([0-9]{2})" testsplit3 = regex_split(testData3 ,SearchExpr3) testresult3 = crlf_to_comma(testsplit3) trace.writeln("3) "+Testresult3 +" = "+padl(word(testresult3,1,","),2,"0")+"/"+padl(word(testResult3,2,","),2,"0")+"/19"+word(testresult3,3,",")) testData4 = "Engine cut on approach and undershot practice forced landing Lords Bridge Cambs. 13.1.42 DBF" SearchExpr4 = "([0-9]{2})[.]([0-9])[.]([0-9]{2})" testsplit4 = regex_split(testData4 ,SearchExpr4) testresult4 = crlf_to_comma(testsplit4) trace.writeln("4) "+Testresult4 + " = "+padl(word(testresult4,1,","),2,"0")+"/"+padl(word(testResult4,2,","),2,"0")+"/19"+word(testresult4,3,",")) testData5 = "Engine cut on approach and undershot practice forced landing Lords Bridge Cambs. 1.11.42 DBF" SearchExpr5 = "([0-9])[.]([0-9]{2})[.]([0-9]{2})" testsplit5 = regex_split(testData5 ,SearchExpr5) testresult5 = crlf_to_comma(testsplit5) trace.writeln("1) "+Testresult5+" = "+padl(word(testresult5,1,","),2,"0")+"/"+padl(word(testResult5,2,","),2,"0")+"/19"+word(testresult5,3,",")) testData6 = "Engine cut on approach and undershot practice forced landing Lords Bridge Cambs. .42 DBF" SearchExpr6 = "[.]([0-9]{2})" testsplit6 = regex_split(testData6,SearchExpr6) testresult6 = crlf_to_comma(testsplit6) trace.writeln("6) "+Testresult6+" = "+"19"+word(testresult6,1,",")) testData7 = "Engine cut on approach and undershot practice forced landing Lords Bridge Cambs. 1.42 DBF" SearchExpr7 = "([0-9])[.]([0-9]{2})" testsplit7 = regex_split(testData7 ,SearchExpr7) testresult7 = crlf_to_comma(testsplit7) trace.writeln("7) "+Testresult7 +" = "+padl(word(testresult7,1,","),2,"0")+"/19"+word(testresult7,2,",")) testData8 = "Engine cut on approach and undershot practice forced landing Lords Bridge Cambs. 11.42 DBF" SearchExpr8 = "([0-9]{2})[.]([0-9]{2})" testsplit8 = regex_split(testData8 ,SearchExpr8) testresult8 = crlf_to_comma(testsplit8) trace.writeln("8) "+Testresult8+" = "+padl(word(testresult8,1,","),2,"0")+"/19"+word(testresult8,2,","))
is there a more simpler way of doing this using regular expressions, where I won't have to know which format the date is in before transformation . the result will be put into another field in the record. ?
thx
Comment