Appending ID with a missing 0 or 0's

Question

Tools: SQL Server 2008

Environment: Windows Vista

Goal: Appending ID with a missing 0 or 0's, if the length is  less than 9

Problem: There is a miss match data type. The data type of ID is CHAR (9) while

right('000000000' + CAST(id as nvarchar(10)),9)

returns nvarchar

I also tried to use

UPDATE    Dummy
SET        id = CAST(SUBSTRING('000000000',0, 2 ) as char(10)) + ID
WHERE    LEN(id) < 9

is showing an error message

(1 row(s) affected)
Msg 8152, Level 16, State 14, Line 37
String or binary data would be truncated.
The statement has been terminated.

The following code is not adding zero or Zeros for LEN (ID) is less than 9. I am curious to learn how would you resolve this issue.

 create table Dummy ( id  CHAR)
insert into dummy (id) values (12345655)
insert into dummy (id) values (12345667)
insert into dummy (id) values (12345656)
insert into dummy (id) values (12345673)
insert into dummy (id) values (12345675)
insert into dummy (id) values (12345670)
insert into dummy (id) values (12345679)
insert into dummy (id) values (12345674)
insert into dummy (id) values (12345678)
insert into dummy (id) values (05555599)
insert into dummy (id) values (05555599)
insert into dummy (id) values (4444499)
insert into dummy (id) values (4444499)
insert into dummy (id) values (3333399)
insert into dummy (id) values (3333399)
insert into dummy (id) values (2222299)
insert into dummy (id) values (2222299)
insert into dummy (id) values (1111199)
insert into dummy (id) values (01111199)
insert into dummy (id) values (05555599)
insert into dummy (id) values (04444499)
insert into dummy (id) values (03333399)
insert into dummy (id) values (02222299)
insert into dummy (id) values (01111199)
insert into dummy (id) values (09555599)
insert into dummy (id) values (09444499)
insert into dummy (id) values (09333399)
insert into dummy (id) values (09222299)
insert into dummy (id) values (09111199)
insert into dummy (id) values (99555599)
insert into dummy (id) values (099444499)
insert into dummy (id) values (99333399)
insert into dummy (id) values (99222299)
insert into dummy (id) values (123456789)

--

UPDATE dummy 
SET  id = right('000000000' + CAST(id as nvarchar(10)),9)

Where  id in 
(
select * from Dummy where LEN(id)< 9 
)

select * from Dummy where LEN(id)< 9 -- 34 rows

drop table dummy
 

Answer 1

You are converting it to NVARCHAR and appending it with 0's but remember, that you are still assigning it to a column of type INT, which will not show up with any leading zeroes. This is not a bug!

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 2

Hi JH !

You may get the desired output using below query in SQL ;

ALTER TABLE Dummy
ALTER COLUMN id NVARCHAR(10)

UPDATE    Dummy
SET        id = RIGHT('000000000' + CAST(id as nvarchar(10)),9)
WHERE    LEN(id) < 9

Your previous update is not changing the value of id column since your table is defined with id as 'INT' column and it cast your update 'NVARCHAR' value back to 'INT' thats why you don't see the difference while executing update statement.

You need to alter your 'id' column to 'NVARCHAR' to be able to see the difference by executing update statement.

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham

Answer 3

select RIGHT('00000000'+CONVERT(VARCHAR(9),cast(id as int)),9)
from Dummy  
RESULTS:
------
012345655
012345667
012345656
012345673
012345675
012345670
012345679
012345674
012345678
005555599
005555599
004444499
004444499
003333399
003333399
002222299
002222299
001111199
001111199
005555599
004444499
003333399
002222299
001111199
009555599
009444499
009333399
009222299
009111199
099555599
099444499
099333399
099222299
123456789

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 4

>> Goal: I would like to add zero or Zeros for LEN (id) is less than 9 <<

What is the FIRST RULE of any tiered architecture, not just SQL?
Answer: Never, never do display formatting in the database. 

A good SQL Programmer does not allow bad data into the schema in the first place. Would you hire a carpenter that builds a leaky roof and then tell you to mop the floor every day?

CREATE TABLE Dummy 
(id CHAR(9) NOT NULL PRIMARY KEY
 CHECK (id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]');

But if you want a kludge until you can fix the roof, use this:

UPDATE Dummy 
SET id 
 = SUBSTRING (REVERSE(REVERSE(id) + '000000000' + id), 1, 9);
 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

UPDATE    Dummy
SET        id = CAST(SUBSTRING('000000000',0, LEN(ID)- 9 ) as nvarchar) + ID
WHERE    LEN(id) < 9

Still generate error

(1 row(s) affected)
Msg 536, Level 16, State 5, Line 38
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

Answer 6

It worked perfect for me. If the data is same as you provided then it should work. If not then

I think the id column has values not olny nymbers some characters as well!

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 7

a few things, when you insert data with leading 0 without "'", it is treated as integer. So you need put your value inside '' for your test data

and also you need to right trim try this:

create table #dummy ( id  CHAR(9))
insert into #dummy (id) values ('12345655')
insert into #dummy (id) values ('12345667')
insert into #dummy (id) values ('12345656')
insert into #dummy (id) values ('12345673')
insert into #dummy (id) values ('12345675')
insert into #dummy (id) values ('12345670')
insert into #dummy (id) values ('12345679')
insert into #dummy (id) values ('12345674')
insert into #dummy (id) values ('12345678')
insert into #dummy (id) values ('05555599')
insert into #dummy (id) values ('05555599')
insert into #dummy (id) values ('4444499')
insert into #dummy (id) values ('4444499')
insert into #dummy (id) values ('3333399')
insert into #dummy (id) values ('3333399')
insert into #dummy (id) values ('2222299')
insert into #dummy (id) values ('2222299')
insert into #dummy (id) values ('1111199')
insert into #dummy (id) values ('01111199')
insert into #dummy (id) values ('05555599')
insert into #dummy (id) values ('04444499')
insert into #dummy (id) values ('03333399')
insert into #dummy (id) values ('02222299')
insert into #dummy (id) values ('01111199')
insert into #dummy (id) values ('09555599')
insert into #dummy (id) values ('09444499')
insert into #dummy (id) values ('09333399')
insert into #dummy (id) values ('09222299')
insert into #dummy (id) values ('09111199')
insert into #dummy (id) values ('99555599')
insert into #dummy (id) values ('099444499')
insert into #dummy (id) values ('99333399')
insert into #dummy (id) values ('99222299')
insert into #dummy (id) values ('123456789')

update #dummy
Set id = Right('000000000' + RTrim(ID), 9)
Where len(ID) < 9

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 8

If you upgrade to SQL server 2012, then alternatively you can do like this:

update #dummy
Set id = Cast(format(cast(ID as int), '000000000') as char(9))
Where len(ID) < 9

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 9

a few things, when you insert data with leading 0 without "'", it is treated as integer. So you need put your value inside '' for your test data

and also you need to right trim try this:

create table #dummy ( id  CHAR(9))
insert into #dummy (id) values ('12345655')
insert into #dummy (id) values ('12345667')
insert into #dummy (id) values ('12345656')
insert into #dummy (id) values ('12345673')
insert into #dummy (id) values ('12345675')
insert into #dummy (id) values ('12345670')
insert into #dummy (id) values ('12345679')
insert into #dummy (id) values ('12345674')
insert into #dummy (id) values ('12345678')
insert into #dummy (id) values ('05555599')
insert into #dummy (id) values ('05555599')
insert into #dummy (id) values ('4444499')
insert into #dummy (id) values ('4444499')
insert into #dummy (id) values ('3333399')
insert into #dummy (id) values ('3333399')
insert into #dummy (id) values ('2222299')
insert into #dummy (id) values ('2222299')
insert into #dummy (id) values ('1111199')
insert into #dummy (id) values ('01111199')
insert into #dummy (id) values ('05555599')
insert into #dummy (id) values ('04444499')
insert into #dummy (id) values ('03333399')
insert into #dummy (id) values ('02222299')
insert into #dummy (id) values ('01111199')
insert into #dummy (id) values ('09555599')
insert into #dummy (id) values ('09444499')
insert into #dummy (id) values ('09333399')
insert into #dummy (id) values ('09222299')
insert into #dummy (id) values ('09111199')
insert into #dummy (id) values ('99555599')
insert into #dummy (id) values ('099444499')
insert into #dummy (id) values ('99333399')
insert into #dummy (id) values ('99222299')
insert into #dummy (id) values ('123456789')

update #dummy
Set id = Right('000000000' + RTrim(ID), 9)
Where len(ID) < 9

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

teven Wang - Shangzhou you are SQL genius Thanks a lot

Answer 10

Steven

You are SQL genius Thanks a lot