Re: Searching addresses

What you really need is a function that normalizes a street address. Then you could use the address normalization function to create a function based index on Address_line. Your query would look something like the following.

select *
from myTable
where f_normalAddress(Address_line) = f_normalAddress('100 N. Columbia Pky')

"Ed prochak" <ed.prochak_at_alltel.com> wrote in message news:e51b160.0202281445.3fcb4f76_at_posting.google.com...
> How are your addresses stored in the database. HOPEFULLY they are not
> stored all in one4 attribute as you seem to imply. But if they are,
> consider adding a couple other fields and indices.
>
> One is a search field created by stripping all non-alphabetic
> characters (spaces, digits, punctuation) and upper casing all
> remaining letters. Make an index on that field. Then you search by
> doing the same thing to the input address ( UPPER(TRANSLATE(...)) =
> SRCH_ALIAS_ADDRESS1 ).
>
> Another is to build the search field by applying some common aliases
> to you address (a standalone 'N' becomes 'NORTH', 'S' becomes
> 'SOUTH'...).
>
> Build a hash function that matches well with your address data and
> build a search column using that. (search column =
> yourHashFunc(Address_Line), then apply yourHashFunc() on the user
> input too.)
>
> Really, unless you can standardize the addresses in your table (split
> into multiple fields like House_number, Prefix_drctn,
> Street_name,...), you cannot hope to make headway on a good search
> algorithm. Look into the USPS addressing standard for some good ideas
> on what fields you need (Publication 28).
>
>
> "spare_brain" <spare_brain_at_yahoo.com> wrote in message
news:<a5k30u$mtg18_at_kcweb01.netnews.att.com>...
> > Folks,
> >
> > I am trying to find examples of SQL snippets that shows an address
search as
> > follows. Such searches are very common in map searching.
> >
> > For example, the "Address_Line" attribute has entries of the form
> >
> > 100 N. Columbia Pky
> > 100 N. Columbia Parkway
> > 100 North Columbia Pky
> > 100 North Columbia Parkway
> > 222 North Columbia Pky
> >
> > Now, my search query should be such that when the user enters "N Col
Pky",
> > it should fetch all of these entries.
> >
> > Could someone provide some pointers, and/or other leads into finding
similar
> > examples?
> >
> > Any help would be great and save a SQLed soul.....so Thanks!
> >
> > sb
> > -----------------------------
> > "..Life is like a database query, you never know what'cha gonna get.."
Received on Fri Mar 01 2002 - 09:36:49 CST