Klauzule BETWEEN oraz LIKE

 

Artykuł ten jest częścią serii Od 0 do TSQL którą znajdziesz tutaj.

W niniejszym poście rozszerzymy tematykę filtracji danych przy pomocy WHERE. Wprowadzimy dwie niezmiernie użyteczne klauzule tj. BETWEEN oraz LIKE.

Pierwsza z nich jest niczym innym jak aliasem operatora AND użytego w określony sposób. Dla przykładu możemy sobie wyobrazić sytuację, w której chcemy pobrać tylko te produkty, których cena nie jest większa niż 20 i nie mniejsza niż 10. Tradycyjnie w tym artykule za przykład posłuży nam baza Northwind  oraz zawarta w niej tabela Products.

Aby osiągnąć zamierzony efekt możemy oczywiście użyć operatora AND tak jak poniżej:

SELECT
	 [ProductName],
	 [UnitPrice]
FROM 
	[Products]
WHERE
	[UnitPrice] >=10 AND
	[UnitPrice]<=20

Otrzymany w ten sposób rezultat jest jak najbardziej wiarygodny i prawidłowy.

Jednak możemy ten sam efekt otrzymać używając wspomnianego wcześniej słowa kluczowego BETWEEN

SELECT
	 [ProductName],
	 [UnitPrice]
FROM 
	[Products]
WHERE
	[UnitPrice] BETWEEN 10 AND 20

Oczywiście otrzymany ten sam rezultat co w poprzednim przypadku. Jaka jest przewaga klauzuli BETWEEN nad zapisem z AND? Kod ten jest bardziej kompaktowy ze względu na fakt, iż nie musimy powtarzać nazwy pola dwukrotnie. Oczywiście pod względem wydajności między oboma sposobami nie ma żadnej różnicy więc tak naprawdę użycie jednego lub drugiego zależy od preferencji piszącego kod.

Tak więc poszerzyliśmy wachlarz możliwości w filtracji – jednak to oczywiście nie wszystko! Przed nami jeszcze bardzo długa droga do poznania tajników języka TSQL, a kolejnym krokiem do osiągnięcia naszego celu jest klauzula  LIKE. Bardzo często wyszukując coś po nazwie jak np. produkt, nazwisko pracownika itp. nie znamy pełnej nazwy, która jest wymagana aby użyć operatora równości – “=”. Dodatkowo niektóre nazwy mogą być zapisane na kilka sposobów, mamy wtedy kilka wariantów rozwiązania tego problemu. Jednym z nich jest metoda prób i błędów lecz bardzo często nie jest to najlepsze wyjście. Do problemu możemy podejść w inny sposób i użyć właśnie klauzuli LIKE. Co dzięki niej możemy zrobić? Najlepiej będzie to zobrazować na konkretnym przykładzie. Podobnie jak w poprzednich artykułach serii użyjemy tabeli pracowników w bazie Northwind.

SELECT
	 [FirstName],
	 [LastName]
FROM 
	[Employees]
WHERE
	[LastName] LIKE 'D%'

Rezultatem tego zapytania są pracownicy, których nazwisko zaczyna się na literę D.

Tak więc klauzula LIKE pozwala przefiltrować tabelę na podstawie tylko części wartości tekstowej. Znak % zastępuje dowolną ilość znaków, możemy również użyć innego znaku kluczowego, a mianowicie  _ , który zastępuje tylko jeden znak w ciągu tekstowym np.

SELECT
	 [FirstName],
	 [LastName]
FROM 
	[Employees]
WHERE
	[LastName] LIKE '_ing'

Zwraca wszystkich pracowników, których ostatnie trzy litery czteroliterowego nazwiska to “ing”. W przypadku naszej tabeli istnieje tylko jedno takie nazwisko:

Oczywiście użycie znaku % i _ to nie wszystko możemy użyć jeszcze kwadratowych nawiasów [] aby np. wyświetlić wszystkich pracowników których nazwisko zaczyna się na A,B,C lub D:

SELECT
	 [FirstName],
	 [LastName]
FROM 
	[Employees]
WHERE
	[LastName] LIKE '[A-D]%'

Oczywiście ten skrócony zapis zastępuje całą serię użyć operatora OR – poniższe zapytanie jest substytutem powyższego, lecz jak można odnotować nie jest tak kompaktowe:

SELECT
	 [FirstName],
	 [LastName]
FROM 
	[Employees]
WHERE
	[LastName] LIKE 'A%' OR
	[LastName] LIKE 'B%' OR
	[LastName] LIKE 'C%' OR
	[LastName] LIKE 'D%'

Przy czterech literach osiągnięcie tego samego efektu co przy użyciu LIKE i znaku [] jest stosunkowo proste ale można sobie wyobrazić ile pracy trzeba by włożyć gdy musielibyśmy wyświetlić nazwiska zaczynające się na dowolną literę od A do np W – w warunkach produkcyjnych wielokrotne użycie OR jest wręcz niewykonalne. Oprócz liter możemy w nawias wpisać również cyfry co w niektórych przypadkach może być użyteczne.

Nawiasy kwadratowe pozwalają podać wartości od do ale nie tylko. Poniżej przedstawiony został przykład gdzie pierwszą literą nazwiska ma być litera K,P lub S.

SELECT
	 [FirstName],
	 [LastName]
FROM 
	[Employees]
WHERE
	[LastName] LIKE '[KPS]%'

Za pomocą przedstawianych konstrukcji możemy nie tylko filtrować początkową część ale dowolną część wartości tekstowej np. poniższe zapytanie zwróci te nazwiska pracowników które kończą się frazą “ama”

SELECT
	 [FirstName],
	 [LastName]
FROM 
	[Employees]
WHERE
	[LastName] LIKE '%ama'

Musimy jednak pamiętać, że tego typu zapytania nie są najwydajniejszą konstrukcją TSQL. Przy takim zapisie SQL Server musi przeczytać wszystkie wiersze i odfiltrować te, które nie pasują do wzorca. W jednym z przyszłych artykułów postaram się szerzej powiedzieć na ten temat znany pod pełną nazwą “SearchARGuments” lub pod akronimem “SARG“.

Wyżej przedstawione możliwości to nie wszystko co potrafi LIKE możliwe jest również napisanie wzorca wykluczającego, możemy to osiągnąć na dwa różne sposoby. Pierwszym z nich jest użycie znaku ^ – tak więc zapytanie:

SELECT
	 [FirstName],
	 [LastName]
FROM 
	[Employees]
WHERE
	[LastName] LIKE '[^A-D]%'

zwróci wszystkich pracowników z tabeli Employees, których nazwiska nie zaczynają się na literę A,B,C i D:

Użycie powyższego zapisu jest proste i intuicyjne, podobnie jest z drugim sposobem wykorzystującym słowo kluczowe NOT. Dzięki temu słowu kluczowemu możemy przefiltrować tabelę w sposób wykluczający nie zmieniając zapisu klauzuli WHERE:

SELECT
	 [FirstName],
	 [LastName]
FROM 
	[Employees]
WHERE
	[LastName] NOT LIKE '[A-D]%'

Wynik tego zapytania będzie dokładnie taki sam jak powyżej.

Ostatnią możliwością o jakiej chciałbym wspomnieć w ramach niniejszego artykułu jest tzw. znak ucieczki (escape string). Jest to technika pozwalająca na szukanie przy pomocy np. klauzuli LIKE wartości tekstowej zawierającej znaki kluczowe języka TSQL. Wyobraźmy sobie sytuację gdzie chcemy znaleźć pole, które zawiera znak procenta % – jak wiemy w ramach LIKE zastępuje ona dowolny ciąg tekstowy dlatego używając wzorca LIKE ‘%’ zwróci wszystkie wartości! Jak osiągnąć zamierzony efekt? Używając właśnie znaku ucieczki. W ramach bazy Northwind nie mamy pól, którymi moglibyśmy się posłużyć dlatego też użyjemy poniżej konstrukcji – jest to tzw. Common Table Expression i będziemy omawiać tą konstrukcję w dalszej części kursu, póki co skupmy się tylko na zapytaniu SELECT znajdującym się na samym końcu, a wszystko co znajduje się powyżej traktujmy jako tabelę.

WITH testTable
AS
(
SELECT
	'ABC' AS TestColumn
UNION
SELECT
	'CDE'
UNION
SELECT
	'^^'
UNION
SELECT
	'%%AA'
UNION
SELECT
	'[]'
)
SELECT
	testColumn
FROM
	testTable

Jak widać nasza testowa kolumna posiada wartości, które są znakami specjalnymi w ramach TSQL. Dlatego też potrzebujemy sposobu, który umożliwi nam pobranie wartości ‘%%AA’ po znaku procenta, na szczęście mamy bardzo prostą metodę aby to zrobić – służy nam do tego słowo kluczowe ESCAPE tworzące dla nas znak ucieczki. Tak więc poniższy kod obrazuje użycie ESCAPE:

WITH testTable
AS
(
SELECT
	'ABC' AS TestColumn
UNION
SELECT
	'CDE'
UNION
SELECT
	'^^'
UNION
SELECT
	'%%AA'
UNION
SELECT
	'[]'
)
SELECT
	*
FROM
	testTable
WHERE
	TestColumn LIKE '%!%%' ESCAPE '!'

Jako znak ucieczki ustawiliśmy ! tak więc wzorzec ‘%!%%’ oznacza co następuje:

• pierwszy znak procenta nie jest poprzedzony znakiem ucieczki tak więc jest traktowany jako dowolny ciąg znaków
• drugi znak procenta jest poprzedzony znakiem procenta więc nie jest tłumaczony na dowolny ciąg znaków, innymi słowy LIKE będzie szukał właśnie tego znaku w wartości pola tekstowego
• trzeci znak procenta nie jest poprzedzony znakiem ucieczki tak więc jest traktowany jako dowolny ciąg znaków

Rezultat powyższego zapytania jest łatwy do przewidzenia:

Tym prostym przykładem zakończę niniejszy artykuł. Mam nadzieję, że przykłady przypadły Wam do gustu i w łatwy sposób rozszerzyliście swoją wiedzę z zakresu TSQL. W następnym epizodzie powiemy sobie parę słów na temat porządkowania rezultatów i słowie kluczowym TOP – ZAPRASZAM!

Spis dostępnych artykułów w ramach sesji znajdziesz tutaj.