Функция 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 с помощью функции SQL Server LEN(), он не учитывает конечные пробелы, например:
-- 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
Как включить конечные пробелы в результат длины?
11 ответов
Это четко задокументировано 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
Я использую этот метод:
LEN(REPLACE(TestField, ' ', '.'))
Я предпочитаю это DATALENGTH, потому что это работает с разными типами данных, и я предпочитаю это добавлять символ в конец, потому что вам не нужно беспокоиться о крайнем случае, когда ваша строка уже имеет максимальную длину.
Примечание: я бы проверил производительность перед ее использованием на очень большом наборе данных; хотя я только что проверил это с 2M строк, и это не было медленнее, чем LEN без ЗАМЕНЫ...
"Как мне включить конечные пробелы в результате длины?"
Вы можете попросить кого-нибудь подать запрос или отчет об ошибках в SQL Server, потому что почти все перечисленные обходные пути к этой удивительно простой проблеме имеют некоторые недостатки или неэффективны. Это все еще кажется верным в SQL Server 2012. Функция автоматической обрезки может происходить из ANSI/ISO SQL-92, но, похоже, есть некоторые дыры (или отсутствие их подсчета).
Пожалуйста, проголосуйте "Добавить настройку, чтобы LEN считал конечные пробелы" здесь:
Ссылка "Удаленный пользователь Connect": https://connect.microsoft.com/SQLServer/feedback/details/801381
Есть проблемы с двумя наиболее популярными ответами. Ответ рекомендующий 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))
Это получает длину данных, а затем делит на длину данных одного символа из строки. Добавление 'x' охватывает случай, когда строка пуста (что в этом случае даст деление на ноль). Это работает ли @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), так что правильный ответ дается даже для этого. Он также должен правильно обрабатывать строки с суррогатными парами.
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)
Не используйте это для ведущих пробелов конечно.
Вы также должны убедиться, что ваши данные действительно сохраняются с конечными пробелами. Когда ANSI PADDING выключен (не по умолчанию):
Конечные пробелы в символьных значениях, вставленных в столбец varchar, обрезаются.
Это лучший алгоритм, который я придумал, который справляется с проблемами максимальной длины и переменного количества байтов на символ:
ISNULL(LEN(STUFF(@Input, 1, 1, '') + '.'), 0)
Это вариант алгоритма, но с помощью удаления первого символа мы гарантируем, что измененная строка не превышает максимальную длину, и избавляемся от необходимости вычитать 1.
ISNULL(..., 0)добавляется для случая, когда @Input = '', что приводит к возврату .
Это имеет побочный эффект: результат также равен 0, когда @Input имеет значение NULL, что несовместимо с
LEN(NULL)который возвращает
NULL, но это может быть обработано логикой вне этой функции, если это необходимо.
Вот результаты использования
LEN(@Input),
LEN(@Input + '.') - 1,
LEN(REPLACE(@Input, ' ', '.'))и приведенный выше вариант, используя образец
@Input = CAST(' S' + SPACE(3998) AS NVARCHAR(4000))более 1000 итераций
В этом случае
STUFFалгоритм на самом деле быстрее , чем
LEN()!
Я могу только предположить, что внутренне SQL смотрит на последний символ и, если это не пробел, оптимизирует вычисления. Но это хороший результат, а?
Не используйте параметр REPLACE, если вы не знаете, что ваши строки маленькие - это крайне неэффективно.
Если вам не нравится DATALENGTH из-за проблем с n/varchar, как насчет:
select DATALENGTH(@var)/isnull(nullif(DATALENGTH(left(@var,1)),0),1)
который просто
select DATALENGTH(@var)/DATALENGTH(left(@var,1))
обернутый защитой от деления на ноль.
Делив на DATALENGTH одного символа, мы получаем нормированную длину.
(Конечно, проблемы с суррогатными парами все еще возникают, если это вызывает озабоченность.)
Вы должны определить функцию CLR, которая возвращает поле длины строки, если вам не нравится конкатенация строк. я использую LEN('x' + @string + 'x') - 2 в моих производственных прецедентах.