Replace text (in the data) in a database

Something that I came across recently as a task, that I felt might be interesting to share. This code combines a few things I have done previously (such as looking through system tables, parsing through data, and table variables) and takes it to a new (and potentially dangerous) level.

In essence, what I am doing here is creating a table variable (then cursor) of all the old/new text values. While looping through this data, I scan for all fields in all tables that are of type %CHAR% and create dynamic SQL to determine if each field contains the specific text I’m looking to replace. If it does, then I create more dynamic SQL to replace the old text with the new text.
Be aware of exactly what this is doing, because the potential exists to literally ruin your data. For example, if you replace “t” with “th” then “HTML” becomes “HTHML”, etc..

The code:
DECLARE @TextUpdate TABLE (
OldText NVARCHAR (255) NOT NULL ,
NewText NVARCHAR (255) NOT NULL)
INSERT INTO @TextUpdate SELECT ‘first old value’,’new value #1′
INSERT INTO @TextUpdate SELECT ‘2nd old value’,’new value 2′
DECLARE @OldText NVARCHAR (255)
DECLARE @NewText NVARCHAR (255)
DECLARE @TABLE_SCHEMA NVARCHAR(255)
DECLARE @TABLE_NAME NVARCHAR(255)
DECLARE @COLUMN_NAME NVARCHAR(255)
DECLARE @SQL NVARCHAR(2000)
DECLARE TextUpdate CURSOR FOR
SELECT * FROM @TextUpdate
OPEN TextUpdate
FETCH NEXT FROM TextUpdate INTO @OldText, @NewText
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE TABLES CURSOR FOR
SELECT T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.Tables T
JOIN INFORMATION_SCHEMA.Columns C ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME
WHERE TABLE_TYPE = ‘BASE TABLE’ AND (DATA_TYPE LIKE ‘%CHAR%’)– OR DATA_TYPE LIKE ‘%TEXT%’)
ORDER BY T.TABLE_TYPE, T.TABLE_SCHEMA, T.TABLE_NAME, C.ORDINAL_POSITION
OPEN TABLES
FETCH NEXT FROM TABLES INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘IF EXISTS(SELECT * FROM [‘ + @TABLE_SCHEMA + ‘].[‘ + @TABLE_NAME + ‘] WHERE [‘ + @COLUMN_NAME + ‘] LIKE ”%’ + @OldText + ‘%”)
BEGIN
UPDATE [‘ + @TABLE_SCHEMA + ‘].[‘ + @TABLE_NAME + ‘]
SET [‘ + @COLUMN_NAME + ‘] = LEFT([‘ + @COLUMN_NAME + ‘], CHARINDEX(”’ + @OldText + ”’, [‘ + @COLUMN_NAME + ‘])-1) + ”’ +
@NewText + ”’ + SUBSTRING([‘ + @COLUMN_NAME + ‘],CHARINDEX(”’ + @OldText + ”’, [‘ + @COLUMN_NAME + ‘])+ ‘ + CAST(Len(@OldText) AS VARCHAR(100)) + ‘,Len([‘ + @COLUMN_NAME + ‘]))
WHERE [‘ + @COLUMN_NAME + ‘] LIKE ”%’ + @OldText + ‘%”
END’
EXEC sp_executesql @SQL
FETCH NEXT FROM TABLES INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
END
CLOSE TABLES
DEALLOCATE TABLES
FETCH NEXT FROM TextUpdate INTO @OldText, @NewText
END
CLOSE TextUpdate
DEALLOCATE TextUpdate

Advertisements

About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s