SQL-Quick tip #3 - Prepending zeroes

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Prepending zeroes

Some data formats require that your numbers have prepended zeros so you can get the SQL Server to format the number. The trick is to prepend the number of zeroes to the your int and then chop the resulting string by the number of wanted digits from the right.

But be aware that in optimized production code this might not be the way to go because the server has to do a lot of string manipulating the do this trick. But for testing or one-time queries this trick can be useful.

DECLARE @names TABLE ([Name] VARCHAR(50), [ImportantNumber] INT)

INSERT INTO @names ([Name], [ImportantNumber]) 
VALUES ('Joe', 2), ('Bob', 4), ('Anne', 42), ('Jane', 134)

SELECT [Name], RIGHT('0000' + CAST(ImportantNumber AS VARCHAR(4)),4) AS [ImportantNumber]
  FROM @names