Spis treści:

Kategoria:SQL Server


Znaki specjalne SQL w zapytaniach LIKE

Znaki specjalne stosowane w warunku LIKE

Jak zapewne wiemy, LIKE służy do sprawdzenia, czy podany ciąg znaków, lub ciąg znaków z danej kolumny, spełnia pewne zdefiniowane kryteria. Wielkim uproszczeniem byłoby pozostawienie jedynie możliwości porównania dwóch wartości tekstowych. Jak postąpilibyśmy wtedy, gdybyśmy potrzebowali wszystkich wartości z kolumny, które zaczynają się na literę B? W takiej sytuacji stosujemy znaki specjalne. Należą do nich między innymi znaki _ i %.

Znak %

Znak % zastępuje dowolny ciąg znaków, także pusty. Jest to odpowiednik znaku specjalnego * używana w większości popularnych systemów operacyjnych.

Przyjrzyjmy się następującemu przypadkowi.

Zadanie 1. Potrzebujemy wszystkich rekordów z tabeli Adresy, których ulica zaczyna się od litery U.

Poniżej znajduje się przykładowe rozwiązanie:

SELECT * FROM Adresy WHERE Ulica LIKE 'U%'

Procent, jak łatwo zauważyć zastępuje dowolny ciąg dowolnych znaków. Procent może znajdować się na początku, na końcu, w środku, może też być użyty wielokrotnie. Przyjrzyjmy się przykładowi numer 2.

Zadanie 2. Potrzebujemy wszystkich rekordów z tabeli Adresy, w których w nazwie ulicy znajduje się ciąg znaków "kot".

Przykładowe rozwiązanie może wyglądać na przykład tak:

SELECT * FROM Adresy WHERE Ulica LIKE '%kot%'

Wyszukujemy zatem wszystkie nazwy, które mają dowolny ciąg znaków, następnie sekwencję "kot", a na końcu znów dowolny ciąg znaków.

Warto zapamiętać, że procent może zastępować także pusty ciąg znaków. W zaprezentowanym przykładzie zwrócone zostaną zatem także te rekordy, które zaczynają się na "kot", oraz te, które się kończą na "kot".

Znak _, mylony ze znakiem ?

Znak podkreślenia (_) z definicji zastępuje jeden, tylko jeden i dokładnie jeden dowolny znak w tekście. Jak już wspomniałem, znaki specjalne stosowane są w większości systemów operacyjnych. O ile znak % rzadko jest mylony, o tyle w przypadku wyszukiwania pojedynczego wystąpienia dość często mylony jest znak _ ze znakiem ?. Zapamiętajmy: znak _ jest w SQLu, znak ? w systemach operacyjnych.

Przyjrzyjmy się takiemu oto przykładowi

Zadanie 3. Potrzebne są nam wszystkie przednioty z tabeli Przedmioty, których nazwa zaczyna się na "Ko" i ma tylko trzy litery.

Rozwiązanie zaprezentowano poniżej:

SELECT * FROM Przedmioty WHERE Nazwa LIKE 'Ko_'

Wynikiem takiego zapytania mogą być nazwy typu: Koń, Kot, Kos, Kod, Koc ale nie będą już żadne wyrazy dwuliterowe, czteroliterowe i dłuższe.

Przyjrzyjmy się jeszcze jednemu przykładowi:

Zadanie 4. Potrzebne są nam wszystkie przednioty z tabeli Przedmioty, których nazwa zaczyna się na "K" i ma dokładnie cztery litery.

Rozwiązanie ma następującą postać:

SELECT * FROM Przedmioty WHERE Nazwa LIKE 'K___'

Przykład nie wymaga chyba szczegółowych wyjaśnień. W warunku LIKE umieszczamy pierwszą literę, a następnie trzy znaki podkreślenia.

Znaki [ i ]

Znaki [ i ] nie są tak powszechnie używane jak znaki % i _. Mało tego, doświadczenie pokazuje, że wiele osób nie jest świadoma ich istnienia. Służą one do określenia zestawu znaków, które mogą znaleźć się na określonej pozycji. Zapis [abc] oznacza, że w danym miejscu może się pojawić litera a, b lub c. Alternatywą jest zapis [a-c], czyli wszystkie znaki od a do c. Najłatwiej przedstawić wszystko na przykładzie.

Zadanie 5. Potrzebujemy wszystkich produktów z tabeli Produkty, które należą do kategorii A1, B1, C1 lub D1.

Rozwiązanie znajduje się poniżej:

SELECT * FROM Produkty WHERE Kategoria LIKE '[ABCD]1'
--lub alternatywnie
SELECT * FROM Produkty WHERE Kategoria LIKE '[A-D]1'

Naturalnie każde takie kombinacje można ze sobą łączyć. Przeanalizujmy jeszcze jeden przykład:

Zadanie 6. Potrzebujemy wszystkich produktów z tabeli Produkty, które należą do kategorii A1, B1, C1 lub D1 i podkategorii W1 lub W2. Nazwa kategorii zapisana jest w formacie XN.YM, gdzie XN to rodzaj kategorii, a YM to podkategoria.

Przyjrzyjmy się teraz rozwiązaniu tego nieco trudniejszego zadania aprezentowanemu poniżej:

SELECT * FROM Produkty WHERE Kategoria LIKE '[A-D]1.W[12]'

Pierwsze dopasowanie dotyczy litery z zakresu od A do D, po nim następuje cyfra jeden, separator podkategorii, litera W i jedna z dwóch dozwolonych wartości 1 lub 2. Warto zwrócić uwagę na użycie dopasowania grupowego w dwóch miejscach i na dwa sposoby. Pierwsze dopasowanie podane jest w postaci zakresu A-D, drugie natomiast jawnie wskazane jako 1 lub 2 ([12]). Nie należy mylić tego z wyszukiwaniem ciągu znaków 12. Ciąg znaków 12 podawany byłby bez nawiasów kwadratowych.

Negacja znaków, czyli [^ ]

Tworzenie wzorców za pomocą znaków specjalnych jest zagadnieniem dużo bardziej skomplikowanym niż się na początku wydaje. Temat jest tak obszerny, że zostanie pewnie w niedługim czasie rozwinięty. Aby dopełnić przynajmniej zagadnienie nawiasów kwadratowych, warto wspomnieć o znaku negacji w postaci symbolu ^. Przypuśćmy, że mamy takie oto zadanie:

Zadanie 7. Potrzebujemy wszystkich produktów z tabeli Produkty, których podkategoria nie zaczyna się na W. Nazwa kategorii zapisana jest w formacie XN.YM, gdzie XN to rodzaj kategorii, a YM to podkategoria.

Rozwiązanie może wyglądać następująco:

SELECT * FROM Produkty WHERE Kategoria LIKE '__.[^W]_'

Jak to działa? Bardzo prosto. Kategoria zaczyna się od dwóch dowolnych znaków, po nich następuje kropka, po nich cokolwiek, byle nie W (i tutaj właśnie pojawia się znak negacji). Ostatni znak też jest dowolny. Co istotne, znak negacji może dotyczyć wielu elementów. Jeżeli napiszemy __.[^A-D]_, to otrzymamy wszystkie produkty, których podkategoria nie zaczyna się od liter A, B, C i D.

Podsumowanie i wnioski

Skomplikowanie niektórych wyrażeń może na początku przerażać. Doświadczenie pokazuje jednak, że zdecydowana większość warunków LIKE korzysta tylko z jednego znaku specjalnego, a jest nim najczęściej znak procentu (%). Pozostałe znaki pojawiają się sporadycznie.

Jeżeli w wyszukiwanym tekście szukamy znaku % lub innego znaku specjalnego, wtedy konieczne może się okazać użycie nieco innych technik. Zachęcam do zapoznania się z artykułem SQL Server i wyszukiwanie znaków specjalnych w tekście.

W jaki sposób najłatwiej można sobie przyswoić skomplikowane wyrażenia korzystajce z wielu symboli i nawiasów kwadratowych? Poćwiczyć na prostych przykładach i w miarę oswajania się komplikować sobie własnoręcznie wymyślane zadania. Zachęcam do dzielenia się ciekawymi zadaniami w komentarzach.

Kategoria:SQL Server

, 2013-12-20

Komentarze:

Mikołaj (2013-02-08 15:27:46)
A jak wyszukać podkreślenie?
"_"
PD (2013-02-10 13:57:31)
Aby znaleźć wszystkie rekordy z tabeli Tabela zawierające podkreślenie w kolumnie Kolumna:
SELECT * FROM Tabela WHERE Kolumna LIKE '%[_]%'
--lub
SELECT * FROM Tabela WHERE Kolumna LIKE '%\_%' ESCAPE '\'
Temat jest rozwinięty tutaj: SQL Server i wyszukiwanie znaków specjalnych w tekście.
RaV (2013-07-03 10:14:16)
A moze ktos wie, jak wyszukac " ' ", a dokladniej ciag znakow z " ' ' ?
PD (2013-07-03 13:10:32)
Pojedynczy apostrof reprezentowany jest przez podwójny apostrof. Żeby w SQL utworzyć łańcuch znaków z ' należy w apostrofie umieścić dwa apostrofy (w sumie będą cztery). Parę przykładów, gdzie pierwsza kolumna to zapis tekstowy, druga ciąg SQL:
a = 'a' (a = a, w SQL test jest ograniczony apostrofami)
a'b = 'a''b' (a[apostrof]b = a[apostrof][apostrof]b)
8" = '8"' (8[cudzysłów] = 8[cudzysłów])
'AX' = '''AX''' ([apostrof]AX[apostrof] = [apostrof][apostrof]AX[apostrof][apostrof])
W skrócie: apostrof należy zamienić na dwa apostrofy.

Przykład SQL z LIKE:

SELECTFROM
(VALUES
(1, '''"'''),--apostrof,cudzysłów,apostrof
(2, '''''"'),--apostrof,apostrof,cudzysłów
(3, '"''"')) --cudzysłów,apostrof,cudzysłów
T(ID, Txt)
WHERE Txt LIKE '%''''%'--dwa sąsiednie apostrofy
Zapytanie zwróci rekord numer 2.
Goa'uld (2015-11-15 17:24:47)
S ktoś poradzi jak wyszukać wszystkie słowa zaczynające się z dowolnej dużej litery?
dom (2017-03-28 14:52:10)
nie mogę sobie poradzić z wyszukaniem w tabeli wszystkich określeń zawierających PSC z puli takich jak PSC36_zaglowa_gr może przez dwukrotny znak _
próbowałem tak ............... AND (v_AlarmEventHistory.Area='PSC%') .......... chciałme użyć ESCAPE ale wtedy zgłasza błąd
PD (2017-03-30 10:31:05)
Powyższe nie zadziała, bo potrzebne jest słowo kluczowe LIKE. Operator = nie obsługuje znaków specjalnych i wzorców.
piotrom (2017-10-17 09:36:43)
Witam
A jak napisać szukaj '%' bo mam w tabeli słowa gdzie występuje % i nie wiem jak znaleźć ten znak specjalny.
PD (2017-10-18 12:46:05)
W sekcji *Podsumowanie i wnioski* jest odnośnik do innego artykułu rozszerzającego powyższy. Tam jest opisany sposób radzenia sobie ze znakami specjalnymi w wyszukiwanym tekście.
Karol (2018-03-07 08:52:39)
Jak znaleźć ciąg znaków z innej komórki? np mam tabele TwrKod i drugą TwrNazwa i chciałbym znaleść wszystkie nazwy zawierające TwrKod
Dodaj komentarz
Wyślij
Ostatnie komentarze
To samo pytanie co wyżej. Mam za zadanie dodać kolumnę do istniejącej tabeli łącząc obie inne kolumny ze sobą, ale nie mam pojęcia jak za to się zabrać
działa :) tylko była literówka :)
Podziękował. Trochę późno, po 8 latach, ale dzięki za testy (rozumiem że dla SQL2012 robione). Tak się właśnie zastanawiałem ile złego czynię stosując czasem __(max).
Super robota, korzystając z innych internetowych kalkulatorow po prostu wątpiłem w ich prawdomówność, w końcu trafiłem tutaj i wynik w końcu jest wiarygodny. 40 km w 2 h 810 kcal, ciekawostka: na fitatu wyliczyło mi 5700 kcal 😊 najlepiej będzie chyba jak kupię zegarek sportowy.
Wielkie dzieki za solidne wyjasnienia tematu.