Replace certain characters in column that is primary key

[CoolDudeeh]

I am trying to replace certain characters in a column that happens to be the primary key for the table.
Here is some data
123-123-4576
asd qwert qawer
asd/lkjljk
asd'wert-zxcv
I would like to eliminate the " " "/" "'" and "-".
I am ok with running the query multiple times - once for each special character being removed.
I thought this would be simple and I have been reading about various fors of replace, but I can not seem to get it to work.
Can anyone help?

 

[djj55]

UPDATE mytable 
SET MyField = REPLACE(MyField, '/', '')

Should do what you want.

Cautions:
I would do this after hours
I suggest making a test table with PK before doing on actual
You might need a reindex after replace - Can you drop the PK then recreate it when done?

Good luck.


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[CoolDudeeh]

Thanks for the reply.
Here is what I am getting.
I am thinking that there are records that once the special characters are out, it is matching a record that already exists?
Any suggestions? or ideas?

Have tried that and I get a message like
SQL execution error
Executed statement: Update parts SET [Pard ID] = REPLACE([Part ID], '/', '')
Error Source: .Net sqlclient Data Provider
Error Message: Violation of PRIMARY KEY constraint 'PK__parts__2E1BDC42'. Cannot insert duplicate key in object 'dbo.parts'
The statement has been terminated.

 

[SQLSister]

This is a much more difficult process than you think. In the first place this is clearly a very bad primary key. If you can rededsign, then get a surrogate key. Why, because you want to change existing values which means the PK is a bad key. Why, because you now also have to change any related tables or you will lose data integrity and if you have formal relationships defined, they will stop you from making the change or will propogate through the database in a cascade which can bring things to a screeching halt (update 10 million order records for one part number change automatically, oops, nobody can create an order on the locked table).

Further you clearly have duplicates in your data once you remove the offending characters. Again you need to decide what to do about that before changing any data. Should the records be dedupped? Is your primary key wrong somehow and is in fact not unique (Part numbers are virtually never unique unless they come from only one vendor another reason to never consider using them as a primary key). Now you need to research which ones are duplicated before trying to change the data and decide what to do about them and any related records.

Do not even consider doing this task on a production server without a good backup and this must be tested thoroughly on a dev server before even considering running on production as this is a major change that can have catostrophic consequences if you get it wrong.

"NOTHING is more important in a database than integrity." ESquared