SQL REPLICATE Function

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.

 

Leave a thought