Thread

1. Regular expression not working [^xyz]

   Stuart Peters <s.peters@surrey.ac.uk> — 2000-10-02T17:15:03Z

   I wasn't aware that Postgres accepted regular expressions (they seem 
   undocumented) until reading the draft of Bruce Momjian's forthcoming 
   book.  However, now having looked at them, I have found a bug in 
   matching a particular expression from a PostgreSQL database.  I am 
   using:
   
     PostgreSQL 7.0.2 on sparc-sun-solaris2.7, compiled by gcc 2.8.1
   
   I have created a very brief table ('bug') that has one 'text' field 
   ('title')  and three records:
   
   
   \connect - postgres
   CREATE TABLE "bug" (
            "title" text
   );
   COPY "bug" FROM stdin;
   abcdefghijklm
   nopqrstuvwxyz
   aeiou
   \.
   
   
   
   The following queries work as expected, EXCEPT for [2].  I believe 
   that [2] and [3] should return identical results, but [2] simply 
   lists ALL titles:
   
   [1]  select title from bug where title ~* '[ae]';
   
          Lists titles that contain one of the letters a or e: WORKS
   
   [2]  select title from bug where title ~* '[^ae]';
   
          Lists ALL titles rather than those that don't contain a or e: 
   DOESN'T WORK
          (Note: 'select title from bug where title ~* '[^a];' does not work either
   
   [3]  select title from bug where title !~* '[ae]';
   
          Lists all titles that don't contain a, q, x or z: WORKS
   
   [4]  select title from bug where title ~* '^[^ae]';
   
          Lists all titles that do not begin with a, q, x or z: WORKS
   
   
   I hope this helps with development,
   
   Regards,
   Stuart Peters
   ____________________________________________________________________________
   Stuart Peters, Dept of Sociology, University of Surrey, Guildford GU2 7XH,
   United Kingdom.  Tel: +44 (0)1483 259292 Fax: +44 (0)1483 259551
   
   Electronic Publishing Resource Service - EPRESS   http://www.epress.ac.uk/
   
   

2. Re: Regular expression not working [^xyz]

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-10-09T23:43:10Z

   On Mon, 2 Oct 2000, Stuart Peters wrote:
   
   > \connect - postgres
   > CREATE TABLE "bug" (
   >          "title" text
   > );
   > COPY "bug" FROM stdin;
   > abcdefghijklm
   > nopqrstuvwxyz
   > aeiou
   > \.
   > 
   > 
   > 
   > The following queries work as expected, EXCEPT for [2].  I believe 
   > that [2] and [3] should return identical results, but [2] simply 
   > lists ALL titles:
   > 
   > [1]  select title from bug where title ~* '[ae]';
   > 
   >        Lists titles that contain one of the letters a or e: WORKS
   > 
   > [2]  select title from bug where title ~* '[^ae]';
   > 
   >        Lists ALL titles rather than those that don't contain a or e: 
   > DOESN'T WORK
   >        (Note: 'select title from bug where title ~* '[^a];' does not work either
   
   No, that will match any title with at least one character that is not an
   a or e.  It you want a match for all characters not a or e it's 
   ~* '^[^ae]*$'
   
   If you were to put into bug a row like a or ae or e, that wouldn't match
   on 2, but abcde... would match because there exists a character other than
   a or e.