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

The IDs after adding zeroes don't look like the original IDs. Can you explain what is happening here? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 14, 2017 at 7:35
ID is always 9 digits but our software removes zeros automaticly but I need to add those zeros again when running select so I need to add zeros after 6 so: if ID1111 so ID111001 and if ID222222 then ID2222202 — DJ_TecRoot
– DJ_TecRoot, Commented Feb 14, 2017 at 7:43
Maybe this is what you're looking for :-) sql server 2008 add to string in particular position within name column — Mike
– Mike, Commented Feb 14, 2017 at 7:47
Your sample data is very unclear. Can you please clean up your question so we don't have to keep guessing? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 14, 2017 at 7:55

Gottfried Lesigang · Accepted Answer · 2017-02-14 08:19:41Z

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.

answered Feb 14, 2017 at 8:19

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Denis Rubashkin · Accepted Answer · 2017-02-14 08:36:16Z

1

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

answered Feb 14, 2017 at 8:36

Denis Rubashkin

2,2311 gold badge11 silver badges16 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2017-02-14 07:58:09Z

0

You can use a combination of padding and substring to get the output you want:

SELECT LEFT(SUBSTRING(SystemID, 1, 6) + '000', 15 - LEN(SystemID)) +
           SUBSTRING(SystemID, 7, LEN(SystemID) AS SystemIDPadded
FROM yourTable

edited Feb 14, 2017 at 7:58

answered Feb 14, 2017 at 7:39

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

DJ_TecRoot Over a year ago

but i need to add zeros after 6 digits to be 9. So if SystemID is ID11111 (7 digits) i need to add zeros after 6 so ID1111001 and if systemID 8 digits so I need ID2222202

Tim Biegeleisen Over a year ago

@DavidJackowiak I gave you a query which works for the example in your comment above. Not sure about the general case though.

Collectives™ on Stack Overflow

Add zeros in SQL columns

3 Answers 3

Short explanation

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Short explanation

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related