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
[color=blue]
> -----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
>
>[color=green]
> > -----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
> >
> >[color=darkred]
> > > -----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/curre...nctions-matchi
> > > ng.html#FUNCTI
> > > ONS-SQL99-REGEXP
> > >
> > >
http://www.postgresql.org/docs/curre...nctions-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[/color]
> > Együd Csaba[color=darkred]
> > > > 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[/color]
> > performance.[color=darkred]
> > > >
> > > > ... 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:[/color][/color]
> 2004. 01. 13.[color=green][color=darkred]
> > >[/color]
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >
http://archives.postgresql.org
> >[/color]
>
>
> ---------------------------(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.
>[/color]
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org 
