Thread: Regular Expressions
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
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
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
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
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_datafrom full_setwhere ss_type in ('type_a','type_x')) as sub_set
whereupper(ss_data) ~ '[A-Z][0-9][A-Z] ?[0-9][A-Z][0-9]'
order byss_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
>
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
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
> 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.
Richards and List,
Now I find out the 'similar to' statement where I can do such search, but I
must still parse all substrings.
Here is my stage:
Select * from myAddressTable where address similar to ('%(ELMO|ST.|258)%')
But I still need to Separete all strings.
Could someone help me in this crusade ?
Regards
Ezequias
Em Tue, 27 Mar 2007 15:16:10 -0700 (PDT) Richard Broersma Jr <rabroersma@yahoo.com> escreveu:
>> 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.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
--
Ezequias Rodrigues da Rocha
> Select * from myAddressTable where address similar to ('%(ELMO|ST.|258)%')
>
> But I still need to Separete all strings.
What is it that you are trying to achieve? What string would you like to seperate?
Regards,
Richard Broersma Jr.