Splitting an Address into Street Number and Street Name in SQL

SQL Searches

An approach to optimizing search routines for big data

Platform

MS SQL Server

The Concept

Attempting to improve search performance when querying on multiple fields (such as First Name, Last Name, Address, City and State) can sometimes could be a bit tricky. Performance improvements can be achieved by splitting the address into separate Street Number and Street Name fields.

Call us for Free Consultation for Remote DBA services at our SQL Consulting Firm at: 732-536-4765

Say you are looking for "109 Lupine Ln." but the search yields no result. If you enter only the Street Name as Lupine Ln. then you will have a chance to find the actual address as "106 Lupine Ln." Since you simply entered the wrong street number this approach helps locate the record you are looking for. You may also desire to use "contains" or wildcard searches to help narrow your search results when you are not able to enter an exact match.

From that reason it is highly recommended to split the address field into StreetNumer and StreetName.  You will need to create these additional fields in the Address table.

The code below will attempt to find one space between the Street Number and the Street Name in the Address filed. If found it will be spilited into 2 fields.

There could an Address like PO Box 200 or PO BOX 500 Pinon Ct and this case it will be copied into the AddressName field.

Check our Custom Software Development Services.

You could also expand you algorithms to include splitting the address further Apartment and Street Direction (N , E , S or W). In most cases Street Number and Street Name are sufficient.

If you have need of processing large volumes of address data you may be interested in my article Processing hundreds of millions of records got much easier where I use batch processing approach.

Written by Doron Farber - The Farber Consulting Group

Doron Farber - The Farber Consulting Group I started to develop custom software since 1981 while using dBase III from Aston Tate. From there I moved to FoxBase and to FoxPro and ended up working with Visual FoxPro until Microsoft stopped supporting that great engine. With the Visual FoxPro, I developed the VisualRep which is Report and Query Engine. We are also a dot net development company, and one of our projects is a screen scrapping from different web sites.

Comments