Click here to Skip to main content
Click here to Skip to main content

Padding strings in TSQL using a generic UDF

, 8 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Left padding or right padding strings in TSQL using a 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!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
GeneralUseful One Pinmemberarvindjo28-Aug-09 0:53 
GeneralRe: Useful One PinmemberRobin_Roy6-Sep-09 18:50 
GeneralMy Vote of 5 Pinmemberalbert_cook10-Aug-09 17:59 
GeneralRe: My Vote of 5 PinmemberRobin_Roy17-Aug-09 16:51 
GeneralThis can be done other ways PinmemberJon_Boy8-Jun-09 6:45 
GeneralRe: This can be done other ways PinmemberRobin_Roy28-Jul-09 17:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 8 Jun 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid