I am working with a dataset in PostgreSQL which is quick messy. I have written a few regex operations to find date patterns in the records of a table. Since I don't have a generalized REGEX defined for finding any kind of date pattern!, so, I have created a few using some examples found in the database such as:
9/18/2010 6:17:00 AM
05/21/2014
Date 2008-02-13
7/18/2016 9:54 PM
2000-04-20
Booked:1/16/2011 6:45:00 AM (2008-09-01 12:35:45 )
on January 3, 2008
on July 3, 2002
23 Mar 2017
5 Feb 2004
January 3, 2008
January 31, 2001
December 19, 2007
February 2009
February 30
- Feb 3, 2011
9 Jun 2010
This REGEX matches a few date formats and is able to find the records in the database!:
select count(*) from gallery_image where description ~* '(\d+)\/(\d+)\/(\d+)|(\d+)-(\d+)-(\d+)';
But, this REGEX matches the example, but unable to find a single record in the database, even though they are present in the DB since I have taken all the examples from it.?
select count(*) from gallery_image where description ~* '(\b\d{1,2}\D{0,3})?\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|(Nov|Dec)(?:ember)?)\D?(\d{1,2}\D?)?\D?((19[7-9]\d|20\d{2})|\d{2})';
Could anyone point out the mistake I have made in the second query or REGEX pattern that it works normally but not when used in the PostgreSQL query?
