October 12, 2007 at 2:58 pm
Hello!
I have an address column that is of ntext data type. There are carriage returns in the data that I need to remove during a select statement. I've tried to do a REPLACE function by replacing chr(13) with null. That didn't work so I just tried to replace an 'a' with an 'A' (REPLACE(Address, 'a', 'A')) and got the same error: "Argument data type ntext is invalid for argument 1 of replace function." I ran the exact syntax on a column that was not an ntext and it worked just fine. It seems that SQL Server doesn't like that replace function on ntext data types.
Is there something I'm missing or is there another way of doing this?
Thanks!
-Bill
October 12, 2007 at 3:40 pm
Hi Bill,
this is unfortunately a limitation of the LOB (text, ntext and image) datatypes. This has been improved in 2005 with the new LOM data types ([n]varchar(max) and varbinary(max)), but in 2000 you will still need to use TEXTPTR and UPDATETEXT http://msdn2.microsoft.com/en-us/library/ms189466.aspx
Regards,
Andras
October 12, 2007 at 3:56 pm
As a follow up, if your LOB data is smaller than 8000 bytes, you could of course convert it to varchar(8000) and do a replace on that.
To see an example how to do replace using the UPDATETEXT have a look at http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html
It may of course be easier to write a small external application (text, ntext and image columns are really a pain)
Regards,
Andras
October 12, 2007 at 4:01 pm
Thanks Andras! I'll try that. I am going to be in the process of converting this database over to 2005 so it seems that it will be easier in there.
-Bill
October 12, 2007 at 5:23 pm
here's a find and replace for a TEXT field in SQL 2000;
in this example, i'm replacing a relative link with a full link, so it's a good example:
DECLARE @reviewid int, @ptr binary(16), @idx int
SET @reviewid = 0
SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body) - 1
FROM reviews
WHERE PATINDEX('%href="reviews%',review_body) > 0
WHILE @reviewid > 0
BEGIN
SELECT @ptr = TEXTPTR(review_body)
FROM reviews
WHERE reviewid = @reviewid
UPDATETEXT reviews.review_body @ptr @idx 13 'href="http://www.somewebsite.com/reviews'
SET @reviewid = 0
SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body)-1
FROM reviews
WHERE reviewid > @reviewid
AND PATINDEX('%href="reviews%',review_body) > 0
END
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply