The Forum has been Updated
The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.
I have a field that looks like "John And Mary". Notice how the 'and' is capitalized. I need to go through the database and find the fields that have that capitalized 'and' and make it lowercase. I know how to do this in a round-about way using ASP but wondered if there's a way to accomplish this using only MSSQL Server?
Thanks in advance!
Thanks in advance!
Posted
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
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.
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.
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
Code:
select columnname from table where columnname like '% And %' COLLATE SQL_Latin1_General_CP1_CS_ASif 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.
Code:
update table set columname = replace(columnname, ' And ' , ' and ') where columnname like '% And %' COLLATE SQL_Latin1_General_CP1_CS_ASSome 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.