Cool Tips‎ > ‎Data Formatting‎ > ‎

Data Formatting - 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


ċ
udf_padstring.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:55 AM
ċ
udf_padstring2.sql
(2k)
Andy Hughes,
Jun 7, 2012, 10:55 AM
Comments