Using User Defined Functions to Pre-Format Fields

Some DBAs would cringe at what I’m about to tell you, but I often use T-SQL to format my data prior to presenting it in a report. Many experts feel that data formatting should always be done in the presentation layer. There are a couple of reasons why I do this.

1. I re-use as much code as I can. I create base templates for most of the main modules in M2M (Sales, Purchasing, Accts Payable, Etc) and these templates include the formatting code. This results in a professional, standardized image to my reports.
2. I report using many different programs as my presentation layer and I want to re-use my scripts for all of them.

Consider the following script:

Select fsono, fenumber, frelease, fpartno from m2mdata50..sorels

The first few rows of data from the M2M educational database return:

000001	001	000	CP2080 
000001	002	000	FF204W 
000002	001	000	WF201R 
000002	001	001	WF201R 
000002	001	002	WF201R 
000002	001	003	WF201R 
000002	001	004	WF201R

Do you notice the distraction of the leading zeros? When searching for a sales order number, does anyone really enter “000003”? These are a problem in most of the modules of M2M. As I mentioned before, many DBAs feel that you should remove them via formatting in the presentation program of your choice. However, I regularly create reports in SQL Reporting Services, Crystal Reports, Visual FoxPro, and Excel. This means that if I don’t pre-format with T-SQL, I will have to adapt the presentation later manually, which and the technique will be different for each reporting media. This is time consuming and error prone.

As always, please read my standard disclaimer before implementing any code from my site. Using T-SQL, you could strip the preceding zeros like this:

select substring(fsono, patindex('%[^0]%',fsono), 10) SONo,
substring(fenumber, patindex('%[^0]%',fenumber), 10) SOItemNo,
substring(frelease, patindex('%[^0]%',frelease), 10) Release,
fpartno PartNo from m2mdata50..sorels

Which yields this:

1	1	000	CP2080 
1	2	000	FF204W 
2	1	000	WF201R 
2	1	1	WF201R 
2	1	2	WF201R 
2	1	3	WF201R 
2	1	4	WF201R

That works, but it’s too wordy for me. A better option is to use a User Defined Function (UDF) to clean up the data. I’ve demonstrated how to write these in the past.

Consider the following:

CREATE FUNCTION RemoveZeros(@ZeroField VARCHAR(25))
  RETURNS VARCHAR(12)
AS
  BEGIN
    DECLARE @ZerosRemoved   VARCHAR(12)
    DECLARE @Length   INT
    SET @length = len(@ZeroField)
    SELECT @ZerosRemoved =
             CASE
               When @ZeroField = '000'
               then
                 'M'
               WHEN @Length > 0
               THEN
                 substring(
                   @ZeroField,
                   patindex(
                     '%[^0]%',
                     @ZeroField),
                   @Length)
               ELSE
                 ''
             END
    RETURN @ZerosRemoved
  END

Which produces output like this:

1	1	M	CP2080 
1	2	M	FF204W 
2	1	M	WF201R 
2	1	1	WF201R 
2	1	2	WF201R 
2	1	3	WF201R 
2	1	4	WF201R

Can you see the possibilities? You can use this UDF in other modules such as shipping and invoicing as well. It works particularly well in shipping because the item numbers are 6 digits. Have any of you ever had a shipper with a million line items on it?

Select fshipno, fitemno, fpartno from m2mdata50..shitem
 
000001	000001	CP2080 
000002	000001	FF204W 
000003	000001	L20100 
000003	000002	L20200 
000004	000001	EF120100P

Becomes:

Select dbo.removezeros(fshipno) ShipNo,
       dbo.removezeros(fitemno) ItemNo,
       fpartno PartNo
from m2mdata50..shitem
 
1	1	CP2080 
2	1	FF204W 
3	1	L20100 
3	2	L20200 
4	1	EF120100P

The performance impact is negligible, and the both the script and the output are well formatted.

What other UDF’s do you think would be useful for M2M reporting?