SQL Server add zero if there is length less than specific number

Question

I have a table with a column of numbers like 789. I want to pad them with 0 to equal 9 digits. For example, 789 should be 789000000

I have tried this code, but there are some numbers more than 3 digits and less than 3 digits.

UPDATE
   Table
SET
   MyCol = MyCol+'0000000'
WHERE
   LEN(MyCol) >9;

Answer 1

The following code does what you need, the bit you need is the REPLICATE section:

declare @var nvarchar(10);

set @var = '321'

select @var + REPLICATE('0',9-LEN(@var))

RESULT:

321000000

Answer 2

Your question would be a better fit in stackoverflow, but I think you are after this:

UPDATE
   Table
SET
   MyCol = left(MyCol + '000000000', 9)
WHERE
   LEN(MyCol) < 9;