I needed to replicate a character 24 times in one instance and in another I had to prefix a value with leading zeros (total of 24 characters). SQL Server has a REPLICATE function that makes duplicating a character x number of times:
First Solution:
Replicate(‘0’, 24) –> 000000000000000000000000 (Total length = 24)
Second Solution (PADLEFT + TRIM):
RIGHT(REPLICATE(‘0’, 24) + ISNULL(CUIN, ‘0’), 24)
If CUIN = 1845
REPLICATE(‘0’, 24) + ISNULL(CUIN, ‘0’) –> 0000000000000000000000001845 (Total length = 28)
Then the RIGHT function will cut off the 24 characters starting from the right going left:
RIGHT(‘0000000000000000000000001845’, 24)
0000000000000000000000001845 (Total length = 24)
Conclusion:
Using the REPLICATE function along with a LEFT or RIGHT function allows you to pad value to the desired length.