SQL 修剪字符串中的空格 - LTRIM RTRIM 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21585914/
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
Trim spaces in string - LTRIM RTRIM not working
提问by InTheSkies
I tried this code -
我试过这个代码 -
UPDATE Table
SET Name = RTRIM(LTRIM(Name))
Data type of Name is varchar(25)
Name 的数据类型是 varchar(25)
None of the leading and trailing spaces get removed. When I copy-paste one such Name,
i get this -
前导和尾随空格都不会被删除。当我复制粘贴一个这样的时Name，我得到了这个 -
"big dash" "space symbol" ABC001
Why is this happening and how do trim the spaces ?
为什么会发生这种情况以及如何修剪空格？
EDIT -
编辑 -
The question has already been answered. I found one more table with this problem. I get "- value" when i copy a column of a row. When I press the enter key at end of this copy-pasted value, i see more dashes. See image below -
这个问题已经得到了回答。我又找到了一张有这个问题的表。当我复制一行的列时，我得到“-值”。当我在这个复制粘贴值的末尾按下回车键时，我看到更多的破折号。见下图——
采纳答案by Maheswaran Ravisankar
I suspect, some non readable(Non-ascii characters) inside the name column, that might not get removed as part of TRIMcalls.
我怀疑，名称列中的一些不可读（非 ascii 字符）可能不会作为TRIM调用的一部分被删除。
select convert(varbinary, Name) from table
Reading the HEXoutput from above query should reveal the same.
读取HEX上述查询的输出应该显示相同。
Kindly read thisto find how to write functions to remove such characters.
请阅读本文以了解如何编写函数来删除此类字符。
回答by Shridhar
Kindly use below query it will remove space new line etc..
请使用下面的查询，它将删除空格新行等。
select LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(Name, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))
回答by Gordon Linoff
You could do something brute force, such as removing the first character "manually" if it is not alphanumeric:
您可以执行一些蛮力操作，例如如果第一个字符不是字母数字，则“手动”删除它：
update table
set name = rtrim(ltrim(case when name not like '[a-zA-Z0-9]%'
then stuff(name, 1, 1, '')
else name
end)
);
You could also search and replace that particular character:
您还可以搜索和替换该特定字符：
update table
set name = rtrim(ltrim(replace(name, "big dash", '')));
回答by InTheSkies
If your string has some non-unicode chars, then those need to be removed first. The functions for that are given later, taken from this link - http://iso30-sql.blogspot.com/2010/10/remove-non-printable-unicode-characters.html
如果您的字符串有一些非 unicode 字符，则需要先删除这些字符。稍后给出的功能，取自此链接 - http://iso30-sql.blogspot.com/2010/10/remove-non-printable-unicode-characters.html
First, check if there are any weird hex chars using -
首先，检查是否有任何奇怪的十六进制字符使用 -
select convert(varbinary, Name) from table
Then, use the code given in the link above. Note that in the usage of functions, square brackets are to be removed, otherwise the code won't work. Eg. [@DatabaseName = 'MyDatabaseName',] [@SchemaName = 'MySchemaName',]
然后，使用上面链接中给出的代码。注意在函数的使用中，方括号要去掉，否则代码将无法运行。例如。[@DatabaseName = 'MyDatabaseName',] [@SchemaName = 'MySchemaName',]
After this, your strings might have some spaces which can be removed using -
在此之后，您的字符串可能会有一些空格，可以使用 -
UPDATE Table
SET Name = RTRIM(LTRIM(Name))
Also NOTE that the scripts given in the above link/below will not work on the following table -
另请注意，上面链接/下面给出的脚本不适用于下表 -
CREATE TABLE [dbo].[Junk](
[JunkHex] nvarchar(50) NULL
) ON [PRIMARY]
GO
GO
INSERT [dbo].[Junk] ([JunkHex]) VALUES (N'String? ')
INSERT [dbo].[Junk] ([JunkHex]) VALUES (N'with?')
INSERT [dbo].[Junk] ([JunkHex]) VALUES (N'??valid?')
INSERT [dbo].[Junk] ([JunkHex]) VALUES (N'characters?')
This is the content of the link I have given above -
这是我上面给出的链接的内容-
Remove non-printable / Unicode characters in SQL Server 2005 A few months ago, I was upgrading some report templates from the older version of Excel (.xls) to Excel 2007 (.xlsx). I ran into numerous problems almost immediately when I attempted to generate the upgraded reports because the incoming data was riddled with charaters that don't play nicely with XML. The data is used for a variety of reporting purposes, so I decided to tackle the problem on the back-end by removing all but the printable ascii characters.
删除 SQL Server 2005 中的不可打印/Unicode 字符 几个月前，我将一些报表模板从旧版本的 Excel ( .xls) 升级到 Excel 2007 (.xlsx)。当我尝试生成升级报告时，我几乎立即遇到了许多问题，因为传入的数据充斥着与 XML 不兼容的字符。数据用于各种报告目的，因此我决定通过删除除可打印的 ascii 字符之外的所有字符来解决后端的问题。
I started by writing a simple user function for individual strings, but I got to thinking that I may want to automate some of these cleanup tasks and ended up putting something together that allows for a bit more the flexibility. The following creates the basic string user function, along with two procedures to perform the cleanup at the column and table level:
我开始为单个字符串编写一个简单的用户函数，但我开始考虑我可能想要自动化其中的一些清理任务，并最终将一些东西放在一起，以提供更多的灵活性。下面创建了基本的字符串用户函数，以及在列和表级别执行清理的两个过程：
Note- Each of the scripts below uses all the ones above it. So, execute all scripts in order to get all functionality.
注意- 下面的每个脚本都使用它上面的所有脚本。因此，执行所有脚本以获得所有功能。
Function: fn_npclean_string
函数：fn_npclean_string
use [master]
go
set ansi_nulls on
go
set quoted_identifier on
go
CREATE function [dbo].[fn_npclean_string] (
@strIn as varchar(1000)
)
returns varchar(1000)
as
begin
declare @iPtr as int
set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
while @iPtr > 0 begin
set @strIn = replace(@strIn COLLATE LATIN1_GENERAL_BIN, substring(@strIn, @iPtr, 1), '')
set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
end
return @strIn
end
Procedure: sp_npclean_col
过程：sp_npclean_col
use [master]
go
set ansi_nulls on
go
set quoted_identifier on
go
CREATE procedure [dbo].[sp_npclean_col]
@DatabaseName varchar(75) = null,
@SchemaName varchar(75) = null,
@TableName varchar(75),
@ColumnName varchar(75)
as
begin
Declare @FullTableName varchar(100)
declare @UpdateSQL nvarchar(1000)
if @DatabaseName is null begin
set @DatabaseName = db_name()
end
if @SchemaName is null begin
set @SchemaName = schema_name()
end
set @FullTableName = '[' + @DatabaseName + '].[' + @SchemaName + '].[' + @TableName + ']'
set @UpdateSQL = 'update ' + @FullTableName + ' set [' + @ColumnName + '] = dbo.fn_npclean_string([' + @ColumnName + ']) where [' + @ColumnName + '] like ''%[^ -~0-9A-Z]%'''
exec sp_ExecuteSQL @UpdateSQL
end
Procedure: sp_npclean_table
过程：sp_npclean_table
use [master]
go
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[sp_npclean_table]
@TargetDatabase varchar(75) = null,
@TargetSchema varchar(75) = null,
@TargetTable varchar(75)
as
begin
declare @getColSQL nvarchar(750)
declare @textCol CURSOR
declare @curCol varchar(75)
if @TargetDatabase is null begin
set @TargetDatabase = db_name()
end
if @TargetSchema is null begin
set @TargetSchema = schema_name()
end
set @getColSQL =
'select sc.name
from ' + @TargetDatabase + '.sys.columns sc
join ' + @TargetDatabase + '.sys.types st
on sc.system_type_id = st.system_type_id
join ' + @TargetDatabase + '.sys.objects so
on sc.object_id = so.object_id
join ' + @TargetDatabase + '.sys.schemas ss
on so.schema_id = ss.schema_id
where
so.type = ''U''
and st.name in (''text'',''ntext'',''varchar'',''char'',''nvarchar'',''nchar'')
and sc.is_rowguidcol = 0
and sc.is_identity = 0
and sc.is_computed = 0
and so.name = ''' + @TargetTable + '''
and ss.name = ''' + @TargetSchema + ''''
set @getColSQL = 'set @inCursor = cursor for ' + @getColSQL + ' open @incursor'
execute sp_executesql @getColSQL,N'@inCursor cursor out',@inCursor=@textCol OUT
fetch next from @textCol into @curCol
while @@fetch_status = 0
begin
exec sp_npclean_col @DatabaseName = @TargetDatabase, @SchemaName = @TargetSchema, @TableName = @TargetTable, @ColumnName = @curCol
fetch next from @textCol into @curCol
end
Close @textCol
DeAllocate @textCol
end
Using these, invalid characters can be removed in the following ways:
使用这些，可以通过以下方式删除无效字符：
By String:
按字符串：
select master.dbo.fn_npclean_string('String? with? ??valid? characters?')
By table column:
按表列：
exec master.dbo.sp_npclean_col [@DatabaseName = 'MyDatabaseName',] [@SchemaName = 'MySchemaName',] @TableName = 'MyTableName', @ColumnName = 'MyColumnName'
By table:
按表：
exec master.dbo.sp_npclean_table [@TargetDatabase = 'MyDatabaseName',] [@TargetSchema = 'MySchemaName',] @TargetTable = 'MyTableName'
回答by Deepan
It is a frequent occurrence that we must remove leading and trailing whitespaces from a string before additional processing or sending it to another layer in an application. We can't always control how the data is entered. The data might come from another system, a data conversion, an old application, EDI, Excel, or from an application which had poor quality control. In some of those cases, a whitespace might not be entered or saved in the system as character 32 which is a whitespace entered in a keyboard. If that happens, SQL built in functions for trimming whitespaces do not work so it becomes necessary to replace the “other” whitespace characters with character 32. Then LTRIM and RTRIM will work as expected.
在额外处理或将其发送到应用程序中的另一层之前，我们必须从字符串中删除前导和尾随空格，这是一种常见的情况。我们不能总是控制数据的输入方式。数据可能来自另一个系统、数据转换、旧应用程序、EDI、Excel，或来自质量控制不佳的应用程序。在其中一些情况下，可能不会在系统中输入或保存空格作为字符 32，这是在键盘中输入的空格。如果发生这种情况，用于修剪空格的 SQL 内置函数将不起作用，因此必须用字符 32 替换“其他”空格字符。然后 LTRIM 和 RTRIM 将按预期工作。
**Select [udfTrim](ColumnName) from Table**
**CREATE FUNCTION [dbo].[udfTrim]
(
@StringToClean as varchar(8000)
)**
RETURNS varchar(8000)
AS
BEGIN
--Replace all non printing whitespace characers with Characer 32 whitespace
--NULL
Set @StringToClean = Replace(@StringToClean,CHAR(0),CHAR(32));
--Horizontal Tab
Set @StringToClean = Replace(@StringToClean,CHAR(9),CHAR(32));
--Line Feed
Set @StringToClean = Replace(@StringToClean,CHAR(10),CHAR(32));
--Vertical Tab
Set @StringToClean = Replace(@StringToClean,CHAR(11),CHAR(32));
--Form Feed
Set @StringToClean = Replace(@StringToClean,CHAR(12),CHAR(32));
--Carriage Return
Set @StringToClean = Replace(@StringToClean,CHAR(13),CHAR(32));
--Column Break
Set @StringToClean = Replace(@StringToClean,CHAR(14),CHAR(32));
--Non-breaking space
Set @StringToClean = Replace(@StringToClean,CHAR(160),CHAR(32));
Set @StringToClean = LTRIM(RTRIM(@StringToClean));
Return @StringToClean
END
回答by EricI
You can use the HEX method above, or you can also use the ASCII() function to determine the ASCII code of the character in question...
您可以使用上面的HEX方法，也可以使用ASCII()函数来确定有问题的字符的ASCII码...
SELECT ASCII(SUBSTRING(' character string', 1, 1))
SELECT ASCII(SUBSTRING(' character string', 2, 1))
The select only returns 1 value for the character you specify. But it's helpful for determining which ASCII CHAR() value(s) you need to replace.
选择只为您指定的字符返回 1 个值。但它有助于确定您需要替换哪些 ASCII CHAR() 值。
-Eric Isaacs
-埃里克·艾萨克
回答by Benzi
There are cases that the LTRIM RTRIM not doing what you want, to me, it happened because of the tab key when tab key inserted to a database we cant see it in our eyes in this cases trim function doesn't work.
在某些情况下，LTRIM RTRIM 没有做你想做的事，对我来说，这是因为当 tab 键插入到数据库时的 tab 键我们在我们的眼睛里看不到它，在这种情况下修剪功能不起作用。
Try this code
试试这个代码
UPDATE <TablaName>
SET NAME = CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(value, CHAR(9), ''), CHAR(13), ''), CHAR(10), ''))) AS VARCHAR(50))
UPDATE <TablaName>
SET NAME = CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(value, CHAR(9), ''), CHAR(13), ''), CHAR(10), ''))) AS VARCHAR(50))
