Padding strings in TSQL using a generic UDF

Introduction

One of my project team members came up with a requirement, wherein she was developing an SSIS package to generate text file as output, the source is an MS-SQL Server 2005 database, and she was using a T-SQL query to read data from the data source. The text file should be a fixed length text file with various lengths for various fields/columns. Also, for various fields, there were various filler patterns. For instance, if it is an amount field, the length is 9 characters with 0 as the filler character appended at the beginning of the field data output string. For string fields, the filler would be appended at the end of the string with white spaces, and so on.

The T-SQL query should return data in accordance to the requirement.

Solution

I worked on the problem and developed a generic UDF (User Defined Function) that would take in the filler character, the length of the output string, the position for the filler, and the actual data value as input, and as output, it would return a string with the desired length and appended with the filler.

Explanation of the solution

The UDF - fnGetPaddedString

CREATE FUNCTION dbo.fnGetPaddedString 
(
 -- Add the parameters for the function here
 @intLength int, --The length for the output string
 @strPaddingSide varchar(1), --L for Left Padding and R for Right Padding
 @strPadWith varchar(1), --Character to pad with
 @strToPad varchar(250)
)
RETURNS varchar(1000)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result varchar(1000)
 
 if @strPaddingSide = 'L'
  Select @Result = REPLICATE(@strPadWith, @intLength -  
                   LEN(ISNULL(@strToPad,''))) + ISNULL(@strToPad, '')
 else
  Select @Result = ISNULL(@strToPad, '') + REPLICATE(@strPadWith, 
                   @intLength -  LEN(ISNULL(@strToPad,'')))
 RETURN @Result
END
GO

In the UDF above, you will find that there are four input parameters:

1. intLength – The total length of the string.
2. strPaddingSide – The position where the filler character is to be placed: L for padding at the beginning of the string and R for appending at the end of the string.
3. strPadWith – The filler character.
4. strToPad – The actual string to be appended with the filler.

The output would be the padded string with left padding or right padding with the specified character.

Example:

SELECT cast (dbo.fnGetPaddedString(10, 'L', '@', 'Rob') as varchar(10)) AS [Name]

The above T-SQL would generate a record with 10 characters length with “@” as the filler character in front of the string. The output would look like “@@@@@@@Rob”.

Conclusion

So, we can now use the generic UDF to decorate our outputs with left padding or right padding inside T-SQL without actually requiring to manipulate the outputs inside the application code.

Hope you enjoyed this article. Happy programming!!!