Universal String Padder

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown/Richard Doering

Date: 29 Nov 2010

Description

An odd little function that will allow any character string to be padded with single or multiple copies of the supplied character at either the beginning, middle, end or beginning and end.

There are actually 2 variants of this function

- #1 Pads with a specified number of repetitions of a single character
- #2 Pads with the number of repetitions of a single character until the resulting string is the specified length

Not a big difference between the two, but #2 would be more useful when producing a tabulation I think.

Might not get used very often, but very useful when they do.

I'm a great fan of formatting dynamic sql that I create for readability when debugging ie constructing the command string with spaces, tabs and CR/LF as per Stored Procedure code. This means that when I write the string to console, file or table for debugging or tracing, then the TSQL is easily readable. It makes for much more straightforward and less time-consuming debugging, especially when I return to the code after a while away.

Code

Variant #1:

DROP FUNCTION dbo.udf_PadString

GO

CREATE FUNCTION [dbo].[udf_PadString]

(

@string_unpadded VARCHAR(100),

@pad_char VARCHAR(1),

@pad_count tinyint,

@pad_pattern INT

)

RETURNS VARCHAR(100)

/*

Pad_Pattern:

0. LEFT 1234 = xxx1234

1. RIGHT 1234 = 1234xxx

2. CENTRE 1234 = 12xxx34

3. EDGES 1234 = xxx1234xxx

*/

AS

BEGIN

DECLARE @string_padded VARCHAR(100)

SELECT @string_padded =

CASE @pad_pattern

WHEN 0 THEN REPLICATE(@pad_char, @pad_count) + @string_unpadded --pad left

WHEN 1 THEN @string_unpadded + REPLICATE(@pad_char, @pad_count) --pad right

WHEN 2 THEN --pad center

LEFT(@string_unpadded, FLOOR(LEN(@string_unpadded)/2))

+ REPLICATE(@pad_char, @pad_count)

+ RIGHT(@string_unpadded, LEN(@string_unpadded) - FLOOR(LEN(@string_unpadded)/2))

WHEN 3 THEN --pad edges

REPLICATE(@pad_char, @pad_count)

+ @string_unpadded

+ REPLICATE(@pad_char, @pad_count - FLOOR(@pad_count/2))

END

RETURN @string_padded

END

/*

select [dbo].[udf_padstring] ('Hello World','x',1,0)

select [dbo].[udf_padstring] ('Hello World','x',1,1)

select [dbo].[udf_padstring] ('Hello World','x',1,2)

select [dbo].[udf_padstring] ('Hello World','x',1,3)

*/

Variant #2:

/*

Script : Character Padding Function

Version : 1.0 (April 2010)

Author : Richard Doering

Web : http://sqlsolace.blogspot.com

The function has 4 mandatory parameters, defined as follows >

@Input - the string to be formatted

@OutputWidth - the desired width of the output string

@OutputAlign - either 'LEFT' or 'RIGHT' , depending on where you want the input string aligned.

@PadCharacter - the character to perform the padding with.

*/

DROP FUNCTION dbo.udf_PadString2

GO

CREATE FUNCTION dbo.udf_PadString2

(

@Input VARCHAR(255)

,@OutputWidth INT

,@OutputAlign VARCHAR(5)

,@PadCharacter CHAR(1)

)

RETURNS VARCHAR(255)

AS

BEGIN

DECLARE

@Output VARCHAR(255),

@InputWidth INT

SET @InputWidth = LEN(@Input)

IF @InputWidth > @OutputWidth

BEGIN

IF @OutputAlign = 'LEFT'

BEGIN

SET @Output = LEFT(@Input,@OutputWidth)

END

IF @OutputAlign = 'RIGHT'

BEGIN

SET @Output = RIGHT(@Input,@OutputWidth)

END

ELSE IF @InputWidth < @OutputWidth

BEGIN

IF @OutputAlign = 'RIGHT'

BEGIN

SET @Output = REPLICATE(@PadCharacter, @OutputWidth - @InputWidth ) + @Input

END

IF @OutputAlign = 'LEFT'

BEGIN

SET @Output =@Input+ REPLICATE(@PadCharacter, @OutputWidth - @InputWidth )

END

ELSE IF @InputWidth = @OutputWidth

SET @Output = @Input

RETURN (@Output)

END

GO