zPad() - add leading zeros to a number

,

This user-defined function converts a number to character format and adds leading zeroes to pad the field to a specified length.  useful for displaying SSNs or other numeric fields where the leading 0's are significant but the field is stored as numeric.

To use:
select dbo.zPad(, )

make sure your length includes an extra column for the sign.  If you format an SSN, length = 10.

ALTER FUNCTION dbo.zPad
(@Number Int, @Length TinyInt)
RETURNS varchar(255)
AS
/*****************************************
 *
 * copyright 2002 (c): don frazier
 * all rights reserved.
 *
 * Permission is granted to use this function
 * provided no fee is charge for its use
 * or distribution.
 * 
 * This notice must remain intact in all
 * executable and published copies.
 *
 * pad a number with leading zeros to specified length.
 * Note length includes a leading space
 * for the sign.  The sign is a blanks space
 * for positive numbers and a minus for
 * negative numbers.  cool eh?
 *
 *****************************************/
BEGIN
declare @RC          varchar(255)
,       @MyNumber    varchar(30)
,       @Sign        char(1)
,       @MyLength    int

if @Number >= 0
  set @Sign = ' '
else
  set @Sign = '-'

set @MyNumber = cast(abs(@Number) as varchar(20))
set @MyLength = len(@MyNumber)
if @MyLength < @Length set @MyLength = @Length
if @MyLength > 255 set @MyLength = 255
set @MyLength = @MyLength - 1
return @Sign + right( (replicate('0', 255) + cast(abs(@Number) as varchar(20))), @MyLength)

end

Rate

Share

Share

Rate