Add Leading Zeros to String
SQL Server 2000: Not Supported**
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 18 Jul 2012
One of the string modifications that I regularly come across is the need to add leading zeros to numbers for presentation/labelling purposes
eg When dynamically constructing a file label to include the date, I usually want to include leading zeros on the day and month portions to enable the files to be sorted and/or vsially easy to search - 3 Jan 2012 becomes 20120103
This is relatively straightforward to do, but the code can become repetitive, and several times I've fallen foul of implicit conversion 'features' with inline string manipulation i.e. the output string generally adopts the data type of the input string unless specifically cast - this can cause problems if the input string data types vary.
Utilizing a function to left-pad the string circumvents most of these problems.
** The code uses VARCHAR(MAX) so is marked as unsuitable for SQL 2000. A quick modification to VARCHAR(8000) would make it work.