CODEWITHSUNDEEP
sqlsql-server-2008windows-server-2008-r2
Reece
Reece

Reputation: 807

Find email address in SQL Server database

A website I've been asked to edit content on has a few hundred existing pages (blog articles) that are based on a template. I've adjusted the email address in the template's content so all new articles will have the correct address, but I want to replace the old address on the existing pages.

The website is .aspx built on a SQL Server database.

I have access to the database, but don't know which tables (let alone columns or cells) contain the particular email address.

I'd like to first to a search on the database to locate all appearances of this email address in the text/content, and then, if there are no appearances of it that will have adverse effects, I'd like to do a find/replace to update it to the new address.

I'm unfamiliar with SQL Server. How do I do this?

FYI... the code on the website (it's part of a comments form) will always be: 

<input type="hidden" value="[email protected]" name="Notify" />

EDIT:
I didn't know where the html string would be in the databases at all, but I've done some searching and have found that the pages I need to change the email address on are all defined by the one table and column. 

DB = CMSDB
Table = dbo.tblContent
Column = ContentText

I am using Microsoft SQL Server Management Studio Express to view the databases

Upvotes: 0

Views: 5135

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

This will generate a separate UPDATE statement for every single character-based column in all of the user tables in your database. You could probably enhance this to be nested such that you leave out any UPDATE statements for columns that don't contain the old e-mail address in any row, but as a one-time task, the added complexity is unlikely to pay off.

DECLARE 
  @oldEmailAddress VARCHAR(320) = '[email protected]', 
  @newEmailAddress VARCHAR(320) = '[email protected]';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT TOP (5) @sql += N'
PRINT N''Updating ' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id]))
  + '.' + QUOTENAME(name) + '...'';
UPDATE ' 
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id]))
  + ' SET ' + QUOTENAME(name) + ' = REPLACE('
  + QUOTENAME(name) + ', @old, @new)
    WHERE ' + QUOTENAME(name) + ' LIKE ''%'' + @old + ''%'';'
FROM sys.columns AS c
WHERE system_type_id IN (35,99,167,175,231,239)
AND EXISTS (SELECT 1 FROM sys.tables WHERE [object_id] = c.[object_id]);

PRINT @sql;
/*
 EXEC sp_executesql 
   @sql, 
   N'@old VARCHAR(320), @new VARCHAR(320)',
   @oldEmailAddress, @newEmailAddress;
*/

You really should consider putting tokens of some kind here, and having the web page replace the token with an e-mail address you store in a single config file or table. You know, instead of just replacing dozens of instances of an e-mail address that had to change with dozens of instances of another e-mail address that will probably have to change at some time in the future (at which point you'll be scrambling to find this post so you can repeat the process).

If the content is only in one column of one table, your task is much easier. However you should update your question to reflect that, as currently it sounds like it will be in multiple columns/tables:

but don't know which tables (let alone columns or cells) contain the particular email address.

Now you're saying:

DB = CMSDB, Table = dbo.tblContent, Column = ContentText

So, the requirements are confusing. If the e-mail address really can only appear in the above column, then it is as follows:

USE CMSDB;
GO

DECLARE 
  @oldEmailAddress VARCHAR(320) = '[email protected]', 
  @newEmailAddress VARCHAR(320) = '[email protected]';

UPDATE dbo.tblContent
  SET ContentText 
      = REPLACE(ContentText, @OldEmailAddress, @NewEmailAddress)
  WHERE ContentText LIKE '%' + @OldEmailAddress + '%';

Upvotes: 2

Amilcar Andrade
Amilcar Andrade

Reputation: 1171

You can export all your tables as UPDATE statements, and the you can use a linux utility call sed to update the rows.

You will need to so something like this: 

sed -i 's/old_email/new_email/g' sql.file

Upvotes: 0

Related Questions