如何从 SQL Server 中的字符串中删除空格字符 [英] How to remove white space characters from a string in SQL Server

问题描述

我正在尝试从 SQL 中的字符串中删除空格，但 LTRIM 和 RTRIM 函数似乎不起作用?

I'm trying to remove white spaces from a string in SQL but LTRIM and RTRIM functions don't seem to work?

列:

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

查询:

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

结果:

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

如果LTRIM 和RTRIM 不起作用，是否可以是其他符号，例如Enter"?

Can it be other symbols if LTRIM and RTRIM are not working, like "Enter"?

推荐答案

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

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

这个不能使用标准的LTrim RTrim函数删除.

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

然而你可以使用 (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

这篇关于如何从 SQL Server 中的字符串中删除空格字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！