Add zeros in SQL columns

Question

How can I add zeros to SQL column to match 9 digits? We have an column name SystemID which contains products IDs. Our CRM system removes zeros automatically but sometimes we need to take out those numbers and paste to Excel and it needs to includes all numbers. It's something like this:

ID1111111
ID111111
ID11111

How can I add zeros after 6 digits?

If ID111111 then ID1111011
If ID22222  then ID2222002
If ID3333   then ID3333000

Answer 1

Your description is not all clear, but it might be this you are looking for:

DECLARE @Dummy TABLE(YourID VARCHAR(100));
INSERT INTO @Dummy VALUES('ID111111 '),('ID22222'),('ID3333')      

SELECT LEFT(STUFF(YourID + REPLICATE('0',9),7,0,REPLICATE('0',9-LEN(YourID))),9)
FROM @Dummy

The result

ID1111011
ID2222002
ID3333000

Short explanation

First I add 9 zeros to make sure, that the original string has got 9 digits in any case. Then I use STUFF to introduce the appropriate number of zeros in the 7th place. The final string is cut at 9.

Answer 2

DECLARE @Test TABLE(YourID VARCHAR(100));
INSERT INTO @Test VALUES('ID111111'),('ID22222'),('ID3333'),('ID'),('ID1234567')

SELECT LEFT(YourID, 6) + REPLICATE('0', 9 - LEN(YourID)) + SUBSTRING(YourID, 7, LEN(YourID))

FROM @Test