UPDATETEXT Problem

[kwilliams]

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/a...58(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' GOI customized it to create the following stored procedure:[code]CREATE PROCEDURE [dbo].[spRemoveArrow]@arrow binary(16) ASSELECT @arrow = TEXTPTR(details)FROM		 tblTableNameWHERE	 (details LIKE '%& #9658;%')UPDATETEXT tblTableName.details @arrow NULL 7 '-'GO

Please Note: I had to place a space between & and # for you to to see the code of the arrow....but I receive the following error message:Server: Msg 257, Level 16, State 2, Procedure spRemoveArrow, Line 0Implicit 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.

[Reg Edit]

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

That error will occur if you pass a varchar to your stored procedure's @arrow parameter.

• Why is @arrow a parameter? Just declare it in the stored procedure. Its value is set by your call to TEXTPTR.
• UPDATETEXT has an offset parameter, which you're setting to NULL. You need to set it according to where your arrow occurs in the text. So if the text is '& #9658;abcdefg', offset needs to be 0, and if text is 'abc& #9658;defg', offset needs to be 3. You can use PATINDEX to obtain the index within a TEXT, so subtract 1 to get the offset, i.e. PATINDEX('& #9658;', details) - 1
• Is there only a single record? Currently only one record would be updated by the stored procedure.
• I take it you're using SQL 2000? Because UPDATETEXT is deprecated in SQL 2005.

[kwilliams]

That error will occur if you pass a varchar to your stored procedure's @arrow parameter. - Why is @arrow a parameter? Just declare it in the stored procedure. Its value is set by your call to TEXTPTR.

I did that because that's how I had seen it done in the sample. So you're saying that I should instead do the following: SELECT TEXTPTR(details) FROM... ? if this is wrong, please let me know how I can go about fixing it.

- UPDATETEXT has an offset parameter, which you're setting to NULL. You need to set it according to where your arrow occurs in the text. So if the text is '& #9658;abcdefg', offset needs to be 0, and if text is 'abc& #9658;defg', offset needs to be 3. You can use PATINDEX to obtain the index within a TEXT, so subtract 1 to get the offset, i.e. PATINDEX('& #9658;', details) - 1

Ok, I understand that now. And I'll use the "PATINDEX('& #9658;', details) - 1" method you referenced, as I don't know before-hand where the arrow occurs for each record.

- Is there only a single record? Currently only one record would be updated by the stored procedure.

No, I want it to loop through all of the records in the table. The MS example has the following code at the beginning of the SP:USE DATABASENAMEGOEXEC spRemoveArrow 'DATABASENAME', 'select into/bulkcopy', 'true'...and this and the end of the SP:EXEC sp_dboption 'DATABASENAME', 'select into/bulkcopy', 'false'GOWould these make it loop through all of the records, or is there something else I should use?

- I take it you're using SQL 2000? Because UPDATETEXT is deprecated in SQL 2005.

Yes, I'm currently using SQL Server 2000. We're going to upgrade to 2005 in the near future hopefully, but I need this solution in the meantime.Thanks for all of your help, and I hope to hear from you soon.

[Reg Edit]

I did that because that's how I had seen it done in the sample. So you're saying that I should instead do the following: SELECT TEXTPTR(details) FROM... ?

No, the sample has:

DECLARE @ptrval binary(16)SELECT @ptrval = TEXTPTR(pr_info) ...

so you need to have:

CREATE PROCEDURE [dbo].[spRemoveArrow]ASBEGINDECLARE @arrow  binary(16) SELECT @arrow = TEXTPTR(details)...ENDGO

[...] I want it to loop through all of the records in the table. The MS example has the following code at the beginning of the SP:USE DATABASENAMEGOEXEC spRemoveArrow 'DATABASENAME', 'select into/bulkcopy', 'true'...and this and the end of the SP:EXEC sp_dboption 'DATABASENAME', 'select into/bulkcopy', 'false'GO

The example you showed does not use any SP or bulkcopy. Can you post a link to the example that has SP and bulkcopy that you refer to.

[kwilliams]

No, the sample has:

DECLARE @ptrval binary(16)SELECT @ptrval = TEXTPTR(pr_info) ...

so you need to have:

CREATE PROCEDURE [dbo].[spRemoveArrow]ASBEGINDECLARE @arrow  binary(16) SELECT @arrow = TEXTPTR(details)...ENDGO

Ok, I'll give that a try.

The example you showed does not use any SP or bulkcopy. Can you post a link to the example that has SP and bulkcopy that you refer to.

I actually already posted it in the first post in this thread. The example is at the bottom of the page.P.S. I saw that bulk copy code as something to do with running the stored procedure, but since I'm going to run it within a scheduled DTS package, I didn't see the need for it. I'll await your response on the MS example code. Thanks.

[kwilliams]

...also, I received an error when I applied your suggested changes to my SP. Here's what I have:CREATE PROCEDURE [dbo].[spRemoveArrow]ASBEGINDECLARE @arrow binary(16)SELECT @arrow = TEXTPTR(details)FROM tblTableNameWHERE (details LIKE '%& #9658;%')UPDATETEXT tblTableName.details @arrow (PATINDEX('►', details) - 1) 7 '-'GOPlease Note: I still kept the space is still between "&" and "#" to allow code to come through, but there's no space in my code.and here's the error message that I received:Error 170: Line 9: Incorrect syntax near '('.Any additional suggestions would be very helpful. Thanks for your time.

[Reg Edit]

[...] Any additional suggestions would be very helpful. Thanks for your time.

I think the sp_dboption call is a hangover from SQL Server 7, which gave error messages unless you did that when using UPDATETEXT. If omitting it doesn't give error messages, I think you can leave it out.I've renamed your @arrow as it's confusing me with that name--the text pointer points to the start of the details field, not the arrow position within it. Call it what you will but I've called it @txtptr in the example code below.To update multiple records, use a cursor. This will enable you to loop through all the matching records. For instance:

CREATE PROCEDURE [dbo].[spRemoveArrow]AS	BEGIN	DECLARE @txtptr binary(16)	DECLARE @offset int	DECLARE mycursor CURSOR FAST_FORWARD	FOR 		SELECT TEXTPTR(details), PATINDEX('%& #9658;%', details) - 1 		FROM tblTableName		WHERE details LIKE '%& #9658;%'	OPEN mycursor	FETCH NEXT FROM mycursor INTO @txtptr, @offset	WHILE @@FETCH_STATUS = 0		BEGIN		UPDATETEXT tblTableName.details @txtptr @offset 7 '-'		FETCH NEXT FROM mycursor INTO @txtptr, @offset		END	CLOSE mycursor	DEALLOCATE mycursor	ENDGO

Note that your approach so far only handles a single occurrence of the string within the details field. If there can be multiple occurrences within one details field, this approach will not handle it, and things could get quite messy. In that case it will probably be best just to execute the SP again until all occurrences have been replaced (which you can determine with a SELECT COUNT).

[kwilliams]

I copied and pasted the revised code you provided into my SP, ran it, and it was successful. I did a count on the DB table as I ran the job, and it counted occurrences of arrows in this count pattern: 75 (initial count), 53, 26, 15, 9, 5, 3, 2, 2, 0. I manually ran the job on purpose to make sure that it was working on my side, which it did...yeah:)So I see that the next step is to add a loop for the job using the SELECT COUNT method that you noted. You've already helped me a great deal, so I don't want to request for you to write more code for me. But if you could give me an idea of how I should do it with the revised code and/or point me in the right direction, that would be great. Thanks again Reg Edit.

[Reg Edit]

I copied and pasted the revised code you provided into my SP, ran it, and it was successful. I did a count on the DB table as I ran the job, and it counted occurrences of arrows in this count pattern: 75 (initial count), 53, 26, 15, 9, 5, 3, 2, 2, 0. I manually ran the job on purpose to make sure that it was working on my side, which it did...yeah:)So I see that the next step is to add a loop for the job using the SELECT COUNT method that you noted. You've already helped me a great deal, so I don't want to request for you to write more code for me. But if you could give me an idea of how I should do it with the revised code and/or point me in the right direction, that would be great. Thanks again Reg Edit.

Something like:

DECLARE @numleft INTSELECT @numleft = count(*) FROM tblTableName WHERE details LIKE '%►%'WHILE @numleft > 0	BEGIN	-- execute the stored procedure here	PRINT @numleft -- for testing	SELECT @numleft = count(*) FROM tblTableName WHERE details LIKE '%►%'	END

BTW when you come to replace other characters it would make sense to pass two parameters to your stored procedure (the original and replacement strings) instead of hard-coding these in the SP, so you can reuse the same SP.

[kwilliams]

Something like:

DECLARE @numleft INTSELECT @numleft = count(*) FROM tblTableName WHERE details LIKE '%►%'WHILE @numleft > 0	BEGIN	-- execute the stored procedure here	PRINT @numleft -- for testing	SELECT @numleft = count(*) FROM tblTableName WHERE details LIKE '%►%'	END

BTW when you come to replace other characters it would make sense to pass two parameters to your stored procedure (the original and replacement strings) instead of hard-coding these in the SP, so you can reuse the same SP.

Thanks for the suggested code...I'll work on adding that today, and I'll let you know when/if I'm successful.I agree that it would be a good idea to make it available for multiple options, as I'm also trying to remove a hollow square from the table. But I'm not having success with that at all, as the SP is saying that there are no records with the '□' character. But when I view the data through an ASPX front-end page, the hollow square still shows up. I've tried using '□' and '□' within the SP without success. If you have any information on the hollow square character within SQL Server, please let me know, as I'm truly stumped on that one.But at least the arrow can be removed without a problem:)

[Reg Edit]

Thanks for the suggested code...I'll work on adding that today, and I'll let you know when/if I'm successful.I agree that it would be a good idea to make it available for multiple options, as I'm also trying to remove a hollow square from the table. But I'm not having success with that at all, as the SP is saying that there are no records with the '□' character. But when I view the data through an ASPX front-end page, the hollow square still shows up. I've tried using '□' and '□' within the SP without success. If you have any information on the hollow square character within SQL Server, please let me know, as I'm truly stumped on that one.But at least the arrow can be removed without a problem:)

Where are you seeing the hollow square? In the database, on a web page, or what? The hollow square may just mean a certain character code has no mapping to a character in the current font (i.e. it could be anything). In that case you'll need to check what actual data you're dealing with, so that you know what to search on. Presumably the range of possible characters is known, and not arbitrary?

[kwilliams]

Ahah. I figured it out. I was using this hollow square: □, when I should have been using this hollow square: . Pretty strange.Anyway, they are actually being stored in the DB table with the actual square, not the code for the square. The form that allows this data is a rich text edit form, which allows users to submit data from Word. That's where this crap data is entering from. If you or anyone could give me some suggestions of a good RTE, that would be great. In the meantime, I'll just implement this SP to remove those characters until I do get another front-end solution. Thanks for your help.