Thread: 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
Don't be afraid to read the manual: http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTI ONS-SQL99-REGEXP http://www.postgresql.org/docs/current/static/functions-matching.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 >
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. >
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 >
> > > -----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
> > >
How about:
where adate ~ '^2004-01-10 ([0-9]{2}):(15|30)'
There may be a more concise version but this seems to work. Please say
if you want a description of exactly what the string means.
Kind Regards,
Nick Barr
WebBased Ltd.
Tel: (01752) 764445
Fax: (01752) 764446
Email: nick.barr@webbased.co.uk
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.
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: 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.
>
> 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.
Thanks Nick,
I can understand now. I'm afraid it is far a bit from Postgres by now...
Sorry.
Best wishes,
-- Csaba
> -----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.
>
On Thu, Jan 15, 2004 at 09:05:35AM -0000, Nick Barr wrote: > 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. Isn't there also a performance benefit as you can use an index if you say "this definitely starts at the beginning" with the '^'? Cheers, Patrick
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Patrick Welche > Sent: 15 January 2004 12:09 > To: Nick Barr > Cc: csegyud@vnet.hu; terry@ashtonwoodshomes.com; 'Pgsql- > General@Postgresql.Org (E-mail)' > Subject: Re: [GENERAL] Using regular expressions in LIKE > > On Thu, Jan 15, 2004 at 09:05:35AM -0000, Nick Barr wrote: > > 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. > > Isn't there also a performance benefit as you can use an index if you > say "this definitely starts at the beginning" with the '^'? That is what I was thinking, which is the other reason why I put it in. This is certainly the case with queries that use the LIKE operator, for example: where adate like 'random%'; This is only the case with the default locale I believe. I have no idea when it comes to regexs though and specifically the ~ operator. Could someone more knowledgeable about this stuff reply? Nick
"Nick Barr" <nick.barr@webbased.co.uk> writes:
>> Isn't there also a performance benefit as you can use an index if you
>> say "this definitely starts at the beginning" with the '^'?
> That is what I was thinking, which is the other reason why I put it in.
> This is only the case with the default locale I believe. I have no idea
> when it comes to regexs though and specifically the ~ operator. Could
> someone more knowledgeable about this stuff reply?
Regexes are optimized the same way as equivalent LIKE expressions. In
particular, the pattern has to be left-anchored to consider using it
with an index. In LIKE that means no wildcard at the start of the
pattern, in regex it means there has to be a ^.
Locale and case sensitivity issues are the same, too.
regards, tom lane
> Regexes are optimized the same way as equivalent LIKE expressions. In > particular, the pattern has to be left-anchored to consider using it > with an index. In LIKE that means no wildcard at the start of the > pattern, in regex it means there has to be a ^. What about "^.*oobar" in a regex ? I mean, it seems impossible to use an index on that, right ? Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
>> Regexes are optimized the same way as equivalent LIKE expressions. In
>> particular, the pattern has to be left-anchored to consider using it
>> with an index. In LIKE that means no wildcard at the start of the
>> pattern, in regex it means there has to be a ^.
> What about "^.*oobar" in a regex ? I mean, it seems impossible
> to use an index on that, right ?
Right. You need ^ immediately followed by some constant text. The
planner extracts the "fixed prefix" of the pattern to use with the index.
regards, tom lane