If you have ever used Oracle and needed to create a string padded with zero’s, they have a nifty function called LPAD which allows you to do this easily. In SQL Server we need to create our own way.
Why would you need to do this? A simple example is if you need to provide data to a 3rd party and they need the data in a fixed length format. By padding fields to a certain length they can build their process and not worry about comma’s breaking up fields in the middle.
Below is a nice and easy example using Oracle. PersonID is an integer and we need to make it a 6 character field, padded by a 0 to make it a six character field.
With SQL Server we do not have this handy function so we have to concoct our own.
Below is what our data looks like:
Using the FORMAT function we can do this! Below is more documentation regarding this function: https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15
First lets try using a basic format conversion. It gives and error that is in an invalid format. Our account number is actually a string so we need to convert it.
After converting the field to INT now it works!
What if we have an INT field we need to pad? That works perfect without having to convert it.
Using the FORMAT function allows you to do all kinds of data format manipulation and is a very useful tool in your tool box.
Jim Salasek's SQL Server Blog 