UPDATETEXT Problem: replace one value with another

kwilliams · November 5th, 2007, 12:04 PM

I need to replace a few strange characters that are located in a table field that has a text datatype. My first try is to replace an arrow character (code: #9658;) with a dash.

I found a MS article on the UPDATETEXT method at http://msdn2.microsoft.com/en-us/library/aa260658(sql.80).aspx

...which provides the following example:
[code]DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(pr_info)
   FROM pub_info pr, publishers p
      WHERE p.pub_id = pr.pub_id
      AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'
GO

I customized it to create the following stored procedure:

Code:

CREATE PROCEDURE [dbo].[spRemoveArrow]
@arrow binary(16) AS

SELECT @arrow = TEXTPTR(details)
FROM         tblTableName
WHERE     (details LIKE '%#9658;%')

UPDATETEXT tblTableName.details @arrow NULL 7 '-'
GO

...but I receive the following error message:
Server: Msg 257, Level 16, State 2, Procedure spRemoveArrow, Line 0
Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

I've messed around with it a bit to see if I could fix the problem, but I can't figure out how to do this. If anyone could let me know what I'm doing wrong, it would be greatly appreciated. Thanks.

KWilliams