By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

Using regular expressions in LIKE

P: n/a
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

----------------------------------------
Egyd 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

Nov 22 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Don't be afraid to read the manual:

http://www.postgresql.org/docs/curre...ng.html#FUNCTI
ONS-SQL99-REGEXP

http://www.postgresql.org/docs/curre...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 Egyd 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

----------------------------------------
Egyd 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #2

P: n/a
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 Egyd

-----Original Message-----
From: te***@greatgulfhomes.com [mailto:te***@greatgulfhomes.com]On
Behalf Of te***@ashtonwoodshomes.com
Sent: 2004. janur 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 Egyd 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

----------------------------------------
Egyd 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

Nov 22 '05 #3

P: n/a
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 Egyd 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 Egyd

-----Original Message-----
From: te***@greatgulfhomes.com [mailto:te***@greatgulfhomes.com]On
Behalf Of te***@ashtonwoodshomes.com
Sent: 2004. janur 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 Egyd 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

----------------------------------------
Egyd 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

Nov 22 '05 #4

P: n/a
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. janur 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 Egyd 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 Egyd

-----Original Message-----
From: te***@greatgulfhomes.com [mailto:te***@greatgulfhomes.com]On
Behalf Of te***@ashtonwoodshomes.com
Sent: 2004. janur 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

Egyd 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
>
> ----------------------------------------
> Egyd 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

Nov 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.