LEN 函数不包括 SQLServer 中的尾随空格

在 SQLServer2005中，我有以下测试表:

CREATE TABLE [dbo].[TestTable]
(
[ID] [int] NOT NULL,
[TestField] [varchar](100) NOT NULL
)

人口:

INSERT INTO TestTable (ID, TestField) VALUES (1, 'A value');   -- Len = 7
INSERT INTO TestTable (ID, TestField) VALUES (2, 'Another value      '); -- Len = 13 + 6 spaces

当我尝试用 SQL Server LEN ()函数查找 TestField 的长度时，它不会计算尾随的空格——例如:

-- Note: Also results the grid view of TestField do not show trailing spaces (SQL Server 2005).
SELECT
ID,
TestField,
LEN(TestField) As LenOfTestField, -- Does not include trailing spaces
FROM
TestTable

如何在长度结果中包含尾随空格？

101270 次浏览
小开

微软在 http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx的 MSDN 中清楚地记录了这一点，http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx声明 LEN“返回指定字符串表达式的字符数，不包括尾随空格”。然而，如果你不小心的话，这是一个很容易被忽略的细节。

相反，您需要使用 DATALENGTH 函数-参见 http://msdn.microsoft.com/en-us/library/ms173486(SQL.90).aspx-它“返回用于表示任何表达式的字节数”。

例如:

SELECT
ID,
TestField,
LEN(TestField) As LenOfTestField,           -- Does not include trailing spaces
DATALENGTH(TestField) As DataLengthOfTestField      -- Shows the true length of data, including trailing spaces.
FROM
TestTable
小开

您还需要确保您的数据实际上与后面的空格一起保存。当 ANSI 填充物为 OFF (非默认) :

字符值的尾随空格 插入到 varchar 列中的 修剪过。

小开

你可以使用这个技巧:

LEN (Str + ‘ x’)-1

小开

LEN 在默认情况下削减尾随空格，所以我发现当你将它们移动到前面时，这种方法是有效的

(LEN (REVERSE (TestField)))

所以如果你想，你可以说

SELECT
t.TestField,
LEN(REVERSE(t.TestField)) AS [Reverse],
LEN(t.TestField) AS [Count]
FROM TestTable t
WHERE LEN(REVERSE(t.TestField)) <> LEN(t.TestField)

当然，不要用这个作为前导空格。

小开

“如何在长度结果中包含尾随空格?”

您需要有人提交一个 SQL Server 增强请求/bug 报告，因为几乎所有列出的解决这个令人惊讶的简单问题的方法都有一些缺陷或效率低下。在 SQLServer2012中似乎仍然是这样。自动修剪功能可能源于 ANSI/ISO SQL-92，但似乎有一些漏洞(或缺乏计数)。

请在这里投票“添加设置，以便 LEN 计数尾随空格”:

Https://feedback.azure.com/forums/908035-sql-server/suggestions/34673914-add-setting-so-len-counts-trailing-whitespace

返回文章页面退休连接链接: Https://connect.microsoft.com/sqlserver/feedback/details/801381

小开

如果不喜欢字符串连接，则应定义一个返回 String 的 Llength 字段的 CLR 函数。 我在我的生产用例中使用 LEN('x' + @string + 'x') - 2。

小开

排名前两位的答案存在一些问题。推荐 DATALENGTH的答案很容易出现程序员错误。对于 NVARCHAR类型，DATALENGTH的结果必须除以2，但对于 VARCHAR类型则不能。这需要您知道类型的长度，如果类型发生变化，您必须勤奋地更改使用 DATALENGTH的位置。

这里还有一个问题，那就是最受欢迎的答案(我承认，在这个问题困扰我之前，这是我最喜欢的答案)。如果您得到的长度是 NVARCHAR(4000)类型，并且它实际上包含4000个字符的字符串，SQL 将忽略附加字符，而不是隐式地将结果强制转换为 NVARCHAR(MAX)。最终结果是长度不正确。VARCHAR (8000)也会发生同样的事情。

我所发现的工作，是几乎一样快的普通老 LEN，是快于 LEN(@s + 'x') - 1的大字符串，并没有假定底层字符宽度如下:

DATALENGTH(@s) / DATALENGTH(LEFT(LEFT(@s, 1) + 'x', 1))

它获取数据长度，然后除以字符串中单个字符的数据长度。“ x”的附加部分覆盖了字符串为空的情况(在这种情况下，除数为零)。无论 @s是 VARCHAR还是 NVARCHAR，这都是有效的。当字符串较大时，在追加之前执行1个字符的 LEFT会减少一些时间。但问题是，它不能正确地处理包含代理项对的字符串。

对于已接受的答案，在评论中还提到了另一种方式，即使用 REPLACE(@s,' ','x')。这种方法给出了正确的答案，但是当数量级很大时，这种方法要比其他方法慢一些。

考虑到代理对在使用 DATALENGTH的任何技术中引入的问题，我认为我所知道的能够给出正确答案的最安全的方法是:

LEN(CONVERT(NVARCHAR(MAX), @s) + 'x') - 1

这比 REPLACE技术更快，而且对于更长的字符串更快。基本上，这种技术是 LEN(@s + 'x') - 1技术，但是它保护字符串长度为4000(对于 nvarchar)或8000(对于 varchar)的边缘情况，因此即使对于这种情况也会给出正确的答案。它还应该正确地处理具有代理项对的字符串。

小开

我使用这种方法:

LEN(REPLACE(TestField, ' ', '.'))

我更喜欢这个而不是 DATALENGTH，因为它适用于不同的数据类型，我更喜欢它而不是在结尾添加一个字符，因为您不必担心字符串已经处于最大长度的边缘情况。

注意: 在对一个非常大的数据集使用它之前，我会测试它的性能; 尽管我只是对2M 行进行了测试，而且它并不比没有 REPLACE 的 LEN 慢..。

小开

如果您因为 n/varchar 问题而不喜欢 DATALENGTH，那么:

select DATALENGTH(@var)/isnull(nullif(DATALENGTH(left(@var,1)),0),1)

也就是说

select DATALENGTH(@var)/DATALENGTH(left(@var,1))

用除零保护包裹起来。

通过除以单个字符的 DATALENGTH，我们可以得到规范化的长度。

(当然，如果这是一个问题的话，仍然存在代理对的问题。)

小开

选择 DATALENGTH (‘ string’)

小开

这是我想到的最好的算法，它可以处理每个字符的最大长度和可变字节数问题:

ISNULL(LEN(STUFF(@Input, 1, 1, '') + '.'), 0)

这是 LEN(@Input + '.') - 1算法的一个变体，但是通过使用 STUFF删除第一个字符，我们确保修改后的字符串不会超过最大长度，并且不需要减去1。

添加 ISNULL(..., 0)是为了处理@Input =”导致 STUFF返回 NULL的情况。

这确实有副作用，当@Input 为 NULL 时，结果也是0，这与返回 NULL的 LEN(NULL)不一致，但是如果需要，这可以通过函数外部的逻辑来处理

下面是使用 LEN(@Input)、 LEN(@Input + '.') - 1、 LEN(REPLACE(@Input, ' ', '.'))和上述 STUFF变体的结果，使用了超过1000次迭代的 @Input = CAST(' S' + SPACE(3998) AS NVARCHAR(4000))样本

算法 数据长度 预期结果 结果 女士
LEN 8000 4000 2 14
+ DOT-1 8000 4000 1 13
更换 8000 4000 4000 514
东西 + 点 8000 4000 4000 0

在这种情况下，STUFF算法实际上是 再快点比 LEN()！

我只能假设 SQL 在内部查看最后一个字符，如果它不是空格，则优化计算 但这是个好结果，不是吗？

除非你知道你的字符串很小，否则不要使用 REPLACE 选项-这是非常低效的


提交答案