Zero-Padding a value in T-SQL

Database mssql

Warning! This post is quite old, and it might not apply anymore, or maybe there's a better way to do the same thing nowadays. Take with a big grain of salt!

I keep running into limitations in T-SQL as used in MS SQL Server. Apparently part of MySQL's popularity is that the have added useful extensions to the language that actually solve common problems. (Okay, that was a bit too sarcastic. Still though)

One lovely example is zero-padding a string. In MySQL, you would use the LPAD function:

select lpad(5, 3, 0);

In T-SQL, you have to jump through one of several hoops. The way I ended up writing it for this application:

SELECT RIGHT('000000' + CAST(5 AS varchar), 3);

There are probably other workarounds, you could probably do something with the stuff() function. All of them are workarounds however, and none of them are immediately readable. Very frustrating, for someone used to a different database.