删除尾随空格并更新 SQL Server 中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14781999/
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
Remove Trailing Spaces and Update in Columns in SQL Server
提问by AGM Raja
I have trailing spaces in a column in a SQL Server table called Company Name.
我在名为Company Name.
All data in this column has trailing spaces.
此列中的所有数据都有尾随空格。
I want to remove all those, and I want to have the data without any trailing spaces.
我想删除所有这些，并且我希望数据没有任何尾随空格。
The company name is like "Amit Tech Corp "
公司名称就像 "Amit Tech Corp "
I want the company name to be "Amit Tech Corp"
我希望公司名称是 "Amit Tech Corp"
回答by rs.
回答by Robin Day
To just trim trailing spaces you should use
要修剪尾随空格，您应该使用
UPDATE
TableName
SET
ColumnName = RTRIM(ColumnName)
However, if you want to trim all leading and trailing spaces then use this
但是，如果要修剪所有前导和尾随空格，请使用此
UPDATE
TableName
SET
ColumnName = LTRIM(RTRIM(ColumnName))
回答by Hiram
Well here is a nice script to TRIM all varchar columns on a table dynamically:
好吧，这是一个很好的脚本，可以动态修剪表上的所有 varchar 列：
--Just change table name
declare @MyTable varchar(100)
set @MyTable = 'MyTable'
--temp table to get column names and a row id
select column_name, ROW_NUMBER() OVER(ORDER BY column_name) as id into #tempcols from INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar', 'nvarchar') and TABLE_NAME = @MyTable
declare @tri int
select @tri = count(*) from #tempcols
declare @i int
select @i = 0
declare @trimmer nvarchar(max)
declare @comma varchar(1)
set @comma = ', '
--Build Update query
select @trimmer = 'UPDATE [dbo].[' + @MyTable + '] SET '
WHILE @i <= @tri
BEGIN
IF (@i = @tri)
BEGIN
set @comma = ''
END
SELECT @trimmer = @trimmer + CHAR(10)+ '[' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))'+@comma
FROM #tempcols
where id = @i
select @i = @i+1
END
--execute the entire query
EXEC sp_executesql @trimmer
drop table #tempcols
回答by Christoffer Lette
update MyTable set CompanyName = rtrim(CompanyName)
回答by Simon
回答by Sukanta Saha
If you are using SQL Server (starting with vNext)or Azure SQL Databasethen you can use the below query.
如果您使用的是SQL Server（从 vNext 开始）或 Azure SQL 数据库，则可以使用以下查询。
SELECT TRIM(ColumnName) from TableName;
For other SQL SERVER Databaseyou can use the below query.
对于其他SQL SERVER 数据库，您可以使用以下查询。
SELECT LTRIM(RTRIM(ColumnName)) from TableName
LTRIM - Removes spaces from the left
LTRIM - 从左边删除空格
example: select LTRIM(' test ') as trim= 'test '
例子：select LTRIM(' test ') as trim='test '
RTRIM - Removes spaces from the right
RTRIM - 从右边删除空格
example: select RTRIM(' test ') as trim= ' test'
例子：select RTRIM(' test ') as trim=' test'
回答by Salim Lyoussi
I had the same problem after extracting data from excel file using ETL and finaly i found solution there :
使用 ETL 从 excel 文件中提取数据后，我遇到了同样的问题，最后我在那里找到了解决方案：
https://www.codeproject.com/Tips/330787/LTRIM-RTRIM-doesn-t-always-work
https://www.codeproject.com/Tips/330787/LTRIM-RTRIM-doesn-t-always-work
hope it helps ;)
希望能帮助到你 ;）
回答by Thilina Sandunsiri
SQL Server does not support for Trim() function.
SQL Server 不支持 Trim() 函数。
But you can use LTRIM() to remove leading spaces and RTRIM() to remove trailing spaces.
但是您可以使用 LTRIM() 删除前导空格和 RTRIM() 删除尾随空格。
can use it as LTRIM(RTRIM(ColumnName)) to remove both.
可以将其用作 LTRIM(RTRIM(ColumnName)) 来删除两者。
update tablename
set ColumnName= LTRIM(RTRIM(ColumnName))
回答by Pieter
To remove Enter:
删除Enter：
Update [table_name] set
[column_name]=Replace(REPLACE([column_name],CHAR(13),''),CHAR(10),'')
To remove Tab:
删除选项卡：
Update [table_name] set
[column_name]=REPLACE([column_name],CHAR(9),'')
回答by Arulmouzhi
If we also want to handle white spaces and unwanted tabs-
如果我们还想处理空格和不需要的制表符 -
Check and Try the below script (Unit Tested)-
检查并尝试以下脚本（单元测试）-
--Declaring
DECLARE @Tbl TABLE(col_1 VARCHAR(100));
--Test Samples
INSERT INTO @Tbl (col_1)
VALUES
(' EY y
Salem')
, (' EY P ort Chennai ')
, (' EY Old Park ')
, (' EY ')
, (' EY ')
,(''),(null),('d
f');
SELECT col_1 AS INPUT,
LTRIM(RTRIM(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(col_1,CHAR(10),' ')
,CHAR(11),' ')
,CHAR(12),' ')
,CHAR(13),' ')
,CHAR(14),' ')
,CHAR(160),' ')
,CHAR(13)+CHAR(10),' ')
,CHAR(9),' ')
,' ',CHAR(17)+CHAR(18))
,CHAR(18)+CHAR(17),'')
,CHAR(17)+CHAR(18),' ')
)) AS [OUTPUT]
FROM @Tbl;
