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: 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.
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.
- 000000009 — Padded by 8 zeros
- 123456789 — Padded by no zeros