Hi,

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.

Thanks!

Mintyman (mintyman (AT) ntlworld (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks Erland. I was hoping there would be a nice, easy way to do this (

Thanks for the options though, i'll look into the SQL Express option you
talk of. Failing that, i'll try one of the other ones.

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Mintyman (mintyman (AT) ntlworld (DOT) com) writes:
Anything easy with the text data type? Gee, you must believe in Santa Claus
too! :-)



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Or use BCP in character mode, dump out into a perl script, modify, and BCP
back in.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com