Postgresql – Date pattern regex doesn’t match the records in PostgreSQL query

postgresqlregex

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?

Best Answer

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

Related Solutions

Postgresql regex match all digits in column

Better use regexp_replace, globally replacing non-digits by an empty string:

select regexp_replace('dsa8a552a5a2a5?', '\D', '', 'g');

 regexp_replace 
----------------
 8552525

Postgresql: Pattern match against array elements

You can accomplish what you're asking for by doing something like this.

I created a table and data to help better illustrate what I'm doing.

CREATE TABLE logs (id serial NOT NULL PRIMARY KEY, lognum int, flightnum int);

INSERT INTO logs (lognum, flightnum) VALUES (1,6);
INSERT INTO logs (lognum, flightnum) VALUES (1,7);
INSERT INTO logs (lognum, flightnum) VALUES (1,8);
INSERT INTO logs (lognum, flightnum) VALUES (2,80);
INSERT INTO logs (lognum, flightnum) VALUES (3,12);
INSERT INTO logs (lognum, flightnum) VALUES (4,8008);

postgres@[local]:5432:postgres:=# SELECT * FROM logs;
 id | lognum | flightnum 
----+--------+-----------
 13 |      1 |         6
 14 |      1 |         7
 15 |      1 |         8
 16 |      2 |        80
 17 |      3 |        12
 18 |      4 |      8008
(6 rows)

Time: 0.188 ms
postgres@[local]:5432:postgres:=#

Note that 1, 2, and 4 have flights with 8, 80, and 8008.

Now, using this query, find the lognums with 8 as a flightnum. This takes your original query with an array_agg, wraps another query around it to generates subscripts for each member in the array, which can be arbitrarily large. Finally, an outer query wraps that, which uses the generated subscript to allow you to do a comparison against each of the members of the flightnums array to see if they are LIKE '8%'.

SELECT lognum FROM ( 
                      SELECT lognum, 
                      flightnums, 
                      generate_subscripts(flightnums, 1) AS s 
                      FROM ( 
                             SELECT lognum, 
                                    array_agg(flightnum) AS flightnums 
                                    FROM logs 
                                         GROUP BY lognum
                           ) AS t1
                   ) AS t2 
            WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;

Which gives you the following output

postgres@[local]:5432:postgres:=# SELECT lognum FROM (SELECT lognum, flightnums, generate_subscripts(flightnums, 1) AS s FROM ( SELECT lognum, array_agg(flightnum) AS flightnums FROM logs GROUP BY lognum) AS t1) AS t2 WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;
 lognum 
--------
      1
      2
      4
(3 rows)

Time: 0.338 ms
postgres@[local]:5432:postgres:=#

As would be expected from the data above.

For further array manipulation needs, I would recommend reading up on their chapter in the PostgreSQL documentation arrays

Best Answer

Related Solutions

Postgresql regex match all digits in column

Postgresql: Pattern match against array elements

Related Question