agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRegular Expressions
18+ messages / 11 participants
[nested] [flat]
* Regular Expressions
@ 2007-03-21 14:04 Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 14:28 ` Re: Regular Expressions Bricklen Anderson <banderson@presinet.com>
2007-03-21 14:36 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
0 siblings, 2 replies; 18+ messages in thread
From: Ezequias R. da Rocha @ 2007-03-21 14:04 UTC (permalink / raw)
To: pgsql-sql
Hi list,
I would like to know if postgresql has a Regular Expressions (Regex)
implemented already.
With it we could implement queries like
Select * from myClientes where name = 'E[zs]equias'
where the result occurs even if the field has Ezequias or Esequias.
Regards
Ezequias
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2007-03-21 14:04 Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
@ 2007-03-21 14:28 ` Bricklen Anderson <banderson@presinet.com>
1 sibling, 0 replies; 18+ messages in thread
From: Bricklen Anderson @ 2007-03-21 14:28 UTC (permalink / raw)
To: Ezequias R. da Rocha <ezequias@fastcon.com.br>; +Cc: pgsql-sql
Ezequias R. da Rocha wrote:
> Hi list,
>
> I would like to know if postgresql has a Regular Expressions (Regex)
> implemented already.
>
> With it we could implement queries like
>
> Select * from myClientes where name = 'E[zs]equias'
>
> where the result occurs even if the field has Ezequias or Esequias.
>
> Regards
> Ezequias
Pretty easy to find matches in the documentation at
http://search.postgresql.org/
eg.
http://www.postgresql.org/docs/8.2/interactive/functions-matching.html
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2007-03-21 14:04 Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
@ 2007-03-21 14:36 ` Guy Fraser <guy@incentre.net>
2007-03-21 17:37 ` Re: Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
1 sibling, 1 reply; 18+ messages in thread
From: Guy Fraser @ 2007-03-21 14:36 UTC (permalink / raw)
To: pgsql-sql
On Wed, 2007-03-21 at 11:04 -0300, Ezequias R. da Rocha wrote:
> Hi list,
>
> I would like to know if postgresql has a Regular Expressions (Regex)
> implemented already.
>
> With it we could implement queries like
>
> Select * from myClientes where name = 'E[zs]equias'
>
Case Sensitive Regular Match ~
Case Insensitive Regular Match ~*
Negated Case Sensitive Regular Match !~
Negated Case Insensitive Regular Match !~*
Select * from myClientes where name ~ 'E[zs]equias'
> where the result occurs even if the field has Ezequias or Esequias.
>
> Regards
> Ezequias
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2007-03-21 14:04 Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 14:36 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
@ 2007-03-21 17:37 ` Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 20:32 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
2007-03-22 01:35 ` Re: Regular Expressions Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 18+ messages in thread
From: Ezequias R. da Rocha @ 2007-03-21 17:37 UTC (permalink / raw)
To: Guy Fraser <guy@incentre.net>; +Cc: pgsql-sql
Guy Fraser escreveu:
> On Wed, 2007-03-21 at 11:04 -0300, Ezequias R. da Rocha wrote:
>
>> Hi list,
>>
>> I would like to know if postgresql has a Regular Expressions (Regex)
>> implemented already.
>>
>> With it we could implement queries like
>>
>> Select * from myClientes where name = 'E[zs]equias'
>>
>>
> Case Sensitive Regular Match ~
> Case Insensitive Regular Match ~*
> Negated Case Sensitive Regular Match !~
> Negated Case Insensitive Regular Match !~*
>
> Select * from myClientes where name ~ 'E[zs]equias'
>
>
>> where the result occurs even if the field has Ezequias or Esequias.
>>
>> Regards
>> Ezequias
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>>
Great I am thinking of putting my like to rest. I felt it faster than
"like" statement, have you any information about that ?
Ezequias
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2007-03-21 14:04 Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 14:36 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
2007-03-21 17:37 ` Re: Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
@ 2007-03-21 20:32 ` Guy Fraser <guy@incentre.net>
2007-03-27 20:01 ` Re: Regular Expressions ezequias@fastcon.com.br
1 sibling, 1 reply; 18+ messages in thread
From: Guy Fraser @ 2007-03-21 20:32 UTC (permalink / raw)
To: pgsql-sql
On Wed, 2007-03-21 at 14:37 -0300, Ezequias R. da Rocha wrote:
> Guy Fraser escreveu:
> > On Wed, 2007-03-21 at 11:04 -0300, Ezequias R. da Rocha wrote:
> >
> >> Hi list,
> >>
> >> I would like to know if postgresql has a Regular Expressions (Regex)
> >> implemented already.
> >>
> >> With it we could implement queries like
> >>
> >> Select * from myClientes where name = 'E[zs]equias'
> >>
> >>
> > Case Sensitive Regular Match ~
> > Case Insensitive Regular Match ~*
> > Negated Case Sensitive Regular Match !~
> > Negated Case Insensitive Regular Match !~*
> >
> > Select * from myClientes where name ~ 'E[zs]equias'
> >
> >
> >> where the result occurs even if the field has Ezequias or Esequias.
> >>
> >> Regards
> >> Ezequias
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 2: Don't 'kill -9' the postmaster
> >>
> >>
> Great I am thinking of putting my like to rest. I felt it faster than
> "like" statement, have you any information about that ?
>
No I don't know if regular expressions are faster than "LIKE" but
I think they are more flexible. When developing queries, I usually
try different methods of matching to find out what works best for
each circumstance. Some times upper() lower() and substr() with an
"=" are more effective than other methods.
One of the more powerful features of PostgreSQL is the ability to
use sub-selects to reduce the time required to process a subset of
data from a larger volume of data.
Example :
select
*
from (
select
ss_time,
ss_date,
ss_type,
ss_data
from
full_set
where
ss_type in ('type_a','type_x')
) as sub_set
where
upper(ss_data) ~ '[A-Z][0-9][A-Z] ?[0-9][A-Z][0-9]'
order by
ss_time,
ss_date,
ss_type
;
> Ezequias
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2007-03-21 14:04 Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 14:36 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
2007-03-21 17:37 ` Re: Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 20:32 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
@ 2007-03-27 20:01 ` ezequias@fastcon.com.br
2007-03-27 22:16 ` Re: Regular Expressions Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 18+ messages in thread
From: ezequias@fastcon.com.br @ 2007-03-27 20:01 UTC (permalink / raw)
To: Guy Fraser <guy@incentre.net>; pgsql-sql
Guy,
Could you give me a hand ?
I have a ZipCode table and my address table
I just would like to find out all matches that my zipcode table has where my
address table appears like this:
Elmo Street, 30
I would like my SQL find out all matches we can find 'Elmo', 'Street'.
The commas, spaces and numbers could be forgive.
I hope you could help me
Regards
Ezequias
Em Wed, 21 Mar 2007 14:32:26 -0600
Guy Fraser <guy@incentre.net> escreveu:
>On Wed, 2007-03-21 at 14:37 -0300, Ezequias R. da Rocha wrote:
>> Guy Fraser escreveu:
>> > On Wed, 2007-03-21 at 11:04 -0300, Ezequias R. da Rocha wrote:
>> >
>> >> Hi list,
>> >>
>> >> I would like to know if postgresql has a Regular Expressions (Regex)
>> >> implemented already.
>> >>
>> >> With it we could implement queries like
>> >>
>> >> Select * from myClientes where name = 'E[zs]equias'
>> >>
>> >>
>> > Case Sensitive Regular Match ~
>> > Case Insensitive Regular Match ~*
>> > Negated Case Sensitive Regular Match !~
>> > Negated Case Insensitive Regular Match !~*
>> >
>> > Select * from myClientes where name ~ 'E[zs]equias'
>> >
>> >
>> >> where the result occurs even if the field has Ezequias or Esequias.
>> >>
>> >> Regards
>> >> Ezequias
>> >>
>> >> ---------------------------(end of broadcast)---------------------------
>> >> TIP 2: Don't 'kill -9' the postmaster
>> >>
>> >>
>> Great I am thinking of putting my like to rest. I felt it faster than
>> "like" statement, have you any information about that ?
>>
>
>No I don't know if regular expressions are faster than "LIKE" but
>I think they are more flexible. When developing queries, I usually
>try different methods of matching to find out what works best for
>each circumstance. Some times upper() lower() and substr() with an
>"=" are more effective than other methods.
>
>One of the more powerful features of PostgreSQL is the ability to
>use sub-selects to reduce the time required to process a subset of
>data from a larger volume of data.
>
>Example :
>
>select
> *
>from (
> select
> ss_time,
> ss_date,
> ss_type,
> ss_data
> from
> full_set
> where
> ss_type in ('type_a','type_x')
> ) as sub_set
>where
> upper(ss_data) ~ '[A-Z][0-9][A-Z] ?[0-9][A-Z][0-9]'
>order by
> ss_time,
> ss_date,
> ss_type
>;
>
>
>> Ezequias
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: You can help support the PostgreSQL project by donating at
>>
>> http://www.postgresql.org/about/donate
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
Ezequias Rodrigues da Rocha
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2007-03-21 14:04 Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 14:36 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
2007-03-21 17:37 ` Re: Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 20:32 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
2007-03-27 20:01 ` Re: Regular Expressions ezequias@fastcon.com.br
@ 2007-03-27 22:16 ` Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 18+ messages in thread
From: Richard Broersma Jr @ 2007-03-27 22:16 UTC (permalink / raw)
To: ezequias@fastcon.com.br, Guy Fraser <guy@incentre.net>; pgsql-sql
> Could you give me a hand ?
>
> I have a ZipCode table and my address table
>
> I just would like to find out all matches that my zipcode table has where my
> address table appears like this:
>
> Elmo Street, 30
>
> I would like my SQL find out all matches we can find 'Elmo', 'Street'.
>
select zipcode
from zipzodetable
where address ~ 'Elmo'
and address ~ 'Street';
If the query is too slow I expect that installing the tsearch2 contrib module and using the
tsearch2 type queries would give you want you wanted but in a fraction of the time.
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2007-03-21 14:04 Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 14:36 ` Re: Regular Expressions Guy Fraser <guy@incentre.net>
2007-03-21 17:37 ` Re: Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
@ 2007-03-22 01:35 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 18+ messages in thread
From: Andrew Sullivan @ 2007-03-22 01:35 UTC (permalink / raw)
To: pgsql-sql
On Wed, Mar 21, 2007 at 02:37:07PM -0300, Ezequias R. da Rocha wrote:
> Great I am thinking of putting my like to rest. I felt it faster than
> "like" statement, have you any information about that ?
I think this rather depends on what you're doing.
If you're searching for "like 'blahblah%' or " ~ 'blahblah.*'",
they're AFAIK about the same. When you have a more complicated RE,
though, it might turn out to be a win.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 18+ messages in thread
* Regular Expressions
@ 2018-11-04 19:10 Mark Williams <markwillimas@gmail.com>
2018-11-04 19:20 ` Re: Regular Expressions A. Sasaki <asasaki@gmail.com>
2018-11-04 19:25 ` Re: Regular Expressions David G. Johnston <david.g.johnston@gmail.com>
2018-11-04 19:43 ` RE: Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-05 17:51 ` Re: Regular Expressions Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 4 replies; 18+ messages in thread
From: Mark Williams @ 2018-11-04 19:10 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
If I wanted to search for whole words in a field I would use something like:
Select * from mytable where myfield ~* '(\mtext1\M) | (\mtext2\M)'
This would find all instances of myfield containing either "text1" or
"text2".
I can't figure out how to search myfield for all instances which contain
"text1" AND "text2".
In other words | is the OR operator. What is the AND operator. Tried + and
whilst that executes, it doesn't return matching fields.
Thanks
Mark
__
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
@ 2018-11-04 19:20 ` A. Sasaki <asasaki@gmail.com>
3 siblings, 0 replies; 18+ messages in thread
From: A. Sasaki @ 2018-11-04 19:20 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
‘(*\mtext1\M*\mtext2\M)|(*\mtext2\M*\mtext1\M)’
Thanks,
-Andrew-
> On Nov 4, 2018, at 9:10 AM, Mark Williams <markwillimas@gmail.com> wrote:
>
> If I wanted to search for whole words in a field I would use something like:
>
> Select * from mytable where myfield ~* ‘(\mtext1\M) | (\mtext2\M)’
>
> This would find all instances of myfield containing either “text1” or “text2”.
>
> I can’t figure out how to search myfield for all instances which contain “text1” AND “text2”.
>
> In other words | is the OR operator. What is the AND operator. Tried + and whilst that executes, it doesn’t return matching fields.
>
> Thanks
>
> Mark
>
> __
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
@ 2018-11-04 19:25 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 18+ messages in thread
From: David G. Johnston @ 2018-11-04 19:25 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Sunday, November 4, 2018, Mark Williams <markwillimas@gmail.com> wrote:
>
> I can’t figure out how to search myfield for all instances which contain
> “text1” AND “text2”.
>
> In other words | is the OR operator. What is the AND operator. Tried + and
> whilst that executes, it doesn’t return matching fields.
>
> =====================
>
> Myfield ~* ‘text1’ AND myfield ~* ‘text2’
>
> There is no convenient concept of AND in the sense you want it in Regular
> Expressions and “+” has its own meaning of “one or more of the previous
> item”.
>
> David J.
>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* RE: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
@ 2018-11-04 19:43 ` Mark Williams <markwillimas@gmail.com>
2018-11-04 19:51 ` Re: Regular Expressions David G. Johnston <david.g.johnston@gmail.com>
2018-11-04 19:55 ` Re: Regular Expressions Tom Lane <tgl@sss.pgh.pa.us>
2018-11-04 22:44 ` Re: Regular Expressions A. Sasaki <asasaki@gmail.com>
3 siblings, 3 replies; 18+ messages in thread
From: Mark Williams @ 2018-11-04 19:43 UTC (permalink / raw)
To: 'A. Sasaki' <asasaki@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
Hi Andrew,
Thanks for the reply.
I tried the query, but it produced an error “invalid regular expression: quantifier operand invalid”.
Also, what would be the regular expression if you want to check whether all the words were in the field where you had say 10 words/phrases you wanted to check for?
__
From: A. Sasaki <asasaki@gmail.com>
Sent: 04 November 2018 19:30
To: Mark Williams <markwillimas@gmail.com>
Subject: Re: Regular Expressions
‘(*\mtext1\M*\mtext2\M)|(*\mtext2\M*\mtext1\M)’
Thanks,
-Andrew-
On Nov 4, 2018, at 9:10 AM, Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> > wrote:
If I wanted to search for whole words in a field I would use something like:
Select * from mytable where myfield ~* ‘(\mtext1\M) | (\mtext2\M)’
This would find all instances of myfield containing either “text1” or “text2”.
I can’t figure out how to search myfield for all instances which contain “text1” AND “text2”.
In other words | is the OR operator. What is the AND operator. Tried + and whilst that executes, it doesn’t return matching fields.
Thanks
Mark
__
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-04 19:43 ` RE: Regular Expressions Mark Williams <markwillimas@gmail.com>
@ 2018-11-04 19:51 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 18+ messages in thread
From: David G. Johnston @ 2018-11-04 19:51 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: A. Sasaki <asasaki@gmail.com>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Sunday, November 4, 2018, Mark Williams <markwillimas@gmail.com> wrote:
>
> Also, what would be the regular expression if you want to check whether
> all the words were in the field where you had say 10 words/phrases you
> wanted to check for?
>
> ==============
>
> Consider full text search instead.
>
> Maybe “split_to_array” on spaces and then do something like:
>
> ARRAY[‘term1’, ‘term2’]::text[] && split_to_array(field, ‘ ‘)
>
> David J.
>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-04 19:43 ` RE: Regular Expressions Mark Williams <markwillimas@gmail.com>
@ 2018-11-04 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-11-04 20:02 ` RE: Regular Expressions Mark Williams <markwillimas@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2018-11-04 19:55 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: 'A. Sasaki' <asasaki@gmail.com>; pgsql-sql@lists.postgresql.org
"Mark Williams" <markwillimas@gmail.com> writes:
> Also, what would be the regular expression if you want to check whether all the words were in the field where you had say 10 words/phrases you wanted to check for?
As David said, regular expressions aren't really designed to do that.
Personally I'd do the AND at the SQL level, ie
myfield ~* '\mtext1\M' AND myfield ~* '\mtext2\M' AND ...
You might also take a look at the full text search machinery, which
is probably better suited to this task.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* RE: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-04 19:43 ` RE: Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-04 19:55 ` Re: Regular Expressions Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-11-04 20:02 ` Mark Williams <markwillimas@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Mark Williams @ 2018-11-04 20:02 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: 'A. Sasaki' <asasaki@gmail.com>; pgsql-sql@lists.postgresql.org
Thanks all. Have implemented by way of "AND" for time being. Full text
search on my list of todos!
__
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: 04 November 2018 19:55
To: Mark Williams <markwillimas@gmail.com>
Cc: 'A. Sasaki' <asasaki@gmail.com>; pgsql-sql@lists.postgresql.org
Subject: Re: Regular Expressions
"Mark Williams" <markwillimas@gmail.com> writes:
> Also, what would be the regular expression if you want to check whether
all the words were in the field where you had say 10 words/phrases you
wanted to check for?
As David said, regular expressions aren't really designed to do that.
Personally I'd do the AND at the SQL level, ie
myfield ~* '\mtext1\M' AND myfield ~* '\mtext2\M' AND ...
You might also take a look at the full text search machinery, which is
probably better suited to this task.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-04 19:43 ` RE: Regular Expressions Mark Williams <markwillimas@gmail.com>
@ 2018-11-04 22:44 ` A. Sasaki <asasaki@gmail.com>
2018-11-05 10:21 ` RE: Regular Expressions Mark Williams <markwillimas@gmail.com>
2 siblings, 1 reply; 18+ messages in thread
From: A. Sasaki @ 2018-11-04 22:44 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
I didn’t test it in a DB, sorry ’bout that.
I think others are on the right track here, using SQL for the AND functionality:
‘Select *
FROM [TABLE]
WHERE [regex search 1]
AND [regex search 2]
AND [regex search 3]
....’
Thanks,
-Andrew-
> On Nov 4, 2018, at 9:43 AM, Mark Williams <markwillimas@gmail.com> wrote:
>
> Hi Andrew,
>
> Thanks for the reply.
>
> I tried the query, but it produced an error “invalid regular expression: quantifier operand invalid”.
>
> Also, what would be the regular expression if you want to check whether all the words were in the field where you had say 10 words/phrases you wanted to check for?
>
>
> __
>
> From: A. Sasaki <asasaki@gmail.com>
> Sent: 04 November 2018 19:30
> To: Mark Williams <markwillimas@gmail.com>
> Subject: Re: Regular Expressions
>
> ‘(*\mtext1\M*\mtext2\M)|(*\mtext2\M*\mtext1\M)’
>
> Thanks,
>
> -Andrew-
>
> On Nov 4, 2018, at 9:10 AM, Mark Williams <markwillimas@gmail.com> wrote:
>
> If I wanted to search for whole words in a field I would use something like:
>
> Select * from mytable where myfield ~* ‘(\mtext1\M) | (\mtext2\M)’
>
> This would find all instances of myfield containing either “text1” or “text2”.
>
> I can’t figure out how to search myfield for all instances which contain “text1” AND “text2”.
>
> In other words | is the OR operator. What is the AND operator. Tried + and whilst that executes, it doesn’t return matching fields.
>
> Thanks
>
> Mark
>
> __
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* RE: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-04 19:43 ` RE: Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-04 22:44 ` Re: Regular Expressions A. Sasaki <asasaki@gmail.com>
@ 2018-11-05 10:21 ` Mark Williams <markwillimas@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Mark Williams @ 2018-11-05 10:21 UTC (permalink / raw)
To: 'A. Sasaki' <asasaki@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
No problem. That’s how I have now gone about it.
__
From: A. Sasaki <asasaki@gmail.com>
Sent: 04 November 2018 22:45
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Regular Expressions
I didn’t test it in a DB, sorry ’bout that.
I think others are on the right track here, using SQL for the AND functionality:
‘Select *
FROM [TABLE]
WHERE [regex search 1]
AND [regex search 2]
AND [regex search 3]
....’
Thanks,
-Andrew-
On Nov 4, 2018, at 9:43 AM, Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> > wrote:
Hi Andrew,
Thanks for the reply.
I tried the query, but it produced an error “invalid regular expression: quantifier operand invalid”.
Also, what would be the regular expression if you want to check whether all the words were in the field where you had say 10 words/phrases you wanted to check for?
__
From: A. Sasaki <asasaki@gmail.com <mailto:asasaki@gmail.com> >
Sent: 04 November 2018 19:30
To: Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> >
Subject: Re: Regular Expressions
‘(*\mtext1\M*\mtext2\M)|(*\mtext2\M*\mtext1\M)’
Thanks,
-Andrew-
On Nov 4, 2018, at 9:10 AM, Mark Williams <markwillimas@gmail.com <mailto:markwillimas@gmail.com> > wrote:
If I wanted to search for whole words in a field I would use something like:
Select * from mytable where myfield ~* ‘(\mtext1\M) | (\mtext2\M)’
This would find all instances of myfield containing either “text1” or “text2”.
I can’t figure out how to search myfield for all instances which contain “text1” AND “text2”.
In other words | is the OR operator. What is the AND operator. Tried + and whilst that executes, it doesn’t return matching fields.
Thanks
Mark
__
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Regular Expressions
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
@ 2018-11-05 17:51 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
3 siblings, 0 replies; 18+ messages in thread
From: Andrew Gierth @ 2018-11-05 17:51 UTC (permalink / raw)
To: Mark Williams <markwillimas@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
>>>>> "Mark" == Mark Williams <markwillimas@gmail.com> writes:
Mark> I can't figure out how to search myfield for all instances which
Mark> contain "text1" AND "text2".
I should start by pointing out that (as mentioned by other people) using
regexps is not necessarily the best way to do this, especially not when
dealing with actual words which is what FTS exists for.
But a solution does exist (at least in pg and other regexp engines that
support lookahead assertions):
myfield ~* '^(?=.*\mtext1\M)(?=.*\mtext2\M)'
What this says is: match at the start of the string if (and only if)
both the lookahead assertions succeed; since neither assertion advances
the match, they will find the two specified words regardless of the
order in which they appear. (The trick of using | to search for both
possible orders works for 2 words, but gets unwieldy very quickly with
more; with the assertion method you can handle any number of words.)
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2018-11-05 17:51 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-21 14:04 Regular Expressions Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 14:28 ` Bricklen Anderson <banderson@presinet.com>
2007-03-21 14:36 ` Guy Fraser <guy@incentre.net>
2007-03-21 17:37 ` Ezequias R. da Rocha <ezequias@fastcon.com.br>
2007-03-21 20:32 ` Guy Fraser <guy@incentre.net>
2007-03-27 20:01 ` ezequias@fastcon.com.br
2007-03-27 22:16 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-03-22 01:35 ` Andrew Sullivan <ajs@crankycanuck.ca>
2018-11-04 19:10 Regular Expressions Mark Williams <markwillimas@gmail.com>
2018-11-04 19:20 ` A. Sasaki <asasaki@gmail.com>
2018-11-04 19:25 ` David G. Johnston <david.g.johnston@gmail.com>
2018-11-04 19:43 ` Mark Williams <markwillimas@gmail.com>
2018-11-04 19:51 ` David G. Johnston <david.g.johnston@gmail.com>
2018-11-04 19:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-11-04 20:02 ` Mark Williams <markwillimas@gmail.com>
2018-11-04 22:44 ` A. Sasaki <asasaki@gmail.com>
2018-11-05 10:21 ` Mark Williams <markwillimas@gmail.com>
2018-11-05 17:51 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox