Re: Searching addresses

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 Thu Feb 28 2002 - 16:45:34 CST