删除尾随空格并更新SQL Server中的列

I have trailing spaces in a column in a SQL Server table called Company Name.

我在SQL Server表中名为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科技公司"

I want the company name to be "Amit Tech Corp"

我希望公司名称是"Amit科技公司"

12 个解决方案

#1

216

Try SELECT LTRIM(RTRIM('Amit Tech Corp '))

尝试选择LTRIM(RTRIM('Amit Tech Corp '))

LTRIM - removes any leading spaces from left side of string

修剪-删除任何领先的空间从左边的字符串

RTRIM - removes any spaces from right

修剪-删除任何空间从右边

Ex:

例:

update table set CompanyName = LTRIM(RTRIM(CompanyName))

#2

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

#3

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

#4

update MyTable set CompanyName = rtrim(CompanyName)

#5

If you are using SQL Server (starting with vNext) or Azure SQL Database then you can use the below query.

如果您正在使用SQL Server(从vNext开始)或Azure SQL数据库，那么您可以使用下面的查询。

SELECT TRIM(ColumnName) from TableName;

For other SQL SERVER Database you can use the below query.

对于其他SQL SERVER数据库，可以使用以下查询。

SELECT LTRIM(RTRIM(ColumnName)) from TableName

LTRIM - Removes spaces from the left

删除左边的空格

example: select LTRIM(' test ') as trim = 'test '

示例:选择LTRIM(' test ')为trim = 'test '

RTRIM - Removes spaces from the right

修剪-从右边删除空间

example: select RTRIM(' test ') as trim = ' test'

示例:选择RTRIM(' test')为trim = ' test'

#6

Use the TRIM SQL function.

使用TRIM SQL函数。

If you are using SQL Server try :

如果您正在使用SQL Server，请尝试:

SELECT LTRIM(RTRIM(YourColumn)) FROM YourTable

#7

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

hope it helps ;)

希望它能帮助;)

#8

Well, it depends on which version of SQL Server you are using.

这取决于您使用的SQL服务器的版本。

In SQL Server 2008 r2, 2012 And 2014 you can simply use TRIM(CompanyName)

在SQL Server 2008 r2、2012和2014中，可以简单地使用TRIM(公司名)

SQL Server TRIM Function

SQL Server修剪函数

In other versions you have to use set CompanyName = LTRIM(RTRIM(CompanyName))

在其他版本中，必须使用set CompanyName = LTRIM(RTRIM(CompanyName))

#9

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

#10

-1

Example:

例子:

SELECT TRIM('   Sample   ');

Result: 'Sample'

结果:“样本”

UPDATE TableName SET ColumnName = TRIM(ColumnName)

#11

-1

SELECT TRIM(ColumnName) FROM dual;

#12

-7

The best way to remove all the spaces is SELECT REPLACE("Amit Tech Corp ",' ','')

删除所有空格的最佳方法是选择REPLACE(“Amit技术公司”、“”、“”)

Instead of LTRIM & RTRIM

而不是LTRIM和RTRIM。

#1

216