SQL Server 2008 pusty ciąg a spacja

Dziś rano natknąłem się na coś dziwnego i pomyślałem, że przedstawię to do komentarza.

Czy ktoś może wyjaśnić, dlaczego następujące zapytanie SQL wyświetla „równe”, gdy jest uruchamiane względem SQL 2008. Poziom zgodności db jest ustawiony na 100.

if '' = ' '
    print 'equal'
else
    print 'not equal'

A to zwraca 0:

select (LEN(' '))

Wygląda na to, że automatycznie przycina przestrzeń. Nie mam pojęcia, czy tak było w poprzednich wersjach programu SQL Server i nie mam już w pobliżu miejsca, aby to przetestować.

Wpadłem na to, ponieważ zapytanie produkcyjne zwracało nieprawidłowe wyniki. Nigdzie nie mogę znaleźć udokumentowanego tego zachowania.

Czy ktoś ma jakieś informacje na ten temat?

varchars i równość są drażliwe w TSQL. LENFunkcja mówi:

Zwraca liczbę znaków, a nie liczbę bajtów, danego wyrażenia łańcuchowego, z wyłączeniem końcowych spacji .

Musisz użyć, DATALENGTHaby uzyskać prawdziwą byteliczbę danych, o których mowa. Jeśli masz dane Unicode, zwróć uwagę, że wartość uzyskana w tej sytuacji nie będzie taka sama jak długość tekstu.

print(DATALENGTH(' ')) --1
print(LEN(' '))        --0

Jeśli chodzi o równość wyrażeń, dwa ciągi są porównywane pod kątem równości w następujący sposób:

• Uzyskaj krótszy ciąg
• Pad ze spacjami aż długość będzie równa długości dłuższego sznurka
• Porównaj oba

To środkowy krok powoduje nieoczekiwane wyniki - po tym kroku skutecznie porównujesz białe spacje z białymi znakami - stąd są one postrzegane jako równe.

LIKEzachowuje się lepiej niż =w sytuacji "spacji", ponieważ nie wykonuje wypełniania pustymi znakami na wzorcu, który próbujesz dopasować:

if '' = ' '
print 'eq'
else
print 'ne'

Daje eqpodczas:

if '' LIKE ' '
print 'eq'
else
print 'ne'

Da ne

Uważaj LIKEjednak: nie jest symetryczny: traktuje końcowe białe znaki jako znaczące we wzorcu (RHS), ale nie w wyrażeniu dopasowującym (LHS). Poniższa pochodzi z tutaj :

declare @Space nvarchar(10)
declare @Space2 nvarchar(10)

set @Space = ''
set @Space2 = ' '

if @Space like @Space2
print '@Space Like @Space2'
else
print '@Space Not Like @Space2'

if @Space2 like @Space
print '@Space2 Like @Space'
else
print '@Space2 Not Like @Space'

@Space Not Like @Space2
@Space2 Like @Space

Operator = to T-SQL to nie tyle „równa się”, ile „to to samo słowo / fraza, zgodnie z zestawieniem kontekstu wyrażenia”, a LEN to „liczba znaków w słowie / frazie”. Żadne sortowania nie traktują końcowych spacji jako części poprzedzającego je słowa / frazy (chociaż traktują początkowe spacje jako część ciągu, który poprzedzają).

Jeśli chcesz odróżnić „to” od „tego”, nie powinieneś używać operatora „to to samo słowo lub fraza”, ponieważ „to” i „to” to to samo słowo.

Przyczyną do tego, jak działa = jest idea, że ​​operator równości ciągów powinien zależeć od zawartości swoich argumentów i kontekstu sortowania wyrażenia, ale nie powinien zależeć od typów argumentów, jeśli oba są typami ciągów .

Pojęcie języka naturalnego „to są to samo słowo” zazwyczaj nie jest wystarczająco precyzyjne, aby można je było przechwycić przez operator matematyczny, taki jak =, aw języku naturalnym nie ma pojęcia typu łańcucha. Kontekst (tj. Zestawienie) ma znaczenie (i istnieje w języku naturalnym) i jest częścią opowieści, a dodatkowe właściwości (niektóre wydają się dziwaczne) są częścią definicji =, aby było dobrze zdefiniowane w nienaturalnym świecie dane.

Jeśli chodzi o typ, nie chciałbyś, aby słowa zmieniały się, gdy są przechowywane w różnych typach ciągów. Na przykład wszystkie typy VARCHAR (10), CHAR (10) i CHAR (3) mogą zawierać reprezentacje słowa „kot” i? = „kot” powinno pozwolić nam zdecydować, czy wartość któregokolwiek z tych typów zawiera słowo „kot” (z uwzględnieniem wielkości liter i akcentu określonych przez porównanie).

Odpowiedź na komentarz JohnFx:

Zobacz Używanie danych char i varchar w Books Online. Cytując z tej strony, podkreśl moje:

Każda wartość danych char i varchar ma sortowanie. Sortowania definiują atrybuty, takie jak wzorce bitowe używane do reprezentowania każdego znaku, reguły porównania i wrażliwość na wielkość liter lub akcentowanie.

Zgadzam się, że mogłoby to być łatwiejsze do znalezienia, ale jest to udokumentowane.

Warto również zauważyć, że semantyka SQL, gdzie = ma związek z rzeczywistymi danymi i kontekstem porównania (w przeciwieństwie do czegoś o bitach przechowywanych na komputerze), była częścią SQL przez długi czas. Założeniem RDBMS i SQL jest wierna reprezentacja rzeczywistych danych, stąd wsparcie dla zestawień na wiele lat przed pojawieniem się podobnych pomysłów (takich jak CultureInfo) do królestwa języków podobnych do Algola. Założeniem tych języków (przynajmniej do niedawna) było rozwiązywanie problemów w inżynierii, a nie zarządzanie danymi biznesowymi. (Ostatnio użycie podobnych języków w aplikacjach niezwiązanych z inżynierią, takich jak wyszukiwanie, ma pewne znaczenie, ale Java, C # itd. Wciąż borykają się z nie-biznesowymi korzeniami).

Moim zdaniem krytykowanie SQL za to, że różni się od „większości języków programowania”, jest niesprawiedliwe. SQL został zaprojektowany do obsługi struktury modelowania danych biznesowych, która jest bardzo różna od inżynierii, więc język jest inny (i lepszy dla jego celu).

Heck, kiedy po raz pierwszy określono SQL, niektóre języki nie miały żadnego wbudowanego typu łańcucha. W niektórych językach operator równości między łańcuchami w ogóle nie porównuje danych znakowych, ale porównuje referencje! Nie zdziwiłbym się, gdyby za następną dekadę lub dwie idea, że ​​== jest zależna od kultury, stanie się normą.

Znalazłem ten artykuł na blogu, który opisuje zachowanie i wyjaśnia dlaczego.

Standard SQL wymaga, aby porównania ciągów skutecznie uzupełniały krótszy ciąg znakami spacji. Prowadzi to do zaskakującego wyniku, że N '' = N '' (pusty ciąg równa się ciągowi jednego lub więcej znaków spacji), a bardziej ogólnie każdy ciąg równa się innemu ciągowi, jeśli różnią się tylko spacjami końcowymi. W niektórych sytuacjach może to stanowić problem.

Więcej informacji można również znaleźć w MSKB316626

Było podobne pytanie jakiś czas temu, gdzie spojrzałem na podobnym problemem tutaj

Zamiast LEN(' ')używać DATALENGTH(' ')- to daje poprawną wartość.

Rozwiązania miały wykorzystywać LIKEklauzulę, jak wyjaśniono w mojej odpowiedzi, i / lub zawierać również drugi warunek w WHEREklauzuli do sprawdzenia DATALENGTH.

Przeczytaj to pytanie i linki tam.

Aby porównać wartość z dosłowną spacją, możesz również użyć tej techniki jako alternatywy dla instrukcji LIKE:

IF ASCII('') = 32 PRINT 'equal' ELSE PRINT 'not equal'

Jak rozróżniać rekordy przy zaznaczaniu za pomocą pól char / varchar na serwerze sql: przykład:

declare @mayvar as varchar(10)

set @mayvar = 'data '

select mykey, myfield from mytable where myfield = @mayvar

spodziewany

mykey (int) | myfield (varchar10)

1 | „dane”

uzyskane

mykey | moja dziedzina

1 | „dane” 2 | „dane”

nawet jeśli napiszę select mykey, myfield from mytable where myfield = 'data' (bez ostatniej spacji), otrzymuję te same wyniki.

jak rozwiązałem? W tym trybie:

select mykey, myfield
from mytable
where myfield = @mayvar 
and DATALENGTH(isnull(myfield,'')) = DATALENGTH(@mayvar)

a jeśli na myfield istnieje indeks, będzie on używany w każdym przypadku.

Mam nadzieję, że będzie to pomocne.

Innym sposobem jest przywrócenie go do stanu, w którym przestrzeń ma wartość. np .: zamień spację na znak znany jak _

if REPLACE('hello',' ','_') = REPLACE('hello ',' ','_')
    print 'equal'
else
    print 'not equal'

zwraca: nie równe

Nie jest idealny i prawdopodobnie powolny, ale jest kolejnym szybkim krokiem naprzód, gdy jest potrzebny szybko.

Czasami mamy do czynienia ze spacjami w danych, z innymi znakami lub bez nich, nawet jeśli pomysł użycia wartości Null jest lepszy - ale nie zawsze nadaje się do użytku. Napotkałem opisaną sytuację i rozwiązałem ją w ten sposób:

... where ('>' + @space + '<') <> ('>' + @space2 + '<')

Oczywiście nie zrobiłbyś tego dla dużej ilości danych, ale działa to szybko i łatwo dla kilkuset wierszy ...