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
Date 2008-02-13 
7/18/2016 9:54 PM  
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?


PostgreSQL does not implement Perl regular expression, it implements POSIX regular expressions. One difference among many is that \b represents a backspace character, not a word boundary. You can use \y to represent a word boundary, although I don't know if that has the exact same meaning as \b has in Perl.

If you want to use Perl regular expressions exactly, you could use the PL/Perl language

| improve this answer | |
  • \y worked! I am able to match the records by switching \b & \y in the REGEX expression I prepared. – Anu Jun 20 '19 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.