Funkcja LEN bez spacji końcowych w SQL Server

Mam następującą tabelę testową w programie SQL Server 2005:

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

Wypełniony:

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

Kiedy próbuję znaleźć długość TestField za pomocą funkcji SQL Server LEN (), nie liczy spacji końcowych - np:

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

Jak uwzględnić końcowe spacje w wyniku długości?

Jest to wyraźnie udokumentowane przez firmę Microsoft w witrynie MSDN pod adresem http://msdn.microsoft.com/en-us/library/ms190329(SQL.90).aspx , który stwierdza, że ​​LEN „zwraca liczbę znaków określonego wyrażenia ciągu, z wyłączeniem końcowe spacje ”. Jest to jednak łatwy szczegół do przeoczenia, jeśli nie jesteś ostrożny.

Zamiast tego należy użyć funkcji DATALENGTH - patrz http://msdn.microsoft.com/en-us/library/ms173486(SQL.90).aspx - która „zwraca liczbę bajtów używanych do reprezentowania dowolnego wyrażenia”.

Przykład:

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

Możesz użyć tej sztuczki:

LEN (Str + 'x') - 1

Używam tej metody:

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

Wolę to od DATALENGTH, ponieważ działa to z różnymi typami danych i wolę to niż dodawanie znaku na końcu, ponieważ nie musisz się martwić o przypadek krawędzi, w którym twój ciąg ma już maksymalną długość.

Uwaga: przed użyciem przetestowałbym wydajność w odniesieniu do bardzo dużego zestawu danych; chociaż właśnie przetestowałem go na 2M rzędach i nie był wolniejszy niż LEN bez REPLACE ...

„Jak uwzględnić końcowe spacje w wyniku długości?”

Możesz poprosić kogoś o zgłoszenie prośby o ulepszenie SQL Server / zgłoszenie błędu, ponieważ prawie wszystkie wymienione tutaj obejścia tego niezwykle prostego problemu mają pewne wady lub są nieefektywne. Nadal wydaje się, że tak jest w SQL Server 2012. Funkcja automatycznego przycinania może pochodzić z ANSI / ISO SQL-92, ale wydaje się, że są jakieś dziury (lub brak ich zliczania).

Zagłosuj na „Dodaj ustawienie, aby LEN liczyło końcowe spacje” tutaj:

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

Wycofane łącze Connect: https://connect.microsoft.com/SQLServer/feedback/details/801381

Wystąpiły problemy z dwoma najczęściej głosowanymi odpowiedziami. Polecająca odpowiedź DATALENGTHjest podatna na błędy programisty. Wynik DATALENGTHnależy podzielić przez 2 dla NVARCHARtypów, ale nie dla VARCHARtypów. Wymaga to znajomości typu, którego długość otrzymujesz, a jeśli ten typ się zmieni, musisz pilnie zmieniać miejsca, z których korzystałeś DATALENGTH.

Jest też problem z najbardziej pozytywną odpowiedzią (przyznaję, że był to mój ulubiony sposób, dopóki ten problem mnie nie ugryzł). Jeśli rzecz, której otrzymujesz długość, jest typu NVARCHAR(4000)i faktycznie zawiera ciąg 4000 znaków, SQL zignoruje dołączony znak, zamiast niejawnie rzutować wynik na NVARCHAR(MAX). Wynik końcowy to nieprawidłowa długość. To samo stanie się z VARCHAR (8000).

To, co znalazłem, działa, jest prawie tak szybkie, jak zwykłe stare LEN, jest szybsze niż w LEN(@s + 'x') - 1przypadku dużych ciągów i nie zakłada, że ​​podstawowa szerokość znaku jest następująca:

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

Pobiera długość danych, a następnie dzieli przez długość danych pojedynczego znaku z ciągu. Dodanie „x” obejmuje przypadek, w którym ciąg jest pusty (co w tym przypadku dałoby podzielenie przez zero). Działa @sto niezależnie od tego, czy jest, VARCHARczy NVARCHAR. Wykonanie LEFT1 znaku przed dołączeniem pozwala zaoszczędzić trochę czasu, gdy ciąg jest duży. Problem z tym polega jednak na tym, że nie działa poprawnie z łańcuchami zawierającymi pary zastępcze.

W komentarzu do zaakceptowanej odpowiedzi wspomniano o innym sposobie użycia REPLACE(@s,' ','x'). Ta technika daje poprawną odpowiedź, ale jest o kilka rzędów wielkości wolniejsza niż inne techniki, gdy struna jest duża.

Biorąc pod uwagę problemy wprowadzone przez pary zastępcze w dowolnej używanej technice DATALENGTH, myślę, że najbezpieczniejsza metoda, która daje prawidłowe odpowiedzi, to:

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

Jest to szybsze niż REPLACEtechnika i znacznie szybsze w przypadku dłuższych strun. Zasadniczo ta technika jest LEN(@s + 'x') - 1techniką, ale z ochroną dla przypadku krawędzi, w którym łańcuch ma długość 4000 (dla nvarchar) lub 8000 (dla varchar), więc nawet w tym przypadku podano poprawną odpowiedź. Powinien również poprawnie obsługiwać łańcuchy z parami zastępczymi.

Musisz również upewnić się, że Twoje dane są faktycznie zapisane z końcowymi spacjami. Kiedy ANSI PADDING jest WYŁĄCZONE (inne niż domyślne):

Końcowe spacje w wartościach znakowych wstawionych do kolumny varchar są przycinane.

LEN domyślnie wycina końcowe spacje, więc stwierdziłem, że działa, gdy przesuwasz je do przodu

(LEN (REVERSE (TestField))

Więc jeśli chcesz, możesz powiedzieć

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)

Nie używaj tego oczywiście do wiodących spacji.

Powinieneś zdefiniować funkcję CLR, która zwraca pole Długość ciągu, jeśli nie lubisz łączenia ciągów. Używam LEN('x' + @string + 'x') - 2w moich przypadkach użycia w produkcji.

Jeśli nie podoba ci się z DATALENGTHpowodu obaw n / varchar, co powiesz na:

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

co jest sprawiedliwe

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

owinięte ochroną dzielenia przez zero.

Dzieląc przez DATALENGTH pojedynczego znaku, otrzymujemy znormalizowaną długość.

(Oczywiście nadal występują problemy z parami zastępczymi, jeśli jest to problem).

użyj SELECT DATALENGTH („ciąg”)