Yet another padding function. This will let you use strings instead of a single character.

/*

Example:Result:

SELECT dbo.fn_Padding('1', '0', 3, 'L')001

SELECT dbo.fn_Padding('15', '0', 3, 'L')015

SELECT dbo.fn_Padding('ABC', '*', 10, 'R')ABC*******

SELECT dbo.fn_Padding('ABC', '_°', 10, 'R')ABC_°_°_°_

SELECT dbo.fn_Padding('ABC', '_°', 10, 'L')°_°_°_°ABC

*/

CREATE FUNCTION dbo.fn_Padding

(

@TextVARCHAR(8000),

@PaddingCharsVARCHAR(20),

@StringLengthINT,

@DirectionCHAR(1) = 'L'

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @PaddingStringVARCHAR(8000)

-- Build string to use for padding

SET @PaddingString = REPLICATE(@PaddingChars, (@StringLength / LEN(@PaddingChars)) + 1)

-- Left padding

IF (@Direction = 'L')

SET @Text = RIGHT(@PaddingString + COALESCE(@Text, ''), @StringLength)

-- Right padding

IF (@Direction = 'R')

SET @Text = LEFT(COALESCE(@Text, '') + @PaddingString, @StringLength)

RETURN @Text

END