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
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
END
ELSE IF @InputWidth = @OutputWidth
SET @Output = @Input
RETURN (@Output)
END
GO