A REPLACE function for a TEXT field

				
--**************************************
-- Name: A REPLACE function for a TEXT field
-- Description:Generically, the Replace function in SQL Server 
doesn't support replacing a particular string 
with another within a TEXT field; this can.
-- By: J Shortland
--
-- Assumes:Replace <TableName> with the name of the table with the TEXT field
Replace	<ColumnName> with the name of the TEXT field
Replace <String1> with the string to look for
Replace <String2> with the string to replace with
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=367&lngWId=5--for details.--**************************************

/*********************************************************************
--			
-- Name			ReplaceBlob
-- Date Created:	30/11/2001
-- Created By:		J Shortland
-- Description:		Generically, the Replace function in SQL Server 
--			doesn't support replacing a particular string 
--			with another within a TEXT field; this can.
--			
-- Steps Performed:	Step 01 : Obtain the handle for the TEXT field to be processed
--			Step 02 : Set the search string
--			Step 03 : Set the replacement string
--			Step 04 : Set the delete length of string to be removed from the TEXT field
--			Step 05 : Initialise the starting position for the update
--			Step 06 : Loop thru table until all instances of the search string are replaced
--			Step 06a : Set the starting position for the update
--			Step 06b : Replace the search string with the replacement string
--			
-- Instructions:	Replace <TableName> with the name of the table with the TEXT field
--			Replace	<ColumnName> with the name of the TEXT field
--			Replace <String1> with the string to look for
--			Replace <String2> with the string to replace with
--			
-- History:		
-- |-------------------------------------------------------------------|
-- | 30/11/2001		JHS		Creation
-- |-------------------------------------------------------------------|
--			
-- ********************************************************************/
		
set nocount on	
		
declare		@TextPointer	varbinary( 16 )	-- Stores the handle for the TEXT field to be processed
declare		@ReplaceStr	varchar( 8000 )	-- Stores the replacement string
declare		@SearchStr	varchar( 8000 )	-- Stores the search string
declare		@DeleteLength	int		-- Stores the length of string to be removed
declare		@OffSet		int		-- Stores the starting position for the update
		
-- Step 01 : Obtain the handle for the TEXT field to be processed
select		@TextPointer	= textptr( <ColumnName> )
from		<TableName>
		
-- Step 02 : Set the search string
set		@SearchStr	= <String1>
		
-- Step 03 : Set the replacement string
set		@ReplaceStr	= <String2>
		
-- Step 04 : Set the delete length of string to be removed from the TEXT field
set		@DeleteLength	= len( @SearchStr )
		
-- Step 05 : Initialise the starting position for the update
set		@OffSet	= 0
		
-- Step 06 : Loop thru table until all instances of the search string are replaced
while		(	select	count( * )
			from	<TableName>
			where	patindex( '%' + @SearchStr + '%', <ColumnName> ) <> 0
		) > 0
begin		
		-- Step 06a : Set the starting position for the update
		select		@OffSet = patindex( '%' + @SearchStr + '%', <ColumnName> ) - 1
		from		<TableName>
		where		patindex( '%' + @SearchStr + '%', <ColumnName> ) <> 0
		
		-- Step 06b : Replace the search string with the replacement string
		updatetext	<TableName>.<ColumnName>	-- table_name.dest_column_name
				@TextPointer			-- dest_text_ptr
				@OffSet				-- insert_offset
				@DeleteLength			-- delete_length
				@ReplaceStr			-- inserted_data
end