Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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]


Address Results

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.

To use this function, please make sure that the Ole Automation Procedures are enabled:

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]


RegExReplace Results

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]

PATINDEX FUNCTION

By using a couple of different T-SQL functions, the special characters from a mailing list can easily be removed.