Writing a UDF to Generate State Abbreviations
Many companies adhere to strict standardization and business rules when storing their data. For example, where I work, we interchange data with a company that uses 2-character state abbreviations. The State field in its database is char(2) and won't accept anything but a correctly formatted state code. The problem is that we receive daily bulk spreadsheet-file imports from clients that sometimes use a state code and sometimes spell out the state name. As the DBA, I could change our State field data type to char(2) and require all our clients to comply. But our job is to make our clients' lives easier, not more complicated. Even with such a requirement, clients could still make mistakes—or simply rebel.
Using an UPDATE statement with the CASE function to change existing values in the database is easy. But we really needed a way to automatically scrub the data before we imported it into the database. With that goal in mind, I created a user-defined function (UDF) called statecode() on our SQL Server 2000 system. Listing 3 shows the function. Now, I can simply alter the SQL code in my Data Transformation Services (DTS) address-import package as follows:
SELECT prop_id, street_num, street_name, apt, city, dbo.statecode(state) AS
state, zip
FROM [Sheet1$]
and the State value is automatically formatted correctly.
—Cade Bryant
cadebryant@hotmail.com
Importing DB2 Dates into SQL Server
Our site often uses 0001-01-01 on DB2 OS390 as low date instead of NULL. We've developed an ANSI SQL statement, which Listing 4 shows, that we use in a Data Transformation Services (DTS) package to import into SQL Server any DB2 dates earlier than January 1, 1753, and assign those dates a value of any date value greater than or equal to 01/01/1753. This approach, which uses a DB2 Connect ODBC call from SQL Server to DB2 OS390, precludes having to programmatically massage the data on the mainframe, use FTP to send the data to SQL Server, then use the bulk copy program (bcp) to load the data. You might find other uses for this SQL code in handling data exchange between different data types and databases.
—Ron Eureka
ron.eureka@antaressolutions.com