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.