amma.NETtami

.NET walkabout
posts - 11, comments - 18, trackbacks - 0

Thursday, November 15, 2007

How to format a value using T-SQL: pad left

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

posted @ Thursday, November 15, 2007 8:55 AM | Feedback (9) | Filed Under [ T-SQL ]

Powered by:
Powered By Subtext Powered By ASP.NET