如何从 SQL Server 中的字符串中删除空格字符

How to remove white space characters from a string in SQL Server

提问by Justin

I'm trying to remove white spaces from a string in SQL but LTRIMand RTRIMfunctions don't seem to work?

我想从一个字符串中的SQL删除空格，但LTRIM和RTRIM功能不似乎工作？

Column:

柱子：

[ProductAlternateKey] [nvarchar](25) COLLATE Latin1_General_CS_AS NULL

Query:

询问：

select REPLACE(ProductAlternateKey, ' ', '@'),
       LEN(ProductAlternateKey),
       LTRIM(RTRIM(ProductAlternateKey))      AS LRTrim,
       LEN(LTRIM(RTRIM(ProductAlternateKey))) AS LRLen,
       ASCII(RIGHT(ProductAlternateKey, 1))   AS ASCIIR,
       ASCII(LEFT(ProductAlternateKey, 1))    AS ASCIIL,
       ProductAlternateKey
from DimProducts
where ProductAlternateKey  like '46783815%'

Result:

结果：

|  COLUMN_0 | COLUMN_1 | LRTrim | LRLen | ASCIIR | ASCIIL | PRODUCTALTERNATEKEY |
---------------------------------------------------------------------------------
|  46783815 |        8 | 46783815|     8|   53   |   52   |            46783815 |
| 46783815  |        10|46783815  |   10|   10   |   52   |           46783815  |

Can it be other symbols if LTRIMand RTRIMare not working, like "Enter"?

如果LTRIM并且RTRIM不起作用，它可以是其他符号吗，例如“Enter”？

回答by DMK

Using ASCII(RIGHT(ProductAlternateKey, 1))you can see that the right most character in row 2 is a Line Feed or Ascii Character 10.

使用ASCII(RIGHT(ProductAlternateKey, 1))您可以看到第 2 行中最右边的字符是换行符或 Ascii 字符 10。

This can notbe removed using the standard LTrimRTrimfunctions.

这不能使用标准LTrimRTrim功能删除。

You could however use (REPLACE(ProductAlternateKey, CHAR(10), '')

但是，您可以使用 (REPLACE(ProductAlternateKey, CHAR(10), '')

You may also want to account for carriage returns and tabs. These three (Line feeds, carriage returns and tabs) are the usual culprits and can be removed with the following :

您可能还需要考虑回车和制表符。这三个（换行符、回车符和制表符）是常见的罪魁祸首，可以通过以下方式删除：

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ProductAlternateKey, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))

If you encounter any more "white space" characters that can't be removed with the above then try one or all of the below:

如果您遇到更多无法用上述方法删除的“空白”字符，请尝试以下一种或全部方法：

--NULL
Replace([YourString],CHAR(0),'');
--Horizontal Tab
Replace([YourString],CHAR(9),'');
--Line Feed
Replace([YourString],CHAR(10),'');
--Vertical Tab
Replace([YourString],CHAR(11),'');
--Form Feed
Replace([YourString],CHAR(12),'');
--Carriage Return
Replace([YourString],CHAR(13),'');
--Column Break
Replace([YourString],CHAR(14),'');
--Non-breaking space
Replace([YourString],CHAR(160),'');

This list of potential white space characters could be used to create a function such as :

此潜在空白字符列表可用于创建函数，例如：

Create Function [dbo].[CleanAndTrimString] 
(@MyString as varchar(Max))
Returns varchar(Max)
As
Begin
    --NULL
    Set @MyString = Replace(@MyString,CHAR(0),'');
    --Horizontal Tab
    Set @MyString = Replace(@MyString,CHAR(9),'');
    --Line Feed
    Set @MyString = Replace(@MyString,CHAR(10),'');
    --Vertical Tab
    Set @MyString = Replace(@MyString,CHAR(11),'');
    --Form Feed
    Set @MyString = Replace(@MyString,CHAR(12),'');
    --Carriage Return
    Set @MyString = Replace(@MyString,CHAR(13),'');
    --Column Break
    Set @MyString = Replace(@MyString,CHAR(14),'');
    --Non-breaking space
    Set @MyString = Replace(@MyString,CHAR(160),'');

    Set @MyString = LTRIM(RTRIM(@MyString));
    Return @MyString
End
Go

Which you could then use as follows:

然后您可以按如下方式使用：

Select 
    dbo.CleanAndTrimString(ProductAlternateKey) As ProductAlternateKey
from DimProducts

回答by shahkalpesh

In that case, it isn't space that is in prefix/suffix.
The 1st row looks OK. Do the following for the contents of 2nd row.

在这种情况下，前缀/后缀中不是空格。
第一行看起来不错。对第 2 行的内容执行以下操作。

ASCII(RIGHT(ProductAlternateKey, 1))

and

和

ASCII(LEFT(ProductAlternateKey, 1))

回答by TechDo

There may be 2 spaces after the text, please confirm. You can use LTRIMand RTRIMfunctions also right?

正文后可能有2个空格，请确认。你可以使用LTRIM和RTRIM功能也对吗？

LTRIM(RTRIM(ProductAlternateKey))

Maybe the extra space isn't ordinary spaces (ASCII 32, soft space)? Maybe they are "hard space", ASCII 160?

也许额外的空间不是普通空间（ASCII 32，软空间）？也许它们是“硬空间”，ASCII 160？

ltrim(rtrim(replace(ProductAlternateKey, char(160), char(32))))

回答by mr_eclair

How about this?

这个怎么样？

CASE WHEN ProductAlternateKey is NOT NULL THEN
CONVERT(NVARCHAR(25), LTRIM(RTRIM(ProductAlternateKey))) 
FROM DimProducts
where ProductAlternateKey  like '46783815%'

回答by Raj

Looks like the invisible character -

貌似隐形的角色——

ALT+255

Try this

尝试这个

select REPLACE(ProductAlternateKey, ' ', '@')
--type ALT+255 instead of space for the second expression in REPLACE 
from DimProducts
where ProductAlternateKey  like '46783815%'

Raj

拉吉

Edit: Based on ASCII() results, try ALT+10- use numeric keypad

编辑：基于 ASCII() 结果，尝试ALT+10- 使用数字键盘

回答by malinda sanjaka

Remove new line characters with SQL column data

使用 SQL 列数据删除换行符

Update a set  a.CityName=Rtrim(Ltrim(REPLACE(REPLACE(a.CityName,CHAR(10),' '),CHAR(13),' ')))
,a.postalZone=Rtrim(Ltrim(REPLACE(REPLACE(a.postalZone,CHAR(10),' '),CHAR(13),' ')))  
From tAddress a 
inner Join  tEmployees p  on a.AddressId =p.addressId 
Where p.MigratedID is not null and p.AddressId is not null AND
(REPLACE(REPLACE(a.postalZone,CHAR(10),'Y'),CHAR(13),'X') Like 'Y%' OR REPLACE(REPLACE(a.CityName,CHAR(10),'Y'),CHAR(13),'X') Like 'Y%')