TSQL : Convert a String to Pad Leading Zeros

Uncle Julio imported some new product codes into a database for the expanding Fajita business. However the leading zeros have been removed somewhere during the import process. The point of sale machines requires a 4 character product code so he turned to his close friend and I.T. expert Jose, to pad leading zeros in front of the raw product codes. e.g. the following table shows 3 different examples demonstrating how the different lengths of data in the raw code column require more or less leading zeros (maximum required code length of 4 characters) for the Required Code column :

Raw Code Required Code Number of Leading Zeros to Pad
3 0003 3
187 0187 1
5455 5455 0

The following snippet demonstrates how Jose managed a year’s supply of free Fajitas by padding leading zeros in front of the product codes using the REPLICATE function :

REPLICATE('0', 4-LEN(a.[Raw Code])) + CAST(a.[Raw Code] as varchar(4)) as [Required Code]
FROM dbo.table;

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