In this SQL code snippet we go over a method to do a search and replace on text fields within a SQL database. This code came in particularly handy when converting over from DNN 1.x to DNN 2.x where the portal directories changed from a long hex based GUID to a simple integer value.

ALTER PROCEDURE dbo.SearchAndReplace

--here we'll accept some values for the the search string and the replace string.
@FindString varchar(8000),
@ReplaceString varchar(8000)
AS
SET NOCOUNT ON

DECLARE @TextPointer varbinary( 16 )
DECLARE @DeleteLength int
DECLARE @OffSet int

SELECT @TextPointer = textptr( MyTextField)
FROM MyTable
SET @DeleteLength = len( @FindString )
SET @OffSet = 0
WHILE ( SELECT count( * )
FROM MyTable
WHERE patindex( '%' + @FindString + '%', MyTextField) <> 0
) > 0
BEGIN
SELECT @OffSet = patindex( '%' + @FindString + '%', MyTextField) - 1
FROM MyTable
WHERE patindex( '%' + @FindString + '%', MyTextField) <> 0
UPDATETEXT MyTextField
@TextPointer
@OffSet
@DeleteLength
@ReplaceString
END
RETURN