Standardized Street Address Using T-SQL

Article
02/07/2014

Question

_{Friday, February 7, 2014 8:01 PM}

I have an urgent requirement to standardize the Street Name. A fixed list of street suffixes are determined to be used.

I am just curious if this is possible using T-SQL or I have to go for CLR.

We are using SQL Server 2012.

Appreciate your input on this!

Thanks!

Declare @Streets Table(StreetName Varchar(100))

Insert Into @Streets
Values ('35 Don Valley Boulevard') ,
       ('Jhonson Street South'),
       ('Mayor Margarith Drive West') ,
       ('2 Avenue Road'),
       ('Park Avenue North'),
       ('35 Park Avenue'),
       ('1990 Bay Street'),
       ('4 Hudson Bay'),
       ('5 North Drive')


Declare @Suffix Table (FullName Varchar(50),
                       ShortName varchar(2) )

Insert Into @Suffix
Values('Drive','DR'),
      ('Boulevard','BV'), 
      ('Street','ST'), 
      ('Bay','BY'), 
      ('Avenue','AV'), 
      ('Road','RD'), 
      ('East','E'),
      ('West','W'),
      ('North','N'),
      ('South','S')




--Expected Output
--35 Don Valley BV
--Johnson ST S
--Mayor Margarith DR W 
--2 Avenue RD
--Park AV N
--35 Park AV
--1990 Bay ST
--4 Hudson BY
--5 North DR

All replies (10)

_{Friday, February 7, 2014 8:12 PM ✅Answered | 2 votes}

Send your list the US Post Office and have them do it for you. This is extremely complicated and requires full knowledge of the addresses.

https://ribbs.usps.gov/index.cfm?page=aec

_{Friday, February 7, 2014 8:33 PM ✅Answered | 1 vote}

You will never be able to do that 100%.

Take for example:

1234 W Avenue E

In this case "Avenue E" is an actual street name.

1234 Bay Circle Road

Without a full list of actual streets, it is impossible to break down all addresses in the way you describe.

The post office service will actually return the correct information back in the file. See field 17 the return file in: https://ribbs.usps.gov/aec/documents/tech_guides/AEC_AECII_UG.PDF

_{Friday, February 7, 2014 8:23 PM}

Thanks Tom!

Actually the Street Addresses we get from Google Geocode and they are correct. However, we have been asked to format street name as per the standard of Third Party applications(Each has its own format so thinking about a suffix table with additional column about their information) where we feed our data.

Agreed, It is complicated.

_{Friday, February 7, 2014 8:24 PM | 1 vote}

I agree with Tom even though I just wrote a recursive solution. In my solution all words which are suffixes are replaced which is not the case in your output for the 2 Avenue Rd. It will be a bit more tricky to fix that as well.

DECLARE @Streets TABLE (StreetName VARCHAR(100))

INSERT INTO @Streets
VALUES ('35 Don Valley Boulevard')
    ,('Jhonson Street South')
    ,('Mayor Margarith Drive West')
    ,('2 Avenue Road')
    ,('Park Avenue North')
    ,('35 Park Avenue')
    ,('1990 Bay Street')
    ,('4 Hudson Bay')
    ,('5 North Drive')

DECLARE @Suffix TABLE (
    FullName VARCHAR(50)
    ,ShortName VARCHAR(2)
    )

INSERT INTO @Suffix
VALUES (
    'Drive'
    ,'DR'
    )
    ,(
    'Boulevard'
    ,'BV'
    )
    ,(
    'Street'
    ,'ST'
    )
    ,(
    'Bay'
    ,'BY'
    )
    ,(
    'Avenue'
    ,'AV'
    )
    ,(
    'Road'
    ,'RD'
    )
    ,(
    'East'
    ,'E'
    )
    ,(
    'West'
    ,'W'
    )
    ,(
    'North'
    ,'N'
    )
    ,(
    'South'
    ,'S'
    );

WITH cte
AS (
    SELECT S.StreetName
        ,0 AS [Level]
        ,replace(' ' + S.StreetName + ' ', ' ' + Sfx.FullName + ' ', ' ' + Sfx.ShortName + ' ') AS CleanedStreet
        ,Sfx.FullName
        ,Sfx.ShortName
    FROM @Streets S
    LEFT JOIN @Suffix Sfx ON ' ' + S.StreetName + ' ' LIKE '% ' + Sfx.FullName + ' %'
    
    UNION ALL
    
    SELECT S.StreetName
        ,S.[Level] + 1 AS [Level]
        ,replace(' ' + S.CleanedStreet + ' ', ' ' + Sfx.FullName + ' ', ' ' + Sfx.ShortName + ' ') AS CleanedStreet
        ,Sfx.FullName
        ,Sfx.ShortName
    FROM cte S
    INNER JOIN @Suffix Sfx ON ' ' + S.CleanedStreet + ' ' LIKE '% ' + Sfx.FullName + ' %'
    )
    ,cte1
AS (
    SELECT *
        ,row_number() OVER (
            PARTITION BY StreetName ORDER BY [Level] DESC
            ) AS Rn
    FROM cte
    )
SELECT StreetName
    ,CleanedStreet
FROM cte1
WHERE Rn = 1
ORDER BY StreetName

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

_{Friday, February 7, 2014 8:25 PM | 1 vote}

Declare @Streets Table(StreetName Varchar(100))

Insert Into @Streets
Values ('35 Don Valley Boulevard') ,
       ('Jhonson Street South'),
       ('Mayor Margarith Drive West') ,
       ('2 Avenue Road'),
       ('Park Avenue North'),
       ('35 Park Avenue'),
       ('1990 Bay Street'),
       ('4 Hudson Bay'),
       ('5 North Drive')


Declare @Suffix Table (FullName Varchar(50),
                       ShortName varchar(2) )

Insert Into @Suffix
Values('Drive','DR'),
      ('Boulevard','BV'), 
      ('Street','ST'), 
      ('Bay','BY'), 
      ('Avenue','AV'), 
      ('Road','RD'), 
      ('East','E'),
      ('West','W'),
      ('North','N'),
      ('South','S')

select replace(StreetName,RIGHT(StreetName,CHARINDEX(' ', reverse(StreetName))),concat(' ',sf.shortname)) from @Streets st
inner join @Suffix sf on RTRIM(LTRIM(RIGHT(st.StreetName,CHARINDEX(' ', reverse(st.StreetName)))))=sf.FullName

--Prashanth

_{Friday, February 7, 2014 8:45 PM | 1 vote}

We use ACCUZIP software to validate address information based on USPS services. I don't think any other vendors can define standards related to address.

_{Friday, February 7, 2014 8:54 PM}

You will never be able to do that 100%.

Take for example:

1234 W Avenue E

In this case "Avenue E" is an actual street name.

Or

1234 Bay Circle Road

Without a full list of actual streets, it is impossible to break down all addresses in the way you describe.

The post office service will actually return the correct information back in the file. See field 17 the return file in: https://ribbs.usps.gov/aec/documents/tech_guides/AEC_AECII_UG.PDF

Clearly frustrating :(....thanks!

_{Friday, February 7, 2014 8:55 PM}

We use ACCUZIP software to validate address information based on USPS services. I don't think any other vendors can define standards related to address.

Not an option but Thanks!

_{Friday, February 7, 2014 8:56 PM}

Declare @Streets Table(StreetName Varchar(100))

Insert Into @Streets
Values ('35 Don Valley Boulevard') ,
       ('Jhonson Street South'),
       ('Mayor Margarith Drive West') ,
       ('2 Avenue Road'),
       ('Park Avenue North'),
       ('35 Park Avenue'),
       ('1990 Bay Street'),
       ('4 Hudson Bay'),
       ('5 North Drive')


Declare @Suffix Table (FullName Varchar(50),
                       ShortName varchar(2) )

Insert Into @Suffix
Values('Drive','DR'),
      ('Boulevard','BV'), 
      ('Street','ST'), 
      ('Bay','BY'), 
      ('Avenue','AV'), 
      ('Road','RD'), 
      ('East','E'),
      ('West','W'),
      ('North','N'),
      ('South','S')

select replace(StreetName,RIGHT(StreetName,CHARINDEX(' ', reverse(StreetName))),concat(' ',sf.shortname)) from @Streets st
inner join @Suffix sf on RTRIM(LTRIM(RIGHT(st.StreetName,CHARINDEX(' ', reverse(st.StreetName)))))=sf.FullName

--Prashanth

Thanks Prashant!