I've been slammed before for posting so called "solutions" on newsgroups, but it was all for the best since it taught me a few lessons. This is being posted for a couple of reasons. The first one is that I didn't find any other posts that had a solution
to this problem. The other is that I figure someone out there will have improvements on it and I'd like to learn them. The process is rather ugly, involving cursors, temp tables, direct calls to the system tables (instead of the recommended stored
procedure method) but it seems to be working. Hopefully this thread will be helpful to others in the future.

------------------------------------------------------------------------------------------------------------------------
-- How to replace a string value across all char, varchar, text, nchar, nvarchar and ntext fields in all user tables. --
------------------------------------------------------------------------------------------------------------------------

/* ============================================================ */
/* SP: sp_textreplace */
/* ============================================================ */
/* If procedure already exists...delete it */
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'sp_textreplace'
AND type = 'P')

BEGIN
DROP PROC sp_textreplace
END

GO

/**********************************************************/

CREATE PROCEDURE sp_textreplace

/*** Proc: sp_textreplace *********************************/
/* Description: */
/* A procedure that does replacement of text in a field */
/* defined by the user and of a TEXT data type. */
/* */
/* The SQL REPLACE fn doesn't work on TEXT data types, so */
/* we have to break the TEXT field into multiple VARCHARs */
/* and do REPLACE on those, then place them back into the */
/* field value through a text pointer. */
/* */
/* It is strongly advised that this script only executes */
/* during a time when nobody will be accessing the data. */
/* */
/* Input: table_name, text_field_name, record_id, */
/* find_text, replace_text */
/* */
/* Developer Task Date */
/* ------------------------------- ---- -------- */
/* Bri Gipson 06-22-2004 */
/* Created */
/* */
/* Bri Gipson 06-25-2004 */
/* Genericised to handle any table/field instead of */
/* just the table and field of a hard-coded object. */
/* (previously meta_data_glob.glob) */
/**********************************************************/

/* Input Values */
@table_name VARCHAR(256),
@field_name VARCHAR(256),
@record_id NUMERIC,
@find VARCHAR(256),
@replace VARCHAR(256)

/* Output Values */
-- None

AS
BEGIN

/* local variables */
DECLARE @ptr BINARY(16)
DECLARE @str VARCHAR(8000)
DECLARE @len BIGINT
DECLARE @del INT
DECLARE @pos INT
DECLARE @current_id NUMERIC
DECLARE @first_id NUMERIC
DECLARE @last_id NUMERIC
DECLARE @populate VARCHAR(8000)
DECLARE @update VARCHAR(8000)

CREATE TABLE #glob ( glob_id NUMERIC, glob_ptr BINARY(16), glob_length BIGINT, glob TEXT )

IF ( @record_id = 0 )
SET @populate = 'INSERT INTO #glob ( glob_id, glob_ptr, glob_length, glob ) SELECT ' + @table_name + '_id, tptr = TEXTPTR( ' + @field_name + ' ), dlength = DATALENGTH( ' + @field_name + ' ), tfield = ' + @field_name + ' FROM ' + @table_name
ELSE
SET @populate = 'INSERT INTO #glob ( glob_id, glob_ptr, glob_length, glob ) SELECT ' + @table_name + '_id, tptr = TEXTPTR( ' + @field_name + ' ), dlength = DATALENGTH( ' + @field_name + ' ), tfield = ' + @field_name + ' FROM ' + @table_name + ' WHERE
' + @table_name + '_id = ' + CONVERT( VARCHAR(10), @record_id )

EXEC ( @populate )

DECLARE glob_cursor CURSOR FOR
SELECT glob_id, glob_ptr, glob_length
FROM #glob

OPEN glob_cursor
FETCH NEXT FROM glob_cursor INTO @current_id, @ptr, @len

WHILE @@FETCH_STATUS = 0
BEGIN
SET @pos = 1

WHILE @pos < @len
BEGIN
-- Do our replacement:

-- Grab our substring of up to 4000 bytes.
-- We do only 4000 to leave room for replacement
-- that occurrs later on. Since REPLACE can only
-- return 8000 bytes, we need to greatly shrink our
-- original string to occomodate potential expansion
-- of the text.
SELECT
@str = REPLACE( SUBSTRING( glob, @pos, 4000 ), @find, @replace )
FROM
#glob
WHERE
glob_id = @current_id

-- Define values for the variables we send to UPDATETEXT
SELECT @pos = @pos - 1, @del = DATALENGTH( @str )

-- UPDATE our GLOB with the new replaced string
SET @str = REPLACE( @str, '''', '''''' )
SET @update = 'DECLARE @ptr BINARY(16) SET @ptr = CONVERT( BINARY(16), CONVERT( UNIQUEIDENTIFIER, ''' + CONVERT( VARCHAR(255), CONVERT( UNIQUEIDENTIFIER, @ptr ) ) + ''' ) ) ' +
'UPDATETEXT ' + @table_name + '.' + @field_name + ' ' +
'@ptr ' +
CONVERT( VARCHAR(255), @pos ) + ' ' +
CONVERT( VARCHAR(256), @del ) + ' '''
-- PRINT @update
EXECUTE ( @update + @str + '''' )

-- UPDATETEXT
-- meta_data_glob.glob @ptr
-- @pos
-- @del
-- @str

-- If we had a full plate of 4000 bytes, we move forward 100 bytes less.
-- because we want some overlap in case we split a word in two from
-- our current replace cycle.
-- If we don't have a full plate, that means we're at the end.
SELECT @pos = CASE WHEN @del < 4000 THEN @len + 1 ELSE @pos + 5901 END
END

FETCH NEXT FROM glob_cursor INTO @current_id, @ptr, @len
END

CLOSE glob_cursor
DEALLOCATE glob_cursor
END

/* Use these commands to test FROM the command line: */
/* EXECUTE sp_textreplace 'meta_data_glob', 'glob', 0, 'house', 'mouse' */
GO



/* ======================================================================= */
/* File name: Replace All Strings.sql */
/* DBMS name: Microsoft SQL Server 2000 */
/* Created on: 06-22-2004 */
/* Last Modified: 06-22-2004 */
/* Description: */

I have a routine for handling replace() in TEXT columns also.
http://www.aspfaq.com/2445

--
http://www.aspfaq.com/
(Reverse address to reply.)

[quoted text, click to view]