Community

Hello!
I have an issue regarding a find & replace of mixed date formats in a csv file to a default mysql format. I've attempted to use something like the following regular expression (regexp) with no success.

FIND: \([1-9]|0[1-9]|1[012]\)[- /.]\([0-9]|0[1-9]|[12][0-9]|3[01]\)[- /.]\((19|20)[0-9]+\)

REPLACE: \3\-\1\-\2

[x] Regular Expressions
POSIX turned off

It's probably something obvious, but my brain fails to see it.
For those of you that care, I'm a longtime TextPad user, I've RTFM, plus searched on the forum and the web for an answer first, failing to find one that works.
Any help with this is greatly appreciated.

The problem has arisen because you are using non-Posix regular expression syntax. This has led you to make backslashing errors in your regex.

Use Posix regular expression syntax:

Configure | Preferences | Editor

[X] Use POSIX regular expression syntax

Here is a simpler regex:
(0?[1-9]|1[012])[- /.]([012]?[0-9]|3[01])[- /.]((19|20)[0-9]{2})
This even simpler one might be good enough:
([0-9]{1,2})[- /.]([0-9]{1,2})[- /.]([0-9]{4})

Here are some other RegEx strings that will allow for 1/2 digit days/months and 2/4 digits for years: Will handle all years, not just 1900 and 2000 years.

MM/DD/YYYY
(1[0-2]|0?[1-9])[-/.](3[01]|[12][0-9]|0?[1-9])[-/.](0?[12][0-9]{3})


DD/MM/YYYY
(3[01]|[12][0-9]|0?[1-9])[-/.](1[0-2]|0?[1-9])[-/.](0?[12][0-9]{3})


If you know all dates are valid you can use this:
([[:digit:]]{2})[-/.]([[:digit:]]{2})[-/.]([[:digit:]]{4})

Depending on where your strings are located, you may want to put a "^" or a space or another char at the front of the RegEx to prevent getting two matches from things like 12/12/2001. That could result in 12/12/2001 and 2/12/2001 as both being matched from one string. You want to force 2 chars in front of the delimiter.

-------------------------------------------------
Use the following settings:
-----------------------------------------
[X] Regular expression

Configure | Preferences | Editor
[X] Use POSIX regular expression syntax
-----------------------------------------

Thanks Guys! Sorry I was so late to get back. ben_josephs solution works for some, but I could not get Bob's to work for some reason.

The date formats are all mixed, some are mm/dd/yy, others mm/dd/yyyy, or yy/mm/dd, yyyy/mm/dd, d-Jan-yy, etc. So it's going to be quite a bit of work to get it all to yyyy-mm-dd which is what I need.

DW