@".NET Developer, ERP and
     Database Analyst"

SQL: Zeroes (or any character) before/after Field Value

A common search, it seems to me, on the internet for SQL assistance is for “how to add values to the beginning or ending of a field.” This is easily rectified by using the REPLICATE function (REPLICATE(@padChar, @length - LEN(@text)) + @text).

The logic for this function is broken down like so:

REPLICATE(@padChar /* The first value for the function is the character to add to beginning or end */, 
@length - LEN(@text)) /* The second value (20 here) is the value's total length. Change this to however long the value output should be and subtract the current length of the value using the LEN function. This isn't required though, any number for the length can be entered to just "tack" on that many characters */
+ @text /* The third part of the example is the value to add on the characters to, this can be before or after and is concatenated to the function by using the '+' symbol */
This function can be used in almost any part of a SQL statement, some examples are below:
SELECT REPLICATE('0', 6 - LEN(CustomerID)) + CustomerID, CustomerName FROM Customers

Before output the CustomerID could be ‘120’, ‘121’, ‘122’, ‘123’, etc. After output with the REPLICATE function it would be ‘000120’, ‘000121’, ‘000122’, ‘000123’.

SELECT CustomerID, CustomerName FROM Customers WHERE REPLICATE('0', 6 - LEN(CustomerID)) + CustomerID IN ('000120', '000121', '000122', '000123')