SQL server has no built-in format functions. Here is a user defined function which left-pad a varchar value with a variable length string.
CREATE FUNCTION [dbo].[PadString]
(@Seq varchar(16),
@PadWith char(1),
@PadLength int
)
RETURNS varchar(16) AS
BEGIN
declare @curSeq varchar(16)
SELECT @curSeq = ISNULL(REPLICATE(@PadWith, @PadLength - len(ISNULL(@Seq ,0))), '') + @Seq
RETURN @curSeq
END
Testing the function:
SELECT dbo.PadString ('8', '0', 5)
SELECT dbo.PadString ('abc', '*', 12)
SELECT dbo.PadString ('abc', '0', 7)
Here are the results:
----------------
00008
(1 row(s) affected)
----------------
*********abc
(1 row(s) affected)
----------------
0000abc
(1 row(s) affected)
---------------------------------------------------------
TAGS: sql server format string pad left