Click here to Skip to main content
13,446,544 members (41,468 online)
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 8 Jun 2009

Padding strings in TSQL using a generic UDF

, 8 Jun 2009
Rate this:
Please Sign up or sign in to vote.
Left padding or right padding strings in TSQL using a UDF.


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.


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)
 -- Declare the return variable here
 DECLARE @Result varchar(1000)
 if @strPaddingSide = 'L'
  Select @Result = REPLICATE(@strPadWith, @intLength -  
                   LEN(ISNULL(@strToPad,''))) + ISNULL(@strToPad, '')
  Select @Result = ISNULL(@strToPad, '') + REPLICATE(@strPadWith, 
                   @intLength -  LEN(ISNULL(@strToPad,'')))
 RETURN @Result

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.


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”.


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!!!


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


About the Author

You may also be interested in...


Comments and Discussions

GeneralUseful One Pin
arvindjo27-Aug-09 23:53
memberarvindjo27-Aug-09 23:53 
GeneralRe: Useful One Pin
Robin_Roy6-Sep-09 17:50
memberRobin_Roy6-Sep-09 17:50 
GeneralMy Vote of 5 Pin
albert_cook10-Aug-09 16:59
memberalbert_cook10-Aug-09 16:59 
GeneralRe: My Vote of 5 Pin
Robin_Roy17-Aug-09 15:51
memberRobin_Roy17-Aug-09 15:51 
GeneralThis can be done other ways Pin
Jon_Boy8-Jun-09 5:45
memberJon_Boy8-Jun-09 5:45 
GeneralRe: This can be done other ways Pin
Robin_Roy28-Jul-09 16:34
memberRobin_Roy28-Jul-09 16:34 

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

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

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