SQL Server Data and Overnight Mailing List Formatting
How to use T-SQL functions, views, and stored procedures to generate a FedEx compatible mailing list file
This tutorial discusses the various T-SQL code to produce a FedEx style data to create a file for upload to either the Federal Express or USPS Express Mail websites.
Overnight Mailing List in T-SQL Background
Formatting upload files for mailing with Federal Express or Express Mail consists of very specific criteria. The file must consist of 7 columns: Company, Contact, Addr1, Addr2, City, State and Zip Code. The columns must not exceed 30 characters in length. No special characters are permitted (such as: - , . * & ). The zip code must be in a 5 digit format only. The state must be in the postal 2 character format.
Using the AdventureWorks database, consider the following query:
SELECT p.Name as Company,
FirstName + CASE WHEN Len(IsNull(MiddleName,'')) = 0 THEN '' ELSE ' '
+ MiddleName END + ' ' + LastName as Contact,
a.[AddressLine1],a.[AddressLine2],a.[City],
sp.[StateProvinceCode],a.[PostalCode]
from Purchasing.Vendor p
INNER JOIN Purchasing.VendorAddress pv
ON p.VendorID = pv.VendorID
INNER JOIN Purchasing.VendorContact pc
ON pc.VendorID = pv.VendorID
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = pc.[ContactID]
INNER JOIN [Person].[Address] a
ON pv.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
Although the normalized tables in this database get most of the data into the correct column format, the issues with special characters and length still exist. Length can be taken care of simply by using the Substring function and limiting the string to 30 characters. The special character task is a little trickier.
Rather than attempt to copy and paste this data into Excel and do a Find and Replace on all of the commas, periods, asterisks, etc., there are a couple of T-SQL solutions that will quickly remove all but the numbers and letters from the data.
T-SQL Functions to Remove Special Characters
One function using the VBScript.RegExp object, which allows you to use Regular Expressions, and sp_OA* sprocs (OLE automation stored procedures) was RegExReplace which I found here: TSQL Regular Expression Workbench @ simple-talk.com
.
The function takes 5 arguments: @pattern, @replacement, @Subject, @global, @Multiline.
The @pattern is the Regular Expression that you want to use. @replacement is the string that you want to replace any 'found' items with. @Subject in this application will be our field names from our query. @global and @multiline are bit fields which will be set to 1. You can then use the Regular Expression '(\W)+' to extract the special characters in the data fields.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures',1;
GO
RECONFIGURE;
GO
Here is the usage in our previous query:
SELECT Substring(dbo.regexReplace('(\W)+', ' ', p.Name,1,1),1,30) as Company,
dbo.regexReplace('(\W)+', ' ',FirstName,1,1) + CASE WHEN Len(IsNull(MiddleName,'')) = 0 THEN '' ELSE ' '
+ dbo.regexReplace('(\W)+', ' ',MiddleName,1,1) END
+ ' ' +
dbo.regexReplace('(\W)+', ' ',LastName,1,1) as Contact,
dbo.regexReplace('(\W)+', ' ',a.[AddressLine1],1,1) as Addr1,
dbo.regexReplace('(\W)+', ' ',a.[AddressLine2],1,1) as Addr2,
a.[City],sp.[StateProvinceCode],a.[PostalCode]
FROM Purchasing.Vendor p
INNER JOIN Purchasing.VendorAddress pv
ON p.VendorID = pv.VendorID
INNER JOIN Purchasing.VendorContact pc
ON pc.VendorID = pv.VendorID
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = pc.[ContactID]
INNER JOIN [Person].[Address] a
ON pv.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
Another function that will work can be constructed by using the PATINDEX builtin function to locate the position of any character that is not a number or an upper\lower case letter and REPLACE it with ''.
CREATE FUNCTION
[dbo].[myReplace]
(
@address VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @pos INT
SELECT @pos = PATINDEX('%[^a-zA-Z0-9 ]%', @address)
WHILE @pos > 0
BEGIN
SELECT @address = REPLACE(@address, SUBSTRING(@address, @pos, 1), '')
SELECT @pos = PATINDEX('%[^a-zA-Z0-9 ]%', @address)
END
RETURN @address
END
GO
Again, the usage of the function in our original query:
SELECT TOP 10 Substring(dbo.myReplace(p.Name),1,30) as Company,
Substring(dbo.myReplace(FirstName) + CASE WHEN Len(IsNull(MiddleName,'')) = 0
THEN '' ELSE ' '
+ dbo.myReplace(MiddleName) END + ' '
+ dbo.myReplace(LastName),1,30) as Contact,
dbo.myReplace(a.[AddressLine1]) as Addr1,
dbo.myReplace(a.[AddressLine2]) as Addr2,
a.[City],sp.[StateProvinceCode] as [State],a.[PostalCode] as ZipCode
FROM Purchasing.Vendor p
INNER JOIN Purchasing.VendorAddress pv
ON p.VendorID = pv.VendorID
INNER JOIN Purchasing.VendorContact pc
ON pc.VendorID = pv.VendorID
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = pc.[ContactID]
INNER JOIN [Person].[Address] a
ON pv.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
By using a couple of different T-SQL functions, the special characters from a mailing list can easily be removed.