removing spaces from fields

[impulse24]

Hi,

What is the update statement I would use to clear out spaces out of fields? I have a database with over 2M records, and all of the fields of data have extra spaces. I have tried:

Update pages
set index1 = RTrim(index1)

and I just keep getting a TimeOut error. Is the above the correct syntax? And if so how do i get aroung the timeout error.

Each record is entirely unique, and all the fields of a record have different values. So I cant do a subset of records. Can anyone help.

 

[tlbroadbent]

You can do batches of updates. The following script shows how to update 20000 records at a time until all records are updated and trailing spaces removed. You can increase or decrease the number of records affected as needed for your purposes.

-- declare variable to control looping
Declare @rc int
Set @rc=1

-- Set maximum number of rows affected by Update
Set rowcount 20000

-- Create While loop
While @rc>0
Begin

Begin Transaction

-- Only update records that have trailing spaces
Update Pages
Set index1 = RTrim(index1)
Where right(index1,1)=' '

-- Store number of records updated
Set @rc = @@rowcount
Select RowsUpdated=@rc

-- Commit Transaction to clear transaction log
Commit Transaction

End Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm