SQL Serwer wspiera dwa bardzo potężne predykaty służące do wyszukiwania pełnotekstowego. Są to predykaty CONTAINS i FREETEXT. Oba pozwalają na różnorodne przeszukiwanie tekstu. Poza nimi , SQL Server pozwala na użycie specjalnych funkcji tablicowych do przeszukiwania pełnotekstowego ale o nich w następnej lekcji.

Predykat CONTAINS

Użycie predykatu CONTAINS pozwala na wyszukiwanie:

• Wyrazów i fraz w tekście.
• Dokładnych lub rozmytych dopasowań fraz.
• Form fleksyjnych wyrazów.
• Wyrazów podobnych.
• Synonimów.
• Fraz zaczynających się od szukanego prefiksu.

Można również dodać własne wagi do wyszukiwanych słów. Predykatu CONTAINS używa się w klauzuli WHERE zapytania T-SQL. Dokładny opis można znaleźć na stronie http://msdn.microsoft.com/en-us/library/ms187787.aspx. Najczęściej wykorzystywanymi formami predykatu CONTAINS są zaprezentowana poniżej gdzie FTcolumn jest kolumną z indeksem pełnotekstowym:

• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘SearchWord1’) - najprostrza forma w której szukamy dokładnego dopasowania kolumny FTcolumn i wyszukiwanego słowa.
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘SearchWord1 OR SearchWord2’) - szukamy dokładnego dopasowania jednego z dwóch słów  SearchWord1 lub SearchWord2.  Można również stosować inne działania logiczne jak AND i NOT.
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘”SearchWord1 SearchWord2”’) - wyszukujemy dokładnie konkretnej frazy ”SearchWord1 SearchWord2”.
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘”SearchWord1*”’) - wyszukiwanie wierszy w których kolumna FTcolumn zawiera przynajmniej jedno słowo zaczynające się od liter ”SearchWord1” (prefiksu).
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘NEAR (SearchWord1,SearchWord2)’) - wyszukiwanie wierszy w których występują oba wyszukiwane słowa SearchWord1 i SearchWord2 bez względu na odległość między nimi i ich kolejność.
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘NEAR ((SearchWord1,SearchWord2),distance)’) - wyszukiwanie wierszy w których występują oba wyszukiwane słowa SearchWord1 i SearchWord2 bez względu na ich kolejność lecz z uwzględnieniem odległości pomiędzy nimi ograniczonej parametrem distance.
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘NEAR ((SearchWord1,SearchWord2),distance, flag)’) - wyszukiwanie wierszy w których występują oba wyszukiwane słowa SearchWord1 i SearchWord2 z uwzględnieniem odległości pomiędzy nimi ograniczonej parametrem distance i jezeli flaga jest ustawiona na TRUE to również uwzględniona jest kolejność wyszukiwanych wyrazów.
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘FORMSOF(INFLECTIONA L,SearchWord1)’) - wyszukiwanie dowolnej formy fleksyjnej  słowa SearchWord1.
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘FORMSOF(THESAURUS ,SearchWord1)’) - wyszukiwanie słowa SearchWord1 lub jego synonimów zapisanych w pliku THESAURUS.
• SELECT …FROM…WHERE CONTAINS (FTcolumn, ‘ISABOUT (SearchWord1 weight(w1), SearchWord2 weight(w2))’) - jest to term zawierający wagi wyszukiwanych fraz które mają wpływ na kolejność zwracanych dokumentów. predykat CONTAINS nie sortuje wyników to wagi nie mają w tym wypadku znaczenia. Forma z wagami ma wpływ na użycie funkcji CONTAINSTABLE.
• SELECT …FROM…WHERE CONTAINS (PROPERTY (FTcolumn, ‘PropertyName’),‘SearchWord1’) - jest to term wyszukujący właściwości dokumentu. Wyszukuje on dokumenty które mają właściwość 'PropertyName' o wartości SearchWord1.

Predykat FREETEXT

Predykat FREETEXT jest mniej szczegółowy i zwraca więcej rekordów od predykatu CONTAINS. Wyszukuje wartości pasujących do znaczenia wyrażenia, a nie tylko dokładne słowa. Przy użyciu predykatu FREETEXT silnik bazy danych łamie wyszukiwaną frazę, generuje formy fleksyjne i identyfikuje listę rozszeżeń lub zastąpień dla szukanych słów z pliku THESAURUS. Forma jest dużo prostsza niż dla predykatu CONTAINS:

• SELECT…FROM…WHERE FREETEXT(FTcolumn, ‘SearchWord1 SearchWord2’) - wyszukuje wszystkich napotkanych form wyrazów SearchWord1 i SearchWord2

*! FREETEXT jest mniej selektywny niż CONTAINS i zwykle zwraca więcej wierszy niż CONTAINS.

Ćwieczenia

I. Użycie predykatu CONTAINS oraz użycie i edycja pliku THESAURUS.

1. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają słowo "data" w kolumnie docexcerpt.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'data');
   

2. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają słowo "data" lub "index" w kolumnie docexcerpt.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'data OR index');

3. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają słowo "data" i nie zawiera słowa "mining" w kolumnie docexcerpt.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'data AND NOT mining');

4. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają słowo "data" i słowa "fact" lub "warehouse" w kolumnie docexcerpt.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'data OR (fact AND warehouse)');

5. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają frazę "data warehouse" w kolumnie docexcerpt.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'"data warehouse"');

6. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS  które zawierają słowa które zaczynają się prefiksem "add" w kolumnie docexcerpt.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'"add*"');

7. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają słowa "problem" niedaleko słowa "data" w kolumnie docexcerpt.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'NEAR(problem, data)');

8. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają słowa "problem" niedaleko słowa "data" w kolumnie docexcerpt. Słowa te mogą być oddalone tylko o 5 miejsc.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'NEAR((problem, data),5)');

9. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają słowa "problem" niedaleko słowa "data" w kolumnie docexcerpt. Słowa te mogą być oddalone tylko o 5 miejsc i słowo "problem" musi być przed słowem "data".

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'NEAR((problem, data),5, TRUE)');

10. Znajdź wszystkie wiersze tabeli DBO.DOCUMENTS które zawierają słowa "presentation" w kolumnie docexcerpt. Spróbuj wyszukać dokładnego dopasowania i zapytania które wyszuka wszystkie formy fleksyjne.

    SELECT id, title, docexcerpt
    FROM dbo.Documents
    WHERE CONTAINS(docexcerpt, N'presentation');

    SELECT id, title, docexcerpt
    FROM dbo.Documents
    WHERE CONTAINS(docexcerpt, N'FORMSOF(INFLECTIONAL, presentation)');

II. Użycie synonimów i predykatu FREETEXT

1. Dodaj synonim "necessity" do słowa "need" w pliku THESAURUS dla języka angielskiego US. Plik który trzeba edytować to tsenu.xml który znajduje się w lokalizacji instalacji SQL Serwera - domyślnie:C:\Program Files\MicrosoftQLServer\MSSQL11.MSSQLSERVER\MSSQL\FTData. Po edycji plik powinien wyglądać tak:

   
   0
   
   Internet Explorer
   IE
   IE5
   
   
   NT5
   W2K
   Windows 2000
   
   
   run
   jog
   
   
   need
   necessity
   
   
   
   

   Plik trzeba załadować do instancji SQL za pomocą komendy T-SQL:

   EXEC sys.sp_fulltext_load_thesaurus_file 1033;
   

2. Znajdź wiersze w tabeli DBO.DOCUMENTS które zawierają słowo "need" lub jego synonimy w kolumnie docexcerpt. Użyj zapytania z dokładnym dopasowaniem i zapytania wyszukującego synonimy.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'need');
   

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(docexcerpt, N'FORMSOF(THESAURUS, need)');
   

3. Wyszukaj wiersze w tabeli DBO.DOCUMENTS które zawierają słowo "Dejan" we właściwości "Authors" dla dokumentu.

   SELECT id, title, docexcerpt
   FROM dbo.Documents
   WHERE CONTAINS(PROPERTY(doccontent,'Authors'), 'Dejan');
   

4. Znajdź wiersze w tabeli DBO.DOCUMENTS które zawierają słowa "data","presentation" lub "need" w kolumnie docexcerpt. Wyszukiwane słowa mogą mieć jakąkolwiek formę fleksyjną lub być synonimem.

   SELECT id, title, doctype, docexcerpt
   FROM dbo.Documents
   WHERE FREETEXT(docexcerpt, N'data presentation need');
   

Podsumowanie

1. Predykat CONTAINS służy do wyszukiwania selektywnego.
2. Predykat FREETEXT służy do wyszukiwania generalizacji fraz.