Technical Article

Pad Number

,

A simple UDF for padding out numbers with a specific character (eg: pad 3 to show as 003).

Usefull when you can only sort as a text item or for formatting purposes.

Script is similar to the SPACE() function but allow the padding character to be defined.

Usage:

dbo.padNumber('string to pad', padsize, padchar)

eg:

SELECT dbo.padNumber('53', 4, '0') as testNum

Returns:

testNum
-------
0053


CREATE FUNCTION padNumber (@theString varchar(255), @padSize int, @padChar char(1))
RETURNS char(50)
AS
BEGIN
-- =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
--      Script by Daniel Field, daniel@worldof.net 2003
-- =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+declare 
@theResultchar(50),
@countint,
@cint

set @count = Len(RTrim(@theString) )
set @c = 0

if @count > @padSize
begin
set @theResult = RTrim( @theString )
end
else
begin
set @theResult = Replace(SPACE(@padSize-@count) + RTrim( @theString ), ' ', @padChar)
end
set @theResult = replace(@theResult, ' ', '0')

return @theResult
END

Rate

Share

Share

Rate