Using regular expressions in LIKE

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

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

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

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

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

---------------------------(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.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Nick Barr
Sent: 2004. janu�r 15. 10:06
To: csegyud@vnet.hu; terry@ashtonwoodshomes.com;
'Pgsql-General@Postgresql.Org (E-mail)'
Subject: Re: [GENERAL] Using regular expressions in LIKE

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

("_")?

If the ^ was not there then it could theoretically match
anywhere in the
string. In this particular case the regular expression will probably
match at the beginning of the string anyway, so it is not really
necessary. I prefer to put that sort of thing in to make it
clear to the
programmer what is going on.

To make the query more generic you could replace any of the numbers in
the date part with a ([0-9]{n}) bit. So for instance:

All dates whose minutes = 15 or 30 and whose year is 2003 and whose
month is 12:

select * from t_me30 where mertido ~ '^2003-12-([0-9]{2})
([0-9]{2}):(15|30)';

All dates whose minutes = 15 or 30 and whose year is:

select * from t_me30 where mertido ~ '^2003--([0-9]{2})-([0-9]{2})
([0-9]{2}):(15|30)';

All dates whose minutes = 15 or 30

select * from t_me30 where mertido ~
'^([0-9]{1,4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):(15|30)';

Note I have made the assumption that the year can be anything
from 1 AD
to now, hence the {0,4} part.

HTH

Nick

P.S. I am more familiar with Perl Regular Expressions, and not POSIX
ones, so this may not be the most concise form.

---------------------------(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.