自动修剪空格 [2spaces = 5spaces] [英] Spaces trimmed automatically [2spaces = 5spaces]

问题描述

我尝试了以下查询(测试数据)，发现空格自动修剪.

I tried the following query (test data) and found SPACES trimmed automatically.

DECLARE @Employees TABLE(EmpID INT IDENTITY(1,1), FirstName VARCHAR(10), LastName VARCHAR(10))

INSERT INTO @Employees VALUES
('Mani','  '),
('Muthu','Kumar'),
('Ram','Prasath'),
('Elango',''),
('Prabhu','     ')

DECLARE @Name VARCHAR(10) = '  ' -- 2 Spaces

-- SELECT LEN(@Name)  -- 0

-- Returned rows with empty LastName
SELECT * FROM @Employees WHERE LastName = @Name

-- Update - Multiple spaces
UPDATE @Employees SET LastName = NULLIF(LastName, '  ') -- All empty last name updated

SELECT * FROM @Employees

在上面的例子中，我搜索了行 LastName with 2 个空格.但它返回了所有姓氏为空的行.我检查了给定参数值的长度.它显示 0(实际上是 6 个空格).

In the above example, I searched rows LastName with 2 spaces. But it returned all rows with Empty LastName. I checked the length of the given parameter value. And it shows 0 (actually 6 spaces).

我尝试更新包含多个空格的姓氏(例如，尝试了 2 个空格)，但它用空的姓氏更新了所有记录

I tried to update LastName which contains more than one space (in example, tried 2 spaces), but it updated all the records with empty LastName

自动修剪是如何发生的?

我使用的是 SQL Server 2012.

I am using SQL Server 2012.

推荐答案

1) LEN 记录为:

1) LEN is documented as:

返回指定字符串表达式的字符数，不包括尾随空格.

Returns the number of characters of the specified string expression, excluding trailing blanks.

(我的重点)

和 2) SQL Server 如何将字符串与尾随空格进行比较 已记录:

and 2) How SQL Server compares strings with trailing spaces is documented:

ANSI 标准要求对比较中使用的字符串进行填充，以便在比较之前它们的长度匹配.填充直接影响 WHERE 和 HAVING 子句谓词以及其他 Transact-SQL 字符串比较的语义.例如，Transact-SQL 认为字符串 'abc' 和 'abc ' 对于大多数比较操作是等效的.

The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

...

(因此，实际上，从技术上讲，发生的不是修剪而是填充.如果您能真正找出一种方法来证明这种差异，则可以加分)

(And so, in fact, technically what happens is not trimming but padding. Bonus points if you can actually work out a way to demonstrate this difference)

使用这些功能的两种方法 - 一种，您可以使用 数据长度.另一种是你可以附加一个尾随的非空格字符，用作哨兵.

Two ways to work with these features - one, you can use DATALENGTH. The other is that you can append a trailing non-space character, to use as a sentinel.

这篇关于自动修剪空格 [2spaces = 5spaces]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！