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


There is a lengthy discussion about this topic already on Stack Overflow here:

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


Leave a Reply

Your email address will not be published. Required fields are marked *