472,986 Members | 2,874 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,986 software developers and data experts.

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

Nov 22 '05 #1
4 5092
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ron Brennan | last post by:
Good morning. I would like to use one or more RegExp to validate country names as having the first and last words beginning with an uppercase letter, intermediate words beginning with either...
8
by: Michael McGarry | last post by:
Hi, I am horrible with Regular Expressions, can anyone recommend a book on it? Also I am trying to parse the following string to extract the number after load average. ".... load average:...
11
by: rajarao | last post by:
hi I want to remove the content embedded in <script> and </script> tags submitted via text box. My java script should remove the content embedded between <script> and </script> tag. my current...
2
by: Bob | last post by:
Let me state up front that I know very little about XML. My experience is pretty much limited using the XML Serializer to serialize a user preferences class to a file and back again. I'm writing...
1
by: Allan Ebdrup | last post by:
I have a dynamic list of regular expressions, the expressions don't change very often but they can change. And I have a single string that I want to match the regular expressions against and find...
12
by: FAQEditor | last post by:
Anybody have any URL's to tutorials and/or references for Regular Expressions? The four I have so far are: http://docs.sun.com/source/816-6408-10/regexp.htm...
47
by: Henning_Thornblad | last post by:
What can be the cause of the large difference between re.search and grep? This script takes about 5 min to run on my computer: #!/usr/bin/env python import re row="" for a in range(156000):...
9
by: Rene | last post by:
I'm trying to basically remove chunks of html from a page but I must not be doing my regular expression correctly. What i'm trying with no avail. $site = preg_replace("/<!DOCTYPE(.|\s)*<div...
9
by: netimen | last post by:
I have a text containing brackets (or what is the correct term for '>'?). I'd like to match text in the uppermost level of brackets. So, I have sth like: 'aaaa 123 < 1 aaa < t bbb < a <tt ff 2...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.