Re: Using regular expressions in LIKE

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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Nick Barr
> Sent: 2004. január 15. 10:06
> To: csegyud(at)vnet(dot)hu; terry(at)ashtonwoodshomes(dot)com;
> 'Pgsql-General(at)Postgresql(dot)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.
>