Article: Search and Replace in a TEXT column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Search and Replace in a TEXT column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-18 : 15:07:56

This article was written by Davide Mauri. He writes "If you ever worked with tables using the text datatype you have found that you cannot use the REPLACE TSQL function on it. Ok, that’s fine we know that the text datatype has some drawbacks, but we still need to use it (for example I cannot work without it, since a great part of my work is to build content management systems). Davide shows us a way to accomplish this using the UPDATETEXT function.

Article Link.

javamick
Starting Member

7 Posts

Posted - 2004-08-04 : 15:35:36

Davide, this is a great idea! I've tried this and written my own stored procedure based on your code, but it seems that this part

where
TargetField
like
'%' + @otxt +'%'

does not work paste 8000 or so chars. Any idea on how to get around this?

Thanks!

--
Micky McQuade
www.mcquade.com

manowar
Starting Member

1 Post

Posted - 2004-11-30 : 11:54:52

First of all sorry for answering only now, i have no excuses on that, i completely forgot to check the forum and so i read your post only now.

Probably you don't need my answer anymore, so i write it just for the community.

What you ask is not possibile due to SQL Server 2000 limitations. You cannot have a varchar type to contain more then 8000 chars, and you cannot also use text for declarinng a variable.

Davide Mauri
http://www.davidemauri.it

weissjd
Starting Member

2 Posts

Posted - 2005-02-04 : 17:44:32

Actually, the like should work fine after 8000 characters. Like is fully supported on text fields. I think the problem is that the code only handles the first occurence of the string to be replaced.

I created a stored proc that handles this. It also allows you to pass the table name, field name, integer primary key, old and new text. It's a bit kludgy as I had problems calling updatetext with an exec due to the binary pointer value that must be passed. If anyone can think of a better way to do this, let me know.



CREATE PROCEDURE sp_textreplace
	@table_name varchar (30),
	@field_name varchar (30),
	@key_name varchar (30),
	@otxt varchar (1000),
	@ntxt varchar (1000)

AS

begin tran

declare @txtlen int
set @txtlen = len(@otxt)
declare @txtlenchar as varchar(30)
set @txtlenchar=Cast(@txtlen as varchar(30))

exec('declare curs cursor 
for
select ' +
	@key_name + ', 
	textptr(' + @field_name + ')
from ' +
	@table_name + '
where ' +
	@field_name + '
like 
	''%' + @otxt + '%''')

open curs

exec('

declare @key int 
declare @pos int 
declare @ptr binary(16) 
declare @nextpos int

fetch next from curs into @key, @ptr
while @@fetch_status = 0
	begin
		set @nextpos=1
		select @pos=charindex(''' + @otxt + ''', ' + @field_name + ', @nextpos) - 1
			FROM ' + @table_name + ' WHERE ' + @key_name + ' = Cast(@key as Varchar(30))

		WHILE @pos>0
			BEGIN
				updatetext ' + @table_name + '.' + @field_name + ' @ptr @pos ' + @txtlenchar + ' ''' + @ntxt + '''
			
				SET @nextpos=@pos + ' + @txtlenchar + ' + 1

				select @pos=charindex(''' + @otxt + ''', ' + @field_name + ', @nextpos) -1
					FROM ' + @table_name + ' WHERE ' + @key_name + ' = Cast(@key as Varchar(30))
			END
		fetch next from curs into @key, @ptr
	end

')

close curs
deallocate curs

commit tran

quote:
Originally posted by javamick

Davide, this is a great idea! I've tried this and written my own stored procedure based on your code, but it seems that this part

where
TargetField
like
'%' + @otxt +'%'

does not work paste 8000 or so chars. Any idea on how to get around this?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-04 : 19:55:49

see
http://www.mindsdoor.net/SQLTsql/ReplaceText.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

weissjd
Starting Member

2 Posts

Posted - 2005-02-04 : 21:44:56

That code will indeed replace every occurrence of the string. However, it has a problem if the the new string contains the old string. For example, assign 'jjj' to @old and 'jjj1' to @new and run the code. You'll end up in an infite loop because after replacing jjj with jjj1, it then replaces the new jjj with jjj1 and so on.

You can see in the script that I posted above that I use charindex() instead of patindex(). This allows me to update the position each time a replacement is made and only search the remainder of the text in the column each time through the loop.

quote:
Originally posted by nr

see
http://www.mindsdoor.net/SQLTsql/ReplaceText.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-04 : 22:33:36

Except that charindex doesn't work on text datatypes.
I'll leave it to you to think of the simple change to my script to cater for teh scenario you have pointed out.

>> I use charindex() instead of patindex(). This allows me to update the position each time a replacement is made
Except that the script doesn't.
As given this would suffer from the same problem - that is, if it worked at all.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-05 : 11:56:01

Someone else wanted to replace data in a test column with the replacement text including the replaced text so I've amended the script here.
http://www.mindsdoor.com/topic.asp?TOPIC_ID=18

I might add it to my web site when I get time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-06 : 10:52:34

Have added it here
http://www.mindsdoor.net/SQLTsql/ReplaceText2.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakrati
Starting Member

1 Post

Posted - 2005-02-18 : 15:34:44

Ok so I am confused. Which code can I use if I want to find and replace a string in image datatype. When I try to use David's code, I cant come around converting image to binary to varchar...
Please help.....
Somewhere it mentions use full-text indexing, but not sure how to do that. please help!!!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-21 : 09:12:33

You can't do it for an image only for text.
An image is expected to be a binary string and not parsable (is that a word?).

You can use other methods (e.g. full text) to index the data by specifying the internal format type (e.g. a word document).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rlovetx
Starting Member

1 Post

Posted - 2006-09-25 : 04:41:28

Hey, the script works perfectly for me...except when the @pos value goes to -1 (when the match is at position 0). Is there anything I can do to get it to work in this scenario?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-25 : 09:09:10

see
http://www.mindsdoor.net/SQLTsql/ReplaceText2.html

and are you sure it works. It depends on your data - see my comments earlier in the thread.
The 0's you are finding are probably due to that problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dconlisk
Starting Member

1 Post

Posted - 2007-10-02 : 10:10:52

Hi guys,

It's been a while since this forum was last updated. I tried using various of the scripts provided (or linked to) above - but with no luck. My data table has the "text in row" parameter set to true, and I seemed to have problems with getting a pointer to the text data. Being a programmer and not a DBA, I gave up on the pure SQL route in the end.

If any of you are .net heads then maybe you could benefit from looking at the solution I came up with for myself. You can find it here:

http://equatorlive.com/blogs/dotnetdave/tech/sql-search-and-replace-on-text-type-columns/

Good luck!

Dave
-------------
David Conlisk
Web Developer
http://equatorlive.com/blogs/dotnetdave/

fredclown
Starting Member

1 Post

Posted - 2008-06-26 : 17:13:34

If you use SQL 2005 you can use replace with a text type. All you have to do is the below ...

field = replace(cast(field as varchar(max)),'string' ,'replacement')

Easy as pie.

mack2
Starting Member

1 Post

Posted - 2008-07-23 : 00:40:50

Hey, the script works perfectly for me...except when the @pos value goes to -1 (when the match is at position 0). Is there anything I can do to get it to work in this scenario?
mack2
Wide Circles
[url="http://www.widecircles.ca"]
Wide Circles[/url]

skbharat
Starting Member

1 Post

Posted - 2009-09-03 : 08:20:55

Hi davide, please help me.
i am using this Query to replace the text which lies in around 400 rows in a table but im getting error because of some space between the replacing code.
the code is :
declare @otxt varchar(2000)
declare @ptr binary(16)
declare @pos int
declare @Moduleid int
declare @ntxt varchar(2000)
set @ntxt = '</td><td></td><td colspan="2" valign="top"><a href="http://www.consona.com/CRM/ResourceLibrary.aspx">'
set @otxt = '</td>
<td>
</td>
<td colspan="2" valign="top">
<a href="http://www.consona.com/CRM/ResourceLibrary.aspx">'
declare @txtlen int
set @txtlen = len(@otxt)
declare curs cursor local fast_forward
for
select
Moduleid,
textptr(HTMLContent),
charindex(@otxt, HTMLContent)-1
from
HTMLContent
where
HTMLContent
like
'%' + @otxt +'%'
open curs
fetch next from curs into @Moduleid, @ptr, @pos

while @@fetch_status = 0
begin
print 'Text found in row id=' + cast(@Moduleid as varchar) + ' at pos=' + cast(@pos as varchar)

updatetext HTMLContent.HTMLContent @ptr @pos @txtlen @ntxt
fetch next from curs into @Moduleid, @ptr, @pos
end

close curs
deallocate curs

and the error is:
----------------------
Text found in row id=3 at pos=-1
Msg 7135, Level 16, State 2, Line 33
Deletion length 145 is not in the range of available text, ntext, or image data.
The statement has been terminated.
-----------------

please help me... waiting for reply

Hi

nedshah
Starting Member

1 Post

Posted - 2010-02-02 : 10:52:51

quote:
Originally posted by fredclown

If you use SQL 2005 you can use replace with a text type. All you have to do is the below ...

field = replace(cast(field as varchar(max)),'string' ,'replacement')

Easy as pie.

Two thumbs up to Fredclown!!! command work like a charm for me as well.
This is what I wrote my Update statement to Find and Replace in a Text field in SQL server 2005 database

UPDATE TableName
SET DBTextField = REPLACE(CAST(DBTextField AS varchar(MAX)), 'SearchText', 'ReplaceText')
FROM TableName
WHERE CHARINDEX('SearchText',CAST(DBTextField as varchar(MAX)))>0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources