SQL Server:如何对表的所有 varchar 列执行 Rtrim [英] SQL Server: How to perform Rtrim on all varchar columns of a table

问题描述

我的表中有 30 多列(sql server 2008).列类型是 varchar(x).我知道在每一列中，列值的末尾都有两个额外的空格.如何对所有列使用 rtrim 函数并将此修改保存到此现有表中?

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?

有没有办法使用存储过程或游标来做到这一点，我不必手动声明所有列?

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

推荐答案

对于通用方法，您可以使用这样的脚本为给定表生成语句(如果您有很多列，则很有用！):

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

那只会打印出 SQL 语句.然后，您可以复制 + 运行该语句，或者只是 EXECUTE(@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 :)

这篇关于SQL Server:如何对表的所有 varchar 列执行 Rtrim的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！