Finding and Replacing commas in Text field

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

SQL Server 2000 Forums

SQL Server Development (2000)

Finding and Replacing commas in Text field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-21 : 07:52:48

Ray writes "I am using a search and replace function that I found on your web site. However when I run it I get the following message and error:

Text found in row id=- at pos=4502
Text found in row id= at pos=7940
Text found in row id= at pos=2552
Text found in row id=r at pos=-1

Server: Msg 7135, Level 16, State 2, Line 47
Deletion length 1 is not in the range of available text, ntext, or image data.
The statement has been terminated.

I labled the guilty party below. My max text field size is 64512.

The function is below:

/*
*
* Search & Replace
*
* Use Ctrl+Shift+M to replace template values
*
*/

set xact_abort on
begin tran

declare @otxt varchar(1000)
set @otxt = ','

declare @ntxt varchar(1000)
set @ntxt = ''

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id varchar

declare curs cursor local fast_forward
for
select
problem,
textptr(problem),
charindex(@otxt, problem)-1 */Guilty party*/
from
support_incident
where
problem
like
'%' + @otxt +'%'

open curs

fetch next from curs into @id, @ptr, @pos

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

updatetext support_incident.problem @ptr @pos @txtlen @ntxt

fetch next from curs into @id, @ptr, @pos
end

close curs
deallocate curs

commit tran

Thanks. Ray"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 08:02:36

What is the datatype?
If it is text, ntext then you need to convert it to varchar and replace it

Otherwise refer this
http://www.nigelrivett.net/SQLTsql/InsertTextData.html

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-21 : 08:35:47

Think you mean
http://www.nigelrivett.net/SQLTsql/ReplaceText.html
or
http://www.nigelrivett.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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-22 : 00:37:06

quote:
Originally posted by nr

Think you mean
http://www.nigelrivett.net/SQLTsql/ReplaceText.html
or
http://www.nigelrivett.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.

Yes Nigel

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources