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

I'm trying to remove white spaces from a string in SQL but LTRIM and RTRIM functions 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 LTRIM and RTRIM are not working, like "Enter"?

如果LTRIM和RTRIM不工作，是否还会是其他符号，比如“Enter”?

6 个解决方案

#1

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(右(ProductAlternateKey, 1))您可以看到，第2行中最正确的字符是换行或ASCII字符10。

This can not be removed using the standard LTrim RTrim functions.

不能使用标准的LTrim RTrim函数将其删除。

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

#2

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.

在这种情况下，前缀/后缀不是空格。第一行看起来没问题。对第二行的内容进行以下操作。

ASCII(RIGHT(ProductAlternateKey, 1))

and

和

ASCII(LEFT(ProductAlternateKey, 1))

#3

There may be 2 spaces after the text, please confirm. You can use LTRIM and RTRIM functions 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))))

#4

How about this?

这个怎么样?

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

#5

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 -使用数字键盘

#6

-2

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

#1