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: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of
te***@ashtonwoodshomes.com
Sent: 2004. január 14. 14:32
To: cs*****@vnet.hu; 'P************@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
te***@greatgulfhomes.com
Fax: (416) 441-9085
-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Együd Csaba
Sent: Wednesday, January 14, 2004 8:16 AM
To: te***@ashtonwoodshomes.com; 'P************@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: te***@greatgulfhomes.com [mailto:te***@greatgulfhomes.com]On
Behalf Of te***@ashtonwoodshomes.com
Sent: 2004. január 14. 12:51
To: cs*****@vnet.hu; 'P************@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
te***@greatgulfhomes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pg*****************@postgresql.org
> [mailto:pg*****************@postgresql.org]On Behalf Of
Együd Csaba > Sent: Wednesday, January 14, 2004 6:43 AM
> To: Pg***********@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
> cs*****@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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
ma*******@postgresql.org