Update a word in entire table [RESOLVED]

[usamaalam]

Hello everybody,

Someone generated SQL script for data migration and misspelled the word "Install" with "Instal". This is only in one field of a table but the table contains massive records.

Is there some way to write a script to update Title field in such a way if it contains the word Instal, it should be replaced with Install. I have seen Instal is at the end of the other data.

For example:
abc
abc2
xyz Instal
xyz2Instal

This field should be:
abc
abc2
xyz Install
xyz2Install

Thanks.

[Hack]

UPDATE abc SET field = 'xyz Install' WHERE field = 'xyz Instal'

[sevenhalo]

Or this will work too:

UPDATE tblSample SET Field = REPLACE (Field, 'Instal', 'Install')

Mind you, it applies a wild card on both sides of 'Instal', so correct spelling in that field will have 3 L's.

[usamaalam]

Thanks.

sevenhalo's code is useful for me because data contains different words before Instal like "Store 1302 Instal" or "Store 6957 Instal" etc.

[mendhak]

What about the fields where Install (with two Ls) was originally there?

[Hack]

What about the fields where Install (with two Ls) was originally there?

They would be skipped.

[sevenhalo]

Hack - I think he was reffering to my example. It will over-correct if it's not a universal problem.

Mend - I threw in a disclaimer so they knew ahead of time. I guess you could get really dirty and throw in another statement afterwards:

UPDATE tblSample SET Field = REPLACE (Field, 'Installl', 'Install')

Or, if you want to be clean and proper, you could make a sproc that will work (and maybe even abstract it enough so that it can be used on future issues).

Me... I preffer to be dirty.

[mendhak]

I like that.

[usamaalam]

I did the same thing. Ran second script to replace any Installl with Install.