Correct postal addresses in SQL Server with YAddress User Defined Function (UDF). This UDF can be called directly from SQL queries, stored procedures, and ETL packages.

Setup

Download YAddress UDF Binaries

and follow installation steps in SQL Setup Script.sql.

Source code for YAddress SQL Server UDF: https://github.com/YAddress/YAddressUDF

Usage

ProcessAddress is a table-valued UDF. Executing the following T-SQL query illustrates the values returned by YAddress:

SELECT * FROM ProcessAddress('506 Fourth Avenue Unit 1', 'Asbury Prk, NJ', NULL)

ErrorCode: 0
ErrorMessage:
AddressLine1: 506 4TH AVE APT 1
AddressLine2: ASBURY PARK, NJ 07712-6086
Number: 506
PreDir:
Street: 4TH
Suffix: AVE
PostDir:
Sec: APT
SecNumber: 1
City: ASBURY PARK
State: NJ
Zip: 7712
Zip4: 6086
County: MONMOUTH
CountyFP: 25
CensusTract: 1015
CensusBlock: 8070.03
Latitude: 40.223571
Longitude: -74.005973
GeocodePrecision: 5

SQL Spatial Geography

ProcessAddress returns address location as two floating point values of latitude and longitude. To convert them to SQL Server spatial type Geography:

SELECT Location = GEOGRAPHY::Point(Latitude, Longitude, 4326)
FROM ProcessAddress('506 Fourth Avenue Unit 1', 'Asbury Prk, NJ', NULL)