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?

Solution 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 :)

Solution 2

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

Solution 3

We can have stored procedure to trim specific table under specific schema. If we have different schema names other than default dbo schema, it is better to use this SP by passing schema name and table name. This performs both LTRIM and RTRIM. This SP would check char, nchar, varchar, nvarchar columns.

CREATE PROCEDURE [dbo].[TrimAllColumnsOfTable] @SchemaName Varchar(100),@TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

USAGE: [TrimAllColumnsOfTable] 'SchemaName','TableName'

Solution 4

It is perfect... But remember to put also the where clause:

COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'IsComputed') = 0

Ohterwise you will get an error if the table has a computed column of "%char%" type!

View more solutions

Comments

[Shiva]

It is better to have DATA_TYPE Like '%char%' otherwise we may miss char and nchar datatype fields.

[AdaTheDev]

@Shiva - no. CHAR and NCHAR values are always padded with whitespace so this would serve no purpose. NVARCHAR could be included, but the OP's questions was specifically around VARCHARs

[Shiva]

@AdaTheDev - Right.. DATA_TYPE Like '%varchar%' would be fine.

[user327301]

I'm not sure if I'm missing something here, but when I run this in SQL Server 2008 nothing prints. I just get "Query executed successfully."

[Ryan Shripat]

@raoulcousins PRINT won't print anything if the string is null. In my case, I had no varchar columns, only nvarchar, so the @ SQL variable was empty. Changing the DATA_TYPE to nvarchar produced output for me.

[marky]

This works great. I find myself needing this from time to time, so I'm wondering how it would be utilized in a user-defined function. I tried just putting the code in a function and running exec on the @SQL, but I then found out you can't use exec from within a function...