I need to change date format of many values in a SQL file.
There are date values which are in the 'DD-MON-RR' format but I need them to be in the 'YYYY-MM-DD' format.
In the file I have dates from 21th century and 20th century:
to_date('01-Nov-20','DD-MON-RR'),to_date('28-Dec-99','DD-MON-RR')
I'm searching for a regex to find and replace those values.
This was my first try, works even with the 20th century years:
FIND:
(\d{2})-(\w{3})-(([0-4])\d|[5-9]\d)
REPLACE:
(?{4}20:19)\3-\2-\1
This one helps to reorder the position of elements but I have to replace all the months with related numbers.
Now I'm searching for something to find and replace month names in the same regex:
FIND:
(\d{2})-((Jan)|(Feb)|(Mar)|(Apr)|(May)|(Jun)|(Jul)|(Aug)|(Sep)|(Oct)|(Nov)|(Dec))-(([0-4])\d|[5-9]\d)
Now I'm having trouble trying to write the replace expression, someone can help me?
Thank you
CodePudding user response:
You can define as many month (month abbreviations) instead of (\w{3})
and use a bit more conditions in the conditional replacement pattern.
You can use
Find What: (\d{2})-(?:(Jan)|(Feb)|(Mar)|(Apr)|(May)|(Jun)|(Jul)|(Aug)|(Sep)|(Oct)|(Nov)|(Dec))-(([0-4])\d|[5-9]\d)
Replace With: (?{15}20:19)$14-(?{13}12:?{12}11:?{11}10:?{10}09:?{9}08:?{8}07:?{7}06:?{6}05:?{5}04:?{4}03:?{3}02:?{2}01)-$1
See the demo screenshot:
So, (\w{3})
is replaced with (?:(Jan)|(Feb)|(Mar)|(Apr)|(May)|(Jun)|(Jul)|(Aug)|(Sep)|(Oct)|(Nov)|(Dec))
, a non-capturing group with 12 capturing groups. You may edit it, say, to also support Sept
, add t?
after Sep
, or add e?
after Jun
to also support June
.
The replacement pattern is adjusted to account for the newly introduced 12 capturing groups.