CODEWITHSUNDEEP
sqlsql-server
DJ_TecRoot
DJ_TecRoot

Reputation: 93

Add zeros in SQL columns

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

Upvotes: 3

Views: 780

Answers (3)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

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

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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

Upvotes: 0

Related Questions