473,725 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Nov 22 '05 #1
4 5184
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***@greatgulf homes.com
Fax: (416) 441-9085

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Együd Csaba
Sent: Wednesday, January 14, 2004 6:43 AM
To: Pg***********@P ostgresql.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

---------------------------(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 Együd

-----Original Message-----
From: te***@greatgulf homes.com [mailto:te***@gr eatgulfhomes.co m]On
Behalf Of te***@ashtonwoo dshomes.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***@greatgulf homes.com
Fax: (416) 441-9085

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Együd Csaba
Sent: Wednesday, January 14, 2004 6:43 AM
To: Pg***********@P ostgresql.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

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***@greatgulf homes.com
Fax: (416) 441-9085

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Együd Csaba
Sent: Wednesday, January 14, 2004 8:16 AM
To: te***@ashtonwoo dshomes.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***@greatgulf homes.com [mailto:te***@gr eatgulfhomes.co m]On
Behalf Of te***@ashtonwoo dshomes.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***@greatgulf homes.com
Fax: (416) 441-9085

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Együd Csaba Sent: Wednesday, January 14, 2004 6:43 AM
To: Pg***********@P ostgresql.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

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_m e30"
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_ert ektipus" btree (ertektipus)
"idx_t_me30_fom eazon" btree (fomeazon)
"idx_t_me30_mer tido" btree (mertido)
"idx_t_me30_mer tido2" btree (mertido bpchar_pattern_ ops)
"idx_t_me30_utm odido" 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****** ***********@pos tgresql.org]On Behalf Of
te***@ashtonwoo dshomes.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***@greatgulf homes.com
Fax: (416) 441-9085

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Együd Csaba
Sent: Wednesday, January 14, 2004 8:16 AM
To: te***@ashtonwoo dshomes.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***@greatgulf homes.com [mailto:te***@gr eatgulfhomes.co m]On
Behalf Of te***@ashtonwoo dshomes.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***@greatgulf homes.com
Fax: (416) 441-9085
> -----Original Message-----
> From: pg************* ****@postgresql .org
> [mailto:pg****** ***********@pos tgresql.org]On Behalf Of

Együd Csaba
> Sent: Wednesday, January 14, 2004 6:43 AM
> To: Pg***********@P ostgresql.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*******@postg resql.org

Nov 22 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2362
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 uppercase or lowercase, and all other characters being lowercase characters. The difficult part is that I want to obtain save the string positions of the invalid characters in an array, rather than just obtain a true or false value. Can anybody...
8
2428
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: 0.04, 0.02, 0.01" how can I extract this number with RE or otherwise?
11
3076
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 code is function RemoveHTMLScript(strText) { var regEx = /<script\w*<\/script>/g
2
2455
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 a rules engine for an application that basically involves allowing the user to enter the the WHERE clause for a SQL Server query that becomes the rule. The rules will be stored in a SQL Server database and the user will be able to make changes to...
1
4383
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 the first regular expression that matches the string. I've gor the regular expressions ordered so that the highest priority is first (if two or more regular expressions match the string I want the first one returned) The code that does this has...
12
2485
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 http://en.wikipedia.org/wiki/Regular_expression http://www.regular-expressions.info/javascript.html http://www.webreference.com/js/column5/
47
3440
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): row+="a"
9
2492
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 class=\"notice_tan\">(.| \s)*</div>/", "", $site); I'm trying to remove from the very top to a specific div Top of file:
9
2789
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 > bbbbb'. How to match text between the uppermost brackets ( 1 aaa < t bbb < a <tt ff 2 )? P.S. sorry for my english.
0
8872
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9392
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9246
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9162
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9091
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8069
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5997
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4505
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4773
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.