|
|
Posted by Mintyman on 02/21/07 12:02
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" <esquel@sommarskog.se> wrote in message
news:Xns98DE80DCF284AYazorman@127.0.0.1...
> 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]
|