What the heck is this all about? What's all this code? Let's say you run a blog, a store, a forum, anything. Let's say that you've decided to change the name of your site from "TheOldCompanyName" to "TheNewCompanyName" and all of the areas in the blog, store, or anywhere on your site where you use that word needs to be changed. Let's say that your pages are not your simple HTML pages. Instead, the content of your site sits inside a database - like *ahem* SQL Server 2005 or something.
Basically, I had this situation where I wanted to keep my old site but wanted to change the domain name slightly like "http://archive.radcastro.com". Previously it was called "http://www.radcastro.com". But since I changed the domain of the old site all of its references which use "http://www.radcastro.com" are broken. So if you used something like <img src="http://www.radcastro.com/Photos/Family.jpg"> it would break. Why? Because now it should be <img src="http://archive.radcastro.com/Photos/Family.jpg"> for the image to display. So below is a little sql script that you can run on SQL Server 2000 or 2005 that will allow you to enter a table of your choosing and replace any phrase or text. It's pretty powerful stuff and this is why I blogged it. God knows that I'll use something like this in the future. Imagine having an online store and you want to rename hundreds of products. Super useful stuff.
_________________________________________________________________________
USE YourDatabaseNameHere; -- replace with your databasename
GO
SET NOCOUNT ON;
DECLARE @TextPointer BINARY(16), @TextIndex
INT, @oldString VARCHAR(32),-- change to VARCHAR
@newString VARCHAR(32),-- change to VARCHAR
@lenOldString INT,@currentDataID INT;
SET @oldString = 'SOME STRING YOU WANT REPLACE'; -- remove N
SET @newString = 'THE STRING THAT WILL REPLACE THE OLD ONE'; -- remove N
IF CHARINDEX(@oldString, @newString) > 0
BEGIN PRINT'Quitting to avoid infinite loop.';
END
ELSE
BEGIN SELECT'Before replacement:';
SELECT A_Unique_Identifier_ColumnName, A_Column_That_Is_NText_or_Text FROM Community_Articles;
SET @lenOldString =DATALENGTH(@oldString);-- remove /2
DECLARE irows CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT A_Unique_Identifier_ColumnName
FROM dbo.Community_Articles
WHEREPATINDEX('%'+@oldString+'%', A_Column_That_Is_NText_or_Text)> 0;
OPEN irows;
FETCH NEXT FROM irows INTO @currentDataID;
WHILE(@@FETCH_STATUS= 0)
BEGIN
SELECT @TextPointer =TEXTPTR(A_Column_That_Is_NText_or_Text), @TextIndex
=PATINDEX('%'+@oldString+'%', A_Column_That_Is_NText_or_Text) FROM dbo
.Community_Articles WHERE A_Unique_Identifier_ColumnName
= @currentDataID;
WHILE (
SELECT PATINDEX('%'+@oldString+'%', A_Column_That_Is_NText_or_Text)
FROM dbo.Community_Articles
WHERE A_Unique_Identifier_ColumnName = @currentDataID
)> 0 BEGIN
SELECT @TextIndex =PATINDEX('%'+@oldString+'%', A_Column_That_Is_NText_or_Text)-1
FROM dbo.Community_Articles
WHEREA_Unique_Identifier_ColumnName = @currentDataID;
UPDATETEXT dbo.Community_Articles.A_Column_That_Is_NText_or_Text @TextPointer @TextIndex @lenOldString @newString;
END
FETCH NEXT FROM irows INTO @currentDataID;END
CLOSE irows;
DEALLOCATE irows;
SELECT'After replacement:';
SELECT A_Unique_Identifier_ColumnName, A_Column_That_Is_NText_or_Text FROM Community_Articles;
END Credit to the guys at mssql.com - Thank you! Rad