0

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;
3

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
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.