Czy operator LIKE rozróżnia wielkość liter w przypadku serwera MSSQL?


101

W dokumentacji dotyczącej operatora LIKE nic nie powiedziano o rozróżnianiu wielkości liter. Czy to jest? Jak to włączyć / wyłączyć?

Sprawdzam varchar(n)kolumny w instalacji Microsoft SQL Server 2005, jeśli to ma znaczenie.


14
To zależy od zestawienia Twojej kolumny (lub bazy danych). Jeśli rozróżniana jest LIKELIKE
wielkość liter

Zapoznaj się z dokumentacją dotyczącą zestawień SQL-Server msdn.microsoft.com/en-us/library/ms144250%28v=sql.105%29.aspx
GarethD,

Jaki jest Twój cel? Czy ma być rozróżniana wielkość liter, czy nie?
Aaron Bertrand

1
Rozróżnianie wielkości liter jest domyślnie ustawione na sortowanie w kolumnie, które domyślnie jest zgodne z sortowaniem w bazie danych. Zwykle jest zaokrąglalny, w którą stronę chcesz iść?
Tony Hopkinson,

Odpowiedzi:


104

To nie operator rozróżnia wielkość liter, to sama kolumna.

Gdy wykonywana jest instalacja programu SQL Server, do instancji jest wybierane domyślne sortowanie. O ile wyraźnie nie określono inaczej (sprawdź poniższą klauzulę sortowania), gdy tworzona jest nowa baza danych, dziedziczy ona sortowanie z instancji, a po utworzeniu nowej kolumny dziedziczy sortowanie z bazy danych, do której należy.

Kolacja taka jak sql_latin1_general_cp1_ci_asdyktuje, jak należy traktować zawartość kolumny. CI oznacza niewrażliwość na wielkość liter, a AS oznacza wrażliwość na akcent.

Pełna lista zestawień jest dostępna pod adresem https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx

(a) Aby sprawdzić sortowanie instancji

select serverproperty('collation')

(b) Aby sprawdzić zestawienie w bazie danych

select databasepropertyex('databasename', 'collation') sqlcollation

(c) Tworzenie bazy danych przy użyciu innego sortowania

create database exampledatabase
collate sql_latin1_general_cp1_cs_as 

(d) Aby utworzyć kolumnę przy użyciu innego sortowania

create table exampletable (
    examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
)

(e) Aby zmodyfikować sortowanie kolumn

alter table exampletable
alter column examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null

Istnieje możliwość zmiany instancji i sortowania bazy danych, ale nie ma to wpływu na wcześniej utworzone obiekty.

Możliwa jest również zmiana sortowania kolumn w locie w celu porównania ciągów, ale jest to wysoce niezalecane w środowisku produkcyjnym, ponieważ jest niezwykle kosztowne.

select
  column1 collate sql_latin1_general_cp1_ci_as as column1
from table1

6
Wygląda na to, że zakres znaków [A-Z]jest zawsze niewrażliwy na wielkość liter. [ABCDEFGHIJKLMNOPQRSŠTUVWXYZŽÅÄÖ]jednak wydaje się przestrzegać sortowania.
jumxozizi

1
Możesz również zapytać o rozróżnianie wielkości liter w określonej kolumnie, używając czegoś takiego:select COLLATION_NAME, iif(cast(COLLATIONPROPERTY(COLLATION_NAME, 'ComparisonStyle') as int) & 1 = 0, 'case sensitive', 'case insensitive') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'exampletable' and COLUMN_NAME = 'examplecolumn'
Jeppe Stig Nielsen,

@jumxozizi Dodałem twoją sugestię do odpowiedzi.
John Zabroski

@JeppeStigNielsen Dodałem twoją sugestię do odpowiedzi.
John Zabroski

20

Cała ta rozmowa o zestawianiu wydaje się nieco zbyt skomplikowana. Dlaczego nie użyć czegoś takiego jak:

IF UPPER(@@VERSION) NOT LIKE '%AZURE%'

Wtedy Twój czek nie rozróżnia wielkości liter, niezależnie od zestawienia


10
Ponieważ to nie podlega oskarżeniu. Twój przykład używa zmiennej i wiodącego symbolu wieloznacznego. Ale w przypadku kolumny indeksowanej przy użyciu sortowania bez uwzględniania wielkości liter like 'a%'można użyć indeksu, a upperwersja nie może.
Martin Smith,

3
Pytanie brzmiało, czy likeoperator rozróżnia wielkość liter.
jumxozizi

Musisz znać porównanie, w przeciwnym razie może to być bezcelowe. Na przykład, jeśli kolumna jest odpytywana pod kątem zastosowań Latin1_General_CI_AS, wtedy robienie UPPER(@@VALUE) NOT LIKE '%SOMETHING%'lub @@COLUMN NOT LIKE '%SOMETHING%'jest nieistotne: wynik byłby taki sam.
rsenna

13

Masz możliwość zdefiniowania kolejności zestawiania w momencie definiowania swojej tabeli. Jeśli zdefiniujesz kolejność uwzględniającą wielkość liter, Twój LIKEoperator będzie się zachowywał z uwzględnieniem wielkości liter; Jeśli zdefiniujesz kolejność sortowania bez uwzględniania wielkości liter, LIKEoperator zignoruje również wielkość liter:

CREATE TABLE Test (
    CI_Str VARCHAR(15) COLLATE Latin1_General_CI_AS -- Case-insensitive
,   CS_Str VARCHAR(15) COLLATE Latin1_General_CS_AS -- Case-sensitive
);

Tutaj jest szybkie demo na sqlfiddle pokazujące wyniki sortowania dla wyszukiwań z LIKE.


12

Jeśli chcesz uzyskać wyszukiwanie uwzględniające wielkość liter bez zmiany sortowania kolumny / bazy danych / serwera, zawsze możesz użyć COLLATEklauzuli, np.

USE tempdb;
GO
CREATE TABLE dbo.foo(bar VARCHAR(32) COLLATE Latin1_General_CS_AS);
GO
INSERT dbo.foo VALUES('John'),('john');
GO
SELECT bar FROM dbo.foo 
  WHERE bar LIKE 'j%';
-- 1 row

SELECT bar FROM dbo.foo 
  WHERE bar COLLATE Latin1_General_CI_AS LIKE 'j%';
-- 2 rows

GO    
DROP TABLE dbo.foo;

Działa też w drugą stronę, jeśli w Twojej kolumnie / bazie danych / serwerze jest rozróżniana wielkość liter i nie chcesz wyszukiwania z uwzględnieniem wielkości liter, np

USE tempdb;
GO
CREATE TABLE dbo.foo(bar VARCHAR(32) COLLATE Latin1_General_CI_AS);
GO
INSERT dbo.foo VALUES('John'),('john');
GO
SELECT bar FROM dbo.foo 
  WHERE bar LIKE 'j%';
-- 2 rows

SELECT bar FROM dbo.foo 
  WHERE bar COLLATE Latin1_General_CS_AS LIKE 'j%';
-- 1 row

GO
DROP TABLE dbo.foo;

Uważaj w ostatnim zapytaniu, jeśli WHERE bar COLLATE Latin1_General_CS_AS LIKE '[j-k]%'go użyjesz , zwróci, Johnponieważ w tym zestawieniu kapitał Jjest między małymi ji małymi literami k. To aAbBcC...jJkKlLmM...nie jest oczywiste. Wydaje się, że Latin1_General_BINjest to bardziej przewidywalne w przypadku wyszukiwania zakresów z operatorem LIKE.
wqw

7

likeOperator pobiera dwa sznurki. Te ciągi muszą mieć zgodne zestawienia, co zostało wyjaśnione tutaj .

Moim zdaniem sprawy się wtedy komplikują. Następujące zapytanie zwraca błąd informujący, że sortowania są niezgodne:

select *
from INFORMATION_SCHEMA.TABLES
where 'abc' COLLATE SQL_Latin1_General_CP1_CI_AS like 'ABC' COLLATE SQL_Latin1_General_CP1_CS_AS

Na losowej maszynie domyślnym sortowaniem jest SQL_Latin1_General_CP1_CI_AS. Następujące zapytanie zakończyło się pomyślnie, ale nie zwraca żadnych wierszy:

select *
from INFORMATION_SCHEMA.TABLES
where 'abc' like 'ABC' COLLATE SQL_Latin1_General_CP1_CS_AS

Wartości „abc” i „ABC” nie pasują do siebie w świecie z rozróżnianiem wielkości liter.

Innymi słowy, istnieje różnica między brakiem sortowania a użyciem domyślnego sortowania. Gdy jedna strona nie ma sortowania, wówczas jest „przypisywana” jawna kolacja z drugiej strony.

(Wyniki są takie same, gdy jawne sortowanie znajduje się po lewej stronie).


Czy możesz odtworzyć błąd w tabeli, która NIE jest obiektem systemowym, takim jak INFORMATION_SCHEMA.TABLES?
Aaron Bertrand

@AaronBertrand. . . Tak, mogę. Czy baza danych jest zepsuta;)?
Gordon Linoff,

Nie wiem, jestem teraz na urządzeniu mobilnym i nie mogę uruchomić maszyny wirtualnej z systemem Windows. Po prostu nie wiem, czy cały twój opis jest poprawny technicznie.
Aaron Bertrand

4

Spróbuj biegać,

SELECT SERVERPROPERTY('COLLATION')

Następnie sprawdź, czy w sortowaniu rozróżniana jest wielkość liter, czy nie.



0

Możesz łatwo zmienić sortowanie w Microsoft SQL Server Management Studio.

  • kliknij prawym przyciskiem tabelę -> projekt.
  • wybierz swoją kolumnę, przewiń w dół i właściwości kolumny do Collation.
  • Ustaw preferencje sortowania, zaznaczając opcję „Uwzględnianie wielkości liter”
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.