By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,008 Members | 2,962 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,008 IT Pros & Developers. It's quick & easy.

Use RTRIM function on TEXTBLOB (text) field

P: n/a
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!
Feb 21 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Mintyman (mi******@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, es****@sommarskog.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
Feb 21 '07 #2

P: n/a
Thanks Erland. I was hoping there would be a nice, easy way to do this :o(

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" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Mintyman (mi******@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, es****@sommarskog.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

Feb 21 '07 #3

P: n/a
Mintyman (mi******@ntlworld.com) writes:
Thanks Erland. I was hoping there would be a nice, easy way to do this :o(
Anything easy with the text data type? Gee, you must believe in Santa Claus
too! :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
Feb 21 '07 #4

P: n/a
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Mintyman (mi******@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.
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
Feb 21 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.