Gotcha

There isn’t a Pad Left or Pad Right function in tSQL.

Solution

There is a lengthy discussion about this topic already on Stack Overflow here: https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008

I am not a fan of long solutions or fixed length anything when it comes to stuff like this so this is the solution that I came up with which is essentially the same as this solution provided by user Nicholas Carey.

My solution

This solution I providing is only good for strings, if you need to pad a number use the “FORMAT()” function.

This is my one liner example:

DECLARE @string VARCHAR(9) = '9'

SELECT CONCAT(REPLICATE('0', (9 - LEN(@string))), @string)

The number 9 is of no significance I just happen to be working on something that requires 9 characters left padded by zeros.

Example output

• 000000009 — Padded by 8 zeros
• 123456789 — Padded by no zeros