Find and replace changing date format in notepad++ using regular expressions

user_10315546 0
Viewed 444

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

notepad++ date regex string
1 Answers

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:

enter image description here

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.

user_38329700
Related