kwilliams
11-05-2007, 11:04 AM
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.
Please Note: I had to place a space between & and # for you to to see the code of the arrow.
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:
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:
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.
Please Note: I had to place a space between & and # for you to to see the code of the arrow.
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:
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:
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.