SQL 2000 syntax add leading zeros to varchar

you are ot doing anything wrong (syntax-wise), but you have a unique constraint on field personnum and you are trying to update this row , where another row exists having the new id you are trying to use.

try
print Right('0000000000' + [badgenum],10)  when you get the error and the search Person where Personnum = result. perhaps some of [badgenum] is being truncated (which out the sample data I wont know, but you can try

select distinct len(badgenum) from Badgeassign where len(badgenum) > 10
hopefully you should not get any results, if you do there is a truncation happening and you could get a senario like: 12345678901 and 12345678902 would both try update personnum with 1234567890

How can I get around this ? Perhaps if I first remove any existing leading zero's then add the required leading zero's ? Although I'm unsure how to remove the existing leading zero's. Any suggestions ?

I'm sorry, I think we crossed comments. Any way the select distinct didn't return anything from badgenum. There are quite a few entries in the personnum field with leading Zero's and 10 digits. I 'm thinking if I remove the existing leading zero's, the new update may work. Sound right ?

Need more details.

1) Do you have index on personnum column? What is the data type and data in this column?
2) Do you have duplicate records for badgenum column in Badgeassign table?

How can I tell if I have an Index on the column ?  The data type is varchar in both columns and the data in badgenum is numeric, the first 15 rows in the personnum column are alpha the rest are numeric.
how can I tell if there are duplicate records in the badgeassign table ?

the Right('0000000000' + [badgenum], 10) always results in '0000000000' which is duplicate for second update record.Use

   set p.personnum = [badgenum]

to firstly remove the leading zeros.

Then

    set p.personnum = Left('0000000000', 10-p.personnum.length) + p.personnum

to add the leading zeros.



   

Actually both methods work just fine and both have the same down fall (when personnum lenght is greater than 10 both will give incorrect answers) try:

set p.personnum = case when len(badgenum ) > 9 then badgenum else  Right('0000000000' + [badgenum],10)  end

or alternatively you could also use:

set p.personnum = case when len(badgenum ) > 9 then badgenum else  Left('0000000000', 10-p.personnum.length) + p.personnum end


if you get an error which says data will be truncated then you have data which exceeds the char limit of the field, and you will need to increase that.

I get this error:  Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'p.personnum' does not match with a table name or alias name used in the query.


when running : set p.personnum = Left('0000000000', 10-p.personnum.length) + p.personnum

you may run below queries to find out why there were duplicates, then figure out a solution based on that

-- 1. to get duplicates
with cte as (
select p.personid, Right('0000000000' + [badgenum],10) as new_badgenum
  from person P
  join Badgeassign b
    on p.personid = b.personid
)
select new_badgenum from cte
group by personid
having count(distinct personid ) > 1

-- to find personid
select * from Badgeassign
where Right('0000000000' + [badgenum],10)
= <pick one from the above result>

Select allOpen in new window

To find the duplicate badge numbers in Badgeassign table, execute this query and check the result.

select badgenum from Badgeassign group by badgenum having count(badgenum) > 1;

Sorry all, I'm currently slammed with a client disaster. I will try your soloutions at the very first opportunity I have. I appreciate you knowledge and time, and again I appologize for the hang up.

the p. was from your own update statement where i only posted the set clause.

zadeveloper's solution : use Kronos
update p
   set p.personnum = case when len(badgenum ) > 9 then badgenum else  Right('0000000000' + [badgenum],10)  end
  from person P
  join Badgeassign b
    on p.personid = b.personid

worked perfectly. Thank you.

Thank you all very much

Worked perfectly thank you