Re: Regular Expressions - Mailing list 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