Re: Regular Expressions

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
>