SQL Server: How to perform Rtrim on all varchar columns of a table

Question

I have over 30 columns in my table (sql server 2008). Columns type are varchar(x). I know that in every column there is two extra spaces at the end of column value. How to use rtrim function for all columns and save this modification into this existing table?

Edit: is there a way to do it using stored procedure or cursor where I don't have to manually declare all columns?

Answer 1

For a generic approach, you can use a script like this to generate the statement for you, for a given table (useful if you have many columns!):

DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'YourTableName'

SELECT @SQL = COALESCE(@SQL + ',[', '[') + 
              COLUMN_NAME + ']=RTRIM([' + COLUMN_NAME + '])'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
    AND DATA_TYPE = 'varchar'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
PRINT @SQL

That will just print the SQL statement out. You can either then copy + run the statement, or just EXECUTE(@SQL). This is untested, so just try it out on a test table first :)

Answer 2

UPDATE xxx
  SET col1 = RTRIM(col1),
      col2 = RTRIM(col2),
      col3 = RTRIM(col3),
      ...