Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing Carriage Returns in a Select Statement Expand / Collapse
Author
Message
Posted Friday, October 12, 2007 2:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2007 12:31 PM
Points: 2, Visits: 4
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
Post #410202
Posted Friday, October 12, 2007 3:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #410210
Posted Friday, October 12, 2007 3:56 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #410213
Posted Friday, October 12, 2007 4:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2007 12:31 PM
Points: 2, Visits: 4
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
Post #410214
Posted Friday, October 12, 2007 5:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:21 PM
Points: 12,744, Visits: 31,062
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #410228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse