About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Monday, November 14, 2005

Out of Space

    Sometime ago I found a post in which somebody was annoyed of writing code like this: DECLARE @zipcode char(5) SET @zipcode = '90' SELECT CASE len(@zipcode) WHEN 1 THEN '0000' + @zipcode WHEN 2 THEN '000' + @zipcode WHEN 3 THEN '00' + @zipcode WHEN 4 THEN '0' + @zipcode WHEN 5 THEN @zipcode END     I remembered I met the same situation in a project I worked long time ago, but instead of wroting a line of code for each case, I prefered to write a simple function, which used the Space and Replace built-in functions to add first a number of spaces and then replace them with a defined character. Maybe somebody will argue about the performance of such a function, but I prefer in many cases the reusability against performance, when the difference is not so big. Additionally this enhances code’s readability.     So, here is the function: CREATE FUNCTION dbo.FillBefore( @target varchar(50) , @length int , @filler char(1)) /* Purpose: enlarges a string to a predefiend length by puting in front of it the same character Parameters: @target varchar(50) - the string to be transformed , @length int - the length of output string , @filler char(1) - the character which will be used as filler Notes: the function works if the length of @length is greater or equal with the one of the @target, otherwise will return a NULL Sample: SELECT dbo.FillBefore('1234', 5, '0') SELECT dbo.FillBefore('1234', 10, ' ') SELECT dbo.FillBefore(1234, 10, ' ') SELECT dbo.FillBefore(Cast(1234 as varchar(10)), 10, '0') SELECT dbo.FillBefore(NULL, 10, '0') */ RETURNS varchar(50) AS BEGIN RETURN Replace(Space(@length-len(IsNull(@target, ''))), ' ', @filler) + IsNull(@target, '') END and how it can be used: SELECT dbo.FillBefore('1234', 5, '0') SELECT dbo.FillBefore('1234', 10, ' ') SELECT dbo.FillBefore(1234, 10, ' ') SELECT dbo.FillBefore(Cast(1234 as varchar(10)), 10, '0')     Another situation, when Space function was useful, was when I had to save the result of a query to a text file in fixed width format. It resumes to the same logic used in FillBefore function, except the spaces must be added at the end of the string. For this I just needed to modify the order of concatenation and create a new function. CREATE FUNCTION dbo.FillAfter( @target varchar(50) , @length int , @filler char(1)) /* Purpose: enlarges a string to a predefiend length by adding at the end the same character Parameters: @target varchar(50) - the string to be transformed , @length int - the length of output string , @filler char(1) - the character which will be used as filler Notes: the function works if the length of @length is greater or equal with the one of the @target, otherwise will return a NULL Sample: SELECT dbo.FillAfter('1234', 5, '0') SELECT dbo.FillAfter('1234', 10, ' ') SELECT dbo.FillAfter(1234, 10, '0') SELECT dbo.FillAfter(Cast(1234 as varchar(10)), 10, '0') SELECT dbo.FillAfter(NULL, 10, '0') */ RETURNS varchar(50) AS BEGIN RETURN IsNull(@target, '') + Replace(Space(@length-len(IsNull(@target,''))), ' ', @filler) END Notes: 1.    In SQL Server 2005, the output of a query can be saved using a DTS Package with fix format directly to a text file using ‘fixed width’ (the columns are defined by fix widths) or ‘ragged right’ (the columns are defined by fix widths, except the last one which is delimited by the new line character) format for the destination file. 2.    To document or not to document. I know that many times we don't have the time to document the database objects we created, but I found that a simple comment saved more time later, even if was about my or other developer's time. 3.    If is really needed to use the CASE function, is good do a analysis of the data and put as first evaluation the case with the highest probability to occur, as second evaluation the case with the second probability to occur, and so on. The reason for this is that each option is evaluated until the comparision operation evaluates to TRUE.     For example, if the zip codes are made in most of the cases of 5 characters, then it makes sense to put it in the first WHEN position.

No comments: