SQL Server：如何对表的所有 varchar 列执行 Rtrim
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2552154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: How to perform Rtrim on all varchar columns of a table
提问by atricapilla
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?
我的表中有 30 多列（sql server 2008）。列类型是 varchar(x)。我知道在每一列中，列值的末尾都有两个额外的空格。如何对所有列使用 rtrim 函数并将此修改保存到此现有表中？
Edit: is there a way to do it using stored procedure or cursor where I don't have to manually declare all columns?
编辑：有没有办法使用存储过程或游标来做到这一点，我不必手动声明所有列？
回答by AdaTheDev
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 :)
那只会打印出 SQL 语句。然后，您可以复制 + 运行该语句，或者只是EXECUTE(@SQL). 这是未经测试的，所以请先在测试表上尝试一下:)
回答by Marcelo Cantos
UPDATE xxx
SET col1 = RTRIM(col1),
col2 = RTRIM(col2),
col3 = RTRIM(col3),
...
回答by Shiva
We can have stored procedure to trim specific tableunder specific schema. If we have different schema names other than default dboschema, it is better to use this SP by passing schema name and table name. This performs both LTRIMand RTRIM. This SP would check char, nchar, varchar, nvarcharcolumns.
我们可以有存储过程来修剪specific table下specific schema。如果我们有除默认dbo架构之外的不同架构名称，最好通过传递架构名称和表名称来使用此 SP。这同时执行LTRIM和RTRIM。此 SP 将检查char, nchar, varchar,nvarchar列。
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'
用法： [TrimAllColumnsOfTable] 'SchemaName','TableName'
回答by Flavio
It is perfect... But remember to put also the whereclause:
这是完美的......但记得还要加上where条款：
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'IsComputed') = 0
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!
否则，如果表具有计算列"%char%"类型，您将收到错误消息！
回答by Feety
The accepted answer works well. I ran into an issue with a temp table being named the same name. You can add
接受的答案效果很好。我遇到了一个名为同名的临时表的问题。你可以加
and TABLE_SCHEMA = 'dbo'
And that will get rid of collision on table names.
这将消除表名的冲突。
