VB and VBA Users Source Code: Formatting/padding a number as a fixed length string in SQL server

VB/VBA Source Code
Formatting/padding a number as a fixed length string in SQL server

By:	Andrew Baker
Email (spam proof):	Email the originator of this post
Date:	Thursday, November 20, 2003
Hits:	1001
Category:	Database/SQL/ADO

Article:
The following SQL demonstrates how to return a number as a fixed length string, eg. returning the number 542 as '000542'. --Example formatting a number to fixed size of 6 chars declare @FormatNumber varchar(6), @NumDP tinyint --Cast the number to a string (in this example using 569) select @FormatNumber = cast(569 as varchar(6)) --Format the number to a fixed length select @FormatNumber = REPLICATE('0', 6- LEN(cast(@FormatNumber as varchar(3)))) + @FormatNumber --Return the result select @FormatNumber
Terms and Conditions
Support this site Download a trial version of the Excel Workbook Rebuilder