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
|
 Updating...
Andy Hughes, Jun 7, 2012, 10:55 AM
Andy Hughes, Jun 7, 2012, 10:55 AM
|