Re: Using regular expressions in LIKE - Mailing list pgsql-general

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 ("_")?

Again, many thanks for your help and patience!

Have a nice day, good bye,
-- Csaba


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> terry@ashtonwoodshomes.com
> Sent: 2004. január 14. 14:32
> To: csegyud@vnet.hu; 'Pgsql-General@Postgresql.Org (E-mail)'
> Subject: Re: [GENERAL] Using regular expressions in LIKE
>
>
> Well, if one reads between the lines I think it gives you all
> you need, but
> here is an example to show you:
> devtest3=# select 'test' where 'abcd12' ~ 'abcd(12|34)';
>  ?column?
> ----------
>  test
> (1 row)
>
> devtest3=# select 'test' where 'abcd34' ~ 'abcd(12|34)';
>  ?column?
> ----------
>  test
> (1 row)
>
> devtest3=# select 'test' where 'abcd56' ~ 'abcd(12|34)';
>  ?column?
> ----------
> (0 rows)
>
> Perhaps what is confusing you is you are trying to use a LIKE
> statement.
> DON'T do that:  SQL compliant LIKE statements are *not*
> regular expressions.
> If you really want SQL compliant  regular expressions use the
> SQL statement
> SIMILAR TO  (I believe SIMILAR TO is SQL compliant but not
> 100% positive)
>
> HOWEVER:  Unless you really want to use SIMILAR TO, I would
> use the POSIX
> operators ~, ~*, !~ and !~*, POSIX is more standardized/supported, and
> sometimes can offer power one needs that is not available in
> other pattern
> matching (although I have no specific examples of
> shortcomings in SIMILAR TO
> as I don't use it anyway...)
>
> Terry Fielder
> Manager Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
> Fax: (416) 441-9085
>
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Együd Csaba
> > Sent: Wednesday, January 14, 2004 8:16 AM
> > To: terry@ashtonwoodshomes.com; 'Pgsql-General@Postgresql.Org
> > (E-mail)'
> > Subject: Re: [GENERAL] Using regular expressions in LIKE
> >
> >
> > Hi Terry,
> > thanks for your ansver. I've already read this page but I
> > couldn't find out
> > if I can do such things or not. And if I can than how. So if
> > you can suggest
> > me some additional manual pages regarding regular expressions
> > can be used in
> > LIKE statements, please write me.
> >
> > I don't know where to find it in the manual... :(
> >
> > Thank you very much,
> > -- Csaba Együd
> >
> >
> > > -----Original Message-----
> > > From: terry@greatgulfhomes.com [mailto:terry@greatgulfhomes.com]On
> > > Behalf Of terry@ashtonwoodshomes.com
> > > Sent: 2004. január 14. 12:51
> > > To: csegyud@vnet.hu; 'Pgsql-General@Postgresql.Org (E-mail)'
> > > Subject: RE: [GENERAL] Using regular expressions in LIKE
> > >
> > >
> > > Don't be afraid to read the manual:
> > >
> > > http://www.postgresql.org/docs/current/static/functions-matchi
> > > ng.html#FUNCTI
> > > ONS-SQL99-REGEXP
> > >
> > > http://www.postgresql.org/docs/current/static/functions-matchi
> > > ng.html#FUNCTI
> > > ONS-POSIX-REGEXP
> > >
> > > Terry Fielder
> > > Manager Software Development and Deployment
> > > Great Gulf Homes / Ashton Woods Homes
> > > terry@greatgulfhomes.com
> > > Fax: (416) 441-9085
> > >
> > >
> > > > -----Original Message-----
> > > > From: pgsql-general-owner@postgresql.org
> > > > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> > Együd Csaba
> > > > Sent: Wednesday, January 14, 2004 6:43 AM
> > > > To: Pgsql-General@Postgresql.Org (E-mail)
> > > > Subject: [GENERAL] Using regular expressions in LIKE
> > > >
> > > >
> > > > Hi All,
> > > > I'd like to "compress" the following two filter expressions
> > > into one -
> > > > assuming that it makes sense regarding query execution
> > performance.
> > > >
> > > > ... where (adate LIKE "2004.01.10 __:30" or adate LIKE
> > > > "2004.01.10 __:15")
> > > > ...
> > > >
> > > > into something like this:
> > > >
> > > > ... where adate LIKE "2004.01.10 __:(30/15)" ...
> > > >
> > > > which means that I need only those rows which has an "adate"
> > > > field holding
> > > > dates on 2004.01.10 every 30 or 15 minutes at the end. Is it
> > > > possible to use
> > > > some regular expressions or is it worth at all talking about?
> > > >
> > > > thanks,
> > > > -- Csaba
> > > >
> > > > ----------------------------------------
> > > > Együd Csaba
> > > > csegyud@vnet.hu
> > > > IN-FO Studio Bt.
> > > > tel/fax: +36-23-545-447, +36-23-382-447
> > > > mobil:   +36-23-343-8325
> > > >
> > > >
> > > > ---------------------------(end of
> > > > broadcast)---------------------------
> > > > TIP 8: explain analyze is your friend
> > > >
> > >
> > > -- 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.
> > >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
>
>
> ---------------------------(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.
>