Функция LEN не включает конечные пробелы в SQL Server

У меня есть следующая тестовая таблица в SQL Server 2005:

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

Когда я пытаюсь найти длину TestField с помощью функции LEN() SQL Server, он не учитывает конечные пробелы - например:

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

Как включить конечные пробелы в результат длины?

Ответ 1

Это четко документировано Microsoft в 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

Ответ 2

Вы можете использовать этот трюк:

LEN (Str + 'x') - 1

Ответ 3

"Как включить конечные пробелы в результат длины?"

Вы заставляете кого-то записывать отчет о расширении запроса/ошибки SQL Server, потому что почти все перечисленные обходные пути к этой удивительно простой проблеме здесь имеют некоторый недостаток или неэффективны. Это все еще похоже на SQL Server 2012. Функция автоматической обрезки может быть связана с ANSI/ISO SQL-92, но, похоже, есть некоторые дыры (или их отсутствие).

Пожалуйста, проголосуйте "Добавить настройку, чтобы LEN подсчитывал пробелы":

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

Ссылка на Retired Connect: https://connect.microsoft.com/SQLServer/feedback/details/801381

Ответ 4

Я использую этот метод:

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

Я предпочитаю это по сравнению с DATALENGTH, потому что это работает с разными типами данных, и я предпочитаю его добавление символа в конец, потому что вам не нужно беспокоиться о граничном случае, когда ваша строка уже находится на максимальной длине.

Примечание. Я должен проверить производительность перед тем, как использовать ее для очень большого набора данных; хотя я только что протестировал его против 2М строк, и он был не медленнее, чем LEN без REPLACE...

Ответ 5

Есть проблемы с двумя голосовыми ответами. Ответ, рекомендующий DATALENGTH, подвержен ошибкам программиста. Результат DATALENGTH должен быть разделен типами 2 для NVARCHAR, но не для типов VARCHAR. Для этого требуется знание того типа, в котором вы получаете длину, и если этот тип изменяется, вы должны тщательно изменить места, которые вы использовали DATALENGTH.

Существует также проблема с наиболее распространенным ответом (который, как я признаю, был моим предпочтительным способом сделать это, пока эта проблема не убьет меня). Если вещь, которую вы получаете длиной, имеет тип NVARCHAR(4000), и на самом деле содержит строку из 4000 символов, SQL игнорирует добавленный символ, а не неявно передает результат в NVARCHAR(MAX). Конечный результат - неправильная длина. То же самое произойдет с VARCHAR (8000).

То, что я нашел, работает почти так же быстро, как обычный старый LEN, быстрее, чем LEN(@s + 'x') - 1 для больших строк, и не предполагает, что ширина базового символа такова:

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

Это получает длину datalength и затем делит на datalength одного символа из строки. Добавление "х" охватывает случай, когда строка пуста (что в этом случае даст деление на ноль). Это работает, если @s VARCHAR или NVARCHAR. Выполнение LEFT из 1 символа перед добавлением бреет некоторое время, когда строка большая. Проблема с этим заключается в том, что он не работает корректно с строками, содержащими суррогатные пары.

В комментарии к принятому ответу указан еще один способ, используя REPLACE(@s,' ','x'). Этот метод дает правильный ответ, но на пару порядков медленнее, чем другие методы, когда строка велика.

Учитывая проблемы, возникающие суррогатными парами в любой технике, использующей DATALENGTH, я думаю, что самый безопасный метод, который дает правильные ответы, которые я знаю, следующий:

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

Это быстрее, чем метод REPLACE и намного быстрее с более длинными строками. В основном этот метод - это метод LEN(@s + 'x') - 1, но с защитой для граничного случая, когда длина строки имеет длину 4000 (для nvarchar) или 8000 (для varchar), так что для этого дается правильный ответ. Он также должен правильно обрабатывать строки с суррогатными парами.

Ответ 6

Вам также необходимо убедиться, что ваши данные фактически сохранены с завершающими пробелами. Когда ANSI PADDING выключен (не по умолчанию):

Заканчивающиеся пробелы в символьных значениях вставляемые в колонку varchar, являются подрезаны.

Ответ 7

LEN по умолчанию сокращает пробелы, поэтому я нашел, что это сработало, когда вы перемещаете их на передний план

(LEN (ОБРАТНЫЙ (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)

Не используйте это для ведущих пробелов, конечно.

Ответ 8

Вы должны определить функцию CLR, которая возвращает поле String Length, если вам не нравится конкатенация строк. Я использую LEN('x' + @string + 'x') - 2 в своих производственных случаях.

Ответ 9

Если вам не нравится DATALENGTH из-за проблем с n/varchar, как насчет:

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

который просто

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

завернутый с защитой от деления на нуль.

Разделив DATALENGTH одного char, мы получим нормированную длину.

(Конечно, все еще проблемы с суррогатными парами, если это вызывает беспокойство.)

Ответ 10

использование SELECT DATALENGTH ('string')