amma.NETtami

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

November 2007 Blog Posts

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