Contact Us

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.

StreetNumber AS VARCHAR(20)
StreetName   AS VARCHAR(50)

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.

BEGIN
SET NOCOUNT ON;

UPDATE [Address]
        SET StreetNumber = CASE 
            WHEN ISNUMERIC(LEFT(LTRIM(Address), CHARINDEX(' ', LTRIM(Address)))) = 1 
                THEN LEFT(LTRIM(Address), CHARINDEX(' ', LTRIM(Address)))
            ELSE NULL
            END ,
        StreetName = CASE 
            WHEN ISNUMERIC(LEFT(LTRIM(Address), CHARINDEX(' ', LTRIM(Address)))) = 1 
                THEN LTRIM(RTRIM(SUBSTRING(LTRIM(Address), CHARINDEX(' ', LTRIM(Address)) + 1, LEN(LTRIM(Address)))))
            ELSE LTRIM(RTRIM(Address))
            END 
END

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.

 

Go Back to Our: Main Blog

Written by: Doron Farber - The Farber Consulting Group, Inc

There are 2 comments for "Splitting an Address into Street Number and Street Name in SQL"

avatar
By Leandro Tami | November 07,2013

Problem is, some countries put the street number after the street name, for example Calle Falsa 123. Some other cases are more difficult: 9 de Julio 1234 may be understood as 9 of "de Julio 1234" street, or 1234 of the 9 de julio street (this is the correct interpretation).

Reply
avatar
By | November 08,2013

Hi Leandro,

Thanks for your feedback. The data structure that I was working was the way it is done in USA. If you have some sample data we can look into it.

Regards,

Doron

The Farber Consulting Group, Inc.

Reply

Add your comment