Posted by Erland Sommarskog on 02/21/07 11:42

Mintyman (mintyman@ntlworld.com) writes:
> I have erronous white space at the end of my 'description' field within my
> 'product' table. I tried using the RTRIM function but it won't let me
> because it is a TEXTBLOB (text) field.
>
> Can anyone show me how to write a query that will update all my rows
> automatically?
>
> I'm using SQL Server 2000.

The easiest would almost be to download SQL Express, copy the data
over to a table in SQL 2005, but instead of using text, have a varchar(MAX)
column, on which you can apply rtrim. Then copy back.

To do it in SQL 2000 only, you would have to use UPDATETEXT, and you would
have to work one row at a time. I think you would have to read the last
1000 characters or so, with substring, into a varchar variable and then
write back with UPDATETEXT.

If many of the rows have descriptions shorter than 8000, you could probably
do something like:

col = rtrim(convert(varchar(8000), texttol))

Yet another option that may work is say:

SET ANSI_PADDING OFF
CREATE TABLE #temp (keycol int NOT NULL, textcol text)

Insert data into #temp and update back with UPDATE. When the setting
ANSI_PADDING OFF, trailing spaces are automatically stripped off.

As you see, all options I have presented are fairly complex kludges. The
new MAX data types in SQL 2005 are so much easier to work with.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]