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.
replacing mixed date formats to default mySQL date
Moderators: AmigoJack, bbadmin, helios, Bob Hansen, MudGuard
-
ben_josephs
- Posts: 2461
- Joined: Sun Mar 02, 2003 9:22 pm
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:
(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})
Use Posix regular expression syntax:
Here is a simpler regex:Configure | Preferences | Editor
[X] Use POSIX regular expression syntax
(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})
-
Bob Hansen
- Posts: 1516
- Joined: Sun Mar 02, 2003 8:15 pm
- Location: Salem, NH
- Contact:
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
-----------------------------------------
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
-----------------------------------------
Hope this was helpful.............good luck,
Bob
Bob
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
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
D. White