SQL Cross Platform Interoperability: The Proper Function
February 7, 2007 Michael Sansoterra
|
The code accompanying this article is available for download This tip has one very easy point: There’s lot of reusable SQL code out there, and System i (AS/400) DB2 developers can take advantage of it. Even though SQL’s promise of cross platform independence has never materialized, there are still enough similarities to make conversion between platforms worthwhile. All it takes is a little practice. This tip assumes you have some knowledge of writing SQL modules (triggers, functions or stored procedures in the SQL language). If you’re new to this arena, review the Control State section in the IBM SQL Reference guide to get a handle on the basic elements of programming entirely in SQL. SQL control statements are simply statements (as in any other computer language) that control the flow of logic in a program: conditional and looping statements, error handling, etc. The seeds for this tip started a few years ago when I was working on a SQL Server project. I was merging address data from two databases into one. Unfortunately, one database contained mixed case (actually “proper” case) information and the other uppercase information. The customer, of course, wanted to have everything in the resulting database in proper case. Proper case is similar to title case in that the first letter in each word is capitalized as shown here:
This Sentence Is Written In Proper Case. THIS SENTENCE IS WRITTEN IN UPPER CASE. It was time to go home, but I was able to wow the customer by having the case conversion done in a manner of minutes. How did I do that? Easy, I searched the Internet and found a pre-written user-defined function (written in SQL Server’s T-SQL) that converted a text string to proper case. Problem solved. In case you’re not familiar with user-defined functions, they’re basically little pieces of user-written code that can accept zero or more inputs, perform logic on those inputs and return a single output (similar to an RPG subprocedure). Here’s an example of how to use the Proper user-defined function in SQL Server: Select Proper(‘2431 BROADWAY AVE’) Returns: 2431 Broadway Ave Just a little while ago, I had a similar issue come up for a DB2 database. I needed to keep names in proper case in an employee table instead of upper case. I knew a scalar UDF to convert a string to proper case would do the trick again. My first thought was to either find one written for DB2 or to write one from scratch. But why reinvent the wheel? I whipped out the SQL Server version and in under 15 minutes I had the routine converted from T-SQL to DB2 SQL. (It would have gone even quicker except I first decided to test with DB2 for Windows, which always manages to cause me to stumble at least once per session!) The code accompanying this article contains the DB2 code for user-defined function “Proper”. Unfortunately, for the sake of giving credit to the original T-SQL author, I don’t know who wrote the original code, but I did manage to find a Web site that still publishes the example. For reference and comparison, click here to see the original T-SQL version of the code. Here are a few of the things that were done to convert the code:
When working with DB2’s modules, do not limit your Internet searches for DB2 code. You can start looking for DB2 specific code, but if you can’t find it, someone has probably written code for another database platform that can easily be ported to DB2. As a developer, the best thing about the Internet for me has been code samples and re-useable code. Hey, why do the work if someone else has already done it (or most of it) for you?
Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.
|
