I have used something similar to this in the past when I needed to mass change email addresses at work for over 15k accounts.
Backup your data before running as this can produce unpredicted results if not handled right. Test using a test box first if you can.
First see if you can select the data you are needing to target
select columnname from table where columnname like '% And %' COLLATE SQL_Latin1_General_CP1_CS_AS
if it looks good then an update statement to find and replace the text should work. Keeping the space before and after the And should hopefully help keep names like Andrew Andrea etc from getting updated.
update table set columname = replace(columnname, ' And ' , ' and ') where columnname like '% And %' COLLATE SQL_Latin1_General_CP1_CS_AS
Some of the better db folks may be able to tweak or shed more light on this. If your server is using a different code page, then the collate statement will need to use the appropriate case sensitive code page for the server.
SQL Query help, please
Posted - 01 March 2011 : 11:02:30
