update customer id numbers

[Joseph Smith]

I have a table that contains customer Id numbers that are 5-8 characters. They need to be updated to 10 characters with a leading M and padded with zeros. I am new to this and would appreciate assistance.

[Rabbit]

'M' + replicate('0', 9 - len(cast(id as varchar(10)))) + cast(id as varchar(10))

[Joseph Smith]

Thanks bunch Rabbit that looks like it is perfect. How can I make that run on the customerID field only in my database. Can I simply put the script into the edit steps field in Enterprise Manager?

[Rabbit]

I'm not sure what the Enterprise Manager is. I normally use the SQL Server Management Studio. You would just use that in an update query.

UPDATE tableName SET id = ...

[Joseph Smith]

Ok, I think I understand if you will bear with me one more time. The acutal Table is PatientIdentifier and the column is ID_
so would the script run as:
UPDATE PatientIdentifier SET ID_ = 'M' + replicate('0', 9 - len(cast(id as varchar(10)))) + cast(id as varchar(10))

[Rabbit]

You would need to change the id in the expression as well. I just used id cause I didn't know what your field was called.

[Joseph Smith]

The ID_ is the correct column name that contains the id numbers.

[Rabbit]

Yes but id is not correct.

[Joseph Smith]

Rabbit, I have created the script and all looks correct. I am almost ready to test. Will this script if run again only update the lines that need updating?

[Joseph Smith]

Here is my script as it is written.
UPDATE PatientIdentifier SET ID_ = 'M' + replicate('0', 9 - len(cast(ID_ as varchar(10)))) + cast(ID_ as varchar(10))

[Rabbit]

I don't know what you mean by only update the lines that need updating. You didn't mention that you only wanted to update certain records. This will update every record unless you specify a WHERE clause.

[Joseph Smith]

I want to update them all now and the update the new ones that come in between now and the time we go live with the new system.

[Rabbit]

That will update them all now. You can add a WHERE clause to filter out the ones that are already ten characters in length. But it should be handled on the client side. Otherwise, you will have to run the query periodically and the data won't be real time. Or you could handle it server side by setting up a stored procedure, which would require work on both the client side and server side but would be the most accurate.

[Joseph Smith]

Thank you so much you have been extremely helpful. Since I do not know much about the WHERE statement I think I may just run the script 1 hour before we switch and then manually update the few that come in during the switch. Thanks again.

[Rabbit]

Not a problem Joseph. If you want to learn about the where clause, I suggest the tutorials by wc3.