Zero pad a string

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.

pad0

 

With SQL Server we do not have this handy function so we have to concoct our own.

Below is what our data looks like:

pad2

 

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.

pad3

pad1

After converting the field to INT now it works!

pad4

 

What if we have an INT field we need to pad?  That works perfect without having to convert it.

pad5

 

Using the FORMAT function allows you to do all kinds of data format manipulation and is a very useful tool in your tool box.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s