Thread

1. Re: Using regular expressions in LIKE

   Nick Barr <nick.barr@webbased.co.uk> — 2004-01-15T09:05:35Z

   > Hi Terry & Nick,
   > thank you very much for your help. My lack of comprehension is because
   of
   > my
   > lack of knowladge of regular expressions.
   > 
   > =====================================================
   > tgr=# \d t_me30
   >                 Table "public.t_me30"
   >     Column    |           Type           | Modifiers
   > --------------+--------------------------+-----------
   >  fomeazon     | integer                  |
   >  mertido      | character(16)            |
   >  ertektipus   | character(10)            |
   >  hetnap       | character(1)             |
   >  impulzusszam | double precision         |
   >  mertertek    | double precision         |
   >  merttartam   | integer                  |
   >  utmodido     | timestamp with time zone |
   > Indexes:
   >     "idx_t_me30_ertektipus" btree (ertektipus)
   >     "idx_t_me30_fomeazon" btree (fomeazon)
   >     "idx_t_me30_mertido" btree (mertido)
   >     "idx_t_me30_mertido2" btree (mertido bpchar_pattern_ops)
   >     "idx_t_me30_utmodido" btree (utmodido)
   > =====================================================
   > 
   > 1. Using Terry's query it didn't work because I tried to used LIKE's
   <any
   > one character> operator "_":
   >    select * from t_me30 where mertido ~ '2003-12-17___:(15|30)'; -- It
   > results an empty set.
   > 
   > 2. Using Nick's query "select * from t_me30 where mertido ~
   '^2003-12-17
   > ([0-9]{2}):(15|30)';" it worked fine and fast.
   > 
   > Nick, I can understand now the meaning of your regular expression.
   Just a
   > question: why is it required to indicate the begining of the value by
   "^"?
   > Wouldn't it be clear for the interpreter.
   > Is there any other way (simpler) to indicate that 3-4 irrelevant
   character
   > in the centre of the value - I mean something like I tried first
   ("_")?
   > 
   
   If the ^ was not there then it could theoretically match anywhere in the
   string. In this particular case the regular expression will probably
   match at the beginning of the string anyway, so it is not really
   necessary. I prefer to put that sort of thing in to make it clear to the
   programmer what is going on.
   
   To make the query more generic you could replace any of the numbers in
   the date part with a ([0-9]{n}) bit. So for instance:
   
   All dates whose minutes = 15 or 30 and whose year is 2003 and whose
   month is 12:
   
   select * from t_me30 where mertido ~ '^2003-12-([0-9]{2})
   ([0-9]{2}):(15|30)';
   
   All dates whose minutes = 15 or 30 and whose year is:
   
   select * from t_me30 where mertido ~ '^2003--([0-9]{2})-([0-9]{2})
   ([0-9]{2}):(15|30)';
   
   All dates whose minutes = 15 or 30
   
   select * from t_me30 where mertido ~
   '^([0-9]{1,4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):(15|30)';
   
   Note I have made the assumption that the year can be anything from 1 AD
   to now, hence the {0,4} part.
   
   HTH
   
   
   Nick
   
   
   P.S. I am more familiar with Perl Regular Expressions, and not POSIX
   ones, so this may not be the most concise form.
   
   
   
   

2. Re: Using regular expressions in LIKE

   Együd Csaba <csegyud@vnet.hu> — 2004-01-15T09:57:51Z

   Thanks Nick,
   I can understand now. I'm afraid it is far a bit from Postgres by now...
   Sorry.
   
   Best wishes,
   -- Csaba
   
   > -----Original Message-----
   > From: pgsql-general-owner@postgresql.org
   > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Nick Barr
   > Sent: 2004. január 15. 10:06
   > To: csegyud@vnet.hu; terry@ashtonwoodshomes.com;
   > 'Pgsql-General@Postgresql.Org (E-mail)'
   > Subject: Re: [GENERAL] Using regular expressions in LIKE
   >
   >
   > > Hi Terry & Nick,
   > > thank you very much for your help. My lack of comprehension
   > is because
   > of
   > > my
   > > lack of knowladge of regular expressions.
   > >
   > > =====================================================
   > > tgr=# \d t_me30
   > >                 Table "public.t_me30"
   > >     Column    |           Type           | Modifiers
   > > --------------+--------------------------+-----------
   > >  fomeazon     | integer                  |
   > >  mertido      | character(16)            |
   > >  ertektipus   | character(10)            |
   > >  hetnap       | character(1)             |
   > >  impulzusszam | double precision         |
   > >  mertertek    | double precision         |
   > >  merttartam   | integer                  |
   > >  utmodido     | timestamp with time zone |
   > > Indexes:
   > >     "idx_t_me30_ertektipus" btree (ertektipus)
   > >     "idx_t_me30_fomeazon" btree (fomeazon)
   > >     "idx_t_me30_mertido" btree (mertido)
   > >     "idx_t_me30_mertido2" btree (mertido bpchar_pattern_ops)
   > >     "idx_t_me30_utmodido" btree (utmodido)
   > > =====================================================
   > >
   > > 1. Using Terry's query it didn't work because I tried to used LIKE's
   > <any
   > > one character> operator "_":
   > >    select * from t_me30 where mertido ~
   > '2003-12-17___:(15|30)'; -- It
   > > results an empty set.
   > >
   > > 2. Using Nick's query "select * from t_me30 where mertido ~
   > '^2003-12-17
   > > ([0-9]{2}):(15|30)';" it worked fine and fast.
   > >
   > > Nick, I can understand now the meaning of your regular expression.
   > Just a
   > > question: why is it required to indicate the begining of
   > the value by
   > "^"?
   > > Wouldn't it be clear for the interpreter.
   > > Is there any other way (simpler) to indicate that 3-4 irrelevant
   > character
   > > in the centre of the value - I mean something like I tried first
   > ("_")?
   > >
   >
   > If the ^ was not there then it could theoretically match
   > anywhere in the
   > string. In this particular case the regular expression will probably
   > match at the beginning of the string anyway, so it is not really
   > necessary. I prefer to put that sort of thing in to make it
   > clear to the
   > programmer what is going on.
   >
   > To make the query more generic you could replace any of the numbers in
   > the date part with a ([0-9]{n}) bit. So for instance:
   >
   > All dates whose minutes = 15 or 30 and whose year is 2003 and whose
   > month is 12:
   >
   > select * from t_me30 where mertido ~ '^2003-12-([0-9]{2})
   > ([0-9]{2}):(15|30)';
   >
   > All dates whose minutes = 15 or 30 and whose year is:
   >
   > select * from t_me30 where mertido ~ '^2003--([0-9]{2})-([0-9]{2})
   > ([0-9]{2}):(15|30)';
   >
   > All dates whose minutes = 15 or 30
   >
   > select * from t_me30 where mertido ~
   > '^([0-9]{1,4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):(15|30)';
   >
   > Note I have made the assumption that the year can be anything
   > from 1 AD
   > to now, hence the {0,4} part.
   >
   > HTH
   >
   >
   > Nick
   >
   >
   > P.S. I am more familiar with Perl Regular Expressions, and not POSIX
   > ones, so this may not be the most concise form.
   >
   >
   >
   > ---------------------------(end of
   > broadcast)---------------------------
   > TIP 5: Have you checked our extensive FAQ?
   >
   >                http://www.postgresql.org/docs/faqs/FAQ.html
   >
   >
   >
   > -- Incoming mail is certified Virus Free.
   > Checked by AVG Anti-Virus (http://www.grisoft.com).
   > Version: 7.0.211 / Virus Database: 261 - Release Date: 2004. 01. 13.
   >
   
   
   

3. Re: Using regular expressions in LIKE

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2004-01-15T12:08:52Z

   On Thu, Jan 15, 2004 at 09:05:35AM -0000, Nick Barr wrote:
   > If the ^ was not there then it could theoretically match anywhere in the
   > string. In this particular case the regular expression will probably
   > match at the beginning of the string anyway, so it is not really
   > necessary. I prefer to put that sort of thing in to make it clear to the
   > programmer what is going on.
   
   Isn't there also a performance benefit as you can use an index if you
   say "this definitely starts at the beginning" with the '^'?
   
   Cheers,
   
   Patrick