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

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
>