CONTAINS (Transact-SQL)

Predicato utilizzato in una clausola WHERE per eseguire ricerche in colonne contenenti dati di tipo carattere per corrispondenze esatte e fuzzy (meno precise) con singole parole e frasi, la prossimità delle parole separate da una distanza massima specifica oppure corrispondenze ponderate.

In SQL Server è possibile utilizzare nomi composti da quattro parti nei predicati full-text CONTAINS o FREETEXT per eseguire query su server collegati.

Il predicato CONTAINS consente di cercare:

• Una parola o una frase.

• Il prefisso di una parola o di una frase.

• Una parola accanto a una parola specifica.

• Una parola generata da un'altra per flessione, ad esempio le parole guida, guidare, guidando e guidare derivanti dalla radice guida.

• Sinonimo di una parola specifica utilizzando il thesaurus. Le parole alluminio e acciaio, ad esempio, possono essere sinonimi della parola metallo.

Convenzioni della sintassi Transact-SQL

Sintassi

CONTAINS
      ( { column_name | ( column_list ) | * } 
          , '<contains_search_condition>'     
   [ , LANGUAGE language_term ]
      ) 

<contains_search_condition> ::= 
    { <simple_term> 
    | <prefix_term> 
    | <generation_term> 
    | <proximity_term> 
    | <weighted_term> 
    } 
    | { ( <contains_search_condition> ) 
    [ { <AND> | <AND NOT> | <OR> } ] 
    <contains_search_condition> [ ...n ] 
    } 

<simple_term> ::= 
          word | "phrase"

<prefix term> ::= 
     { "word *" | "phrase *" }

<generation_term> ::= 
     FORMSOF ( { INFLECTIONAL | THESAURUS } , <simple_term> [ ,...n ] ) 

<proximity_term> ::= 
     { <simple_term> | <prefix_term> } 
     { { NEAR | ~ }
     { <simple_term> | <prefix_term> } 
     } [ ...n ] 

<weighted_term> ::= 
     ISABOUT 
        ( { { 
  <simple_term> 
  | <prefix_term> 
  | <generation_term> 
  | <proximity_term> 
  } 
   [ WEIGHT ( weight_value ) ] 
   } [ ,...n ] 
        ) 

<AND> ::= 
     { AND | & }

<AND NOT> ::= 
     { AND NOT | &! }

<OR> ::= 
     { OR | | }

Argomenti

• column_name
  Nome di una colonna con indicizzazione full-text della tabella specificata nella clausola FROM. Le colonne possono essere di tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary o varbinary(max).

• column_list
  Specifica due o più colonne, separate da virgole. column_list deve essere racchiuso tra parentesi. Se non si specifica language_term, la lingua di tutte le colonne in column_list deve essere identica.

• *
  Specifica che la query eseguirà la ricerca in tutte le colonne con indicizzazione full-text nella tabella specificata nella clausola FROM per la condizione di ricerca indicata. Le colonne nella clausola CONTAINS devono appartenere a una singola tabella che include un indice full-text. Se non si specifica language_term, la lingua di tutte le colonne della tabella deve essere identica.

• LANGUAGE language_term
  Lingua da utilizzare per word breaking, stemming, espansioni e sostituzioni del thesaurus e rimozione di parole non significative come parte della query. Questo parametro è facoltativo.

  Se documenti di lingue diverse vengono archiviati insieme come oggetti BLOB in una singola colonna, l'identificatore delle impostazioni locali (LCID) di un documento specifico determina la lingua da utilizzare per indicizzarne il contenuto. Se quando si esegue una query su una colonna di questo tipo si specifica LANGUAGE language_term, è possibile aumentare la probabilità di una corrispondenza soddisfacente.

  È possibile specificare language_term come valore stringa, intero o esadecimale corrispondente all'identificatore LCID di una lingua. Se si specifica language_term, la lingua rappresentata dall'argomento verrà applicata a tutti gli elementi della condizione di ricerca. Se non si specifica alcun valore, verrà utilizzata la lingua full-text della colonna.

  Se specificato come stringa, language_term corrisponde al valore della colonna alias nella vista di compatibilità sys.syslanguages (Transact-SQL). La stringa deve essere racchiusa tra virgolette singole, come in 'language_term'. Se l'argomento language_term viene specificato come valore integer, corrisponde all'LCID effettivo che identifica la lingua. Se specificato come valore esadecimale, language_term è 0x seguito dal valore esadecimale dell'LCID. Il valore esadecimale non deve superare le otto cifre, inclusi gli zero iniziali.

  Se il valore è in formato DBCS (Double-Byte Character Set), verrà convertito in Unicode da SQL Server.

  Se la lingua specificata non è valida o non vi sono risorse installate corrispondenti a tale lingua, SQL Server restituisce un errore. Per utilizzare le risorse della lingua neutra, specificare 0x0 per language_term.

• <contains_search_condition>
  Specifica il testo da cercare in column_name e le condizioni della ricerca.

  <contains_search_condition> è di tipo nvarchar. Viene eseguita una conversione implicita quando si utilizza come input un tipo di dati character diverso. Nell'esempio seguente la variabile @SearchWord, definita come varchar(30), causa una conversione implicita nel predicato CONTAINS.

  USE AdventureWorks2008R2;
  GO
  DECLARE @SearchWord varchar(30)
  SET @SearchWord ='performance'
  SELECT Description 
  FROM Production.ProductDescription 
  WHERE CONTAINS(Description, @SearchWord);
  

  Poiché non è possibile utilizzare lo sniffing dei parametri nella conversione, utilizzare nvarchar per migliorare le prestazioni. Nell'esempio dichiarare @SearchWord come nvarchar(30).

  USE AdventureWorks2008R2;
  GO
  DECLARE @SearchWord nvarchar(30)
  SET @SearchWord = N'performance'
  SELECT Description 
  FROM Production.ProductDescription 
  WHERE CONTAINS(Description, @SearchWord);
  

  È inoltre possibile utilizzare l'hint per la query OPTIMIZE FOR per i casi in cui viene generato un piano non ottimale.

• word
  Stringa di caratteri senza spazi o punteggiatura.

• phrase
  Una o più parole separate da uno spazio.

  Nota

  In alcune lingue, ad esempio quelle asiatiche, sono possibili frasi composte da una o più parole non separate da alcuno spazio.

• <simple_term>
  Specifica che deve essere trovata l'esatta corrispondenza di una parola o di una frase. Esempi di termini semplici validi sono "copri capo", copricapo e "Microsoft SQL Server". Le frasi devono essere racchiuse tra virgolette doppie (""). Le parole di una frase devono essere specificate in <contains_search_condition> nello stesso ordine utilizzato nella colonna del database. La distinzione tra maiuscole e minuscole non è rilevante per la ricerca di caratteri nella parola o nella frase. Le parole non significative, quali preposizioni, congiunzioni e articoli, delle colonne con indicizzazione full-text non vengono archiviate nell'indice full-text. Se una parola non significativa viene utilizzata nella ricerca di una singola parola, SQL Server restituisce un messaggio di errore che indica che la query contiene solo parole non significative. In SQL Server è incluso un elenco standard di parole non significative, disponibile nella directory \Mssql\Binn\FTERef di ogni istanza di SQL Server.

  La punteggiatura viene ignorata. CONTAINS(testing, "computer failure"), pertanto, consente di trovare una riga contenente il valore "Where is my computer? Failure to find it would be expensive". Per ulteriori informazioni sul funzionamento dei word breaker, vedere Word breaker e stemmer.

• <prefix_term>
  Specifica che devono essere trovate parole o frasi che iniziano con il testo specificato. Racchiudere un prefisso tra virgolette doppie ("") e aggiungere un asterisco (*) prima delle virgolette di chiusura, in modo tale da eseguire la ricerca di tutto il testo che inizia con il termine specificato prima dell'asterisco. La clausola deve essere specificata nel seguente modo: CONTAINS (column, '"text*"') L'asterisco corrisponde a zero, uno o più caratteri della parola o delle parole radice della parola o della frase. Se il testo e l'asterisco non sono racchiusi tra virgolette doppie, per cui vengono letti dal predicato come CONTAINS (column, 'text*'), nella ricerca full-text l'asterisco viene interpretato come carattere e viene eseguita la ricerca esatta di text*. In questo caso, non sarà possibile trovare parole con il carattere asterisco (*), in quanto i word breaker ignorano in genere tali caratteri.

  Se <prefix_term> è una frase, ogni parola della frase verrà considerata un prefisso distinto. Una query che specifica il prefisso "sviluppo foto*", pertanto, consente di individuare tutte le righe che includono il testo "sviluppo fotografie", "sviluppo fotografie e ritocco" e così via.

• <generation_term>
  Specifica che devono essere trovate le parole i cui termini semplici includono varianti della parola originale da cercare.

• INFLECTIONAL
  Specifica che deve essere utilizzato lo stemmer specifico della lingua per il termine semplice specificato. Il comportamento dello stemmer dipende dalle regole di stemming di ogni lingua specifica. Alla lingua neutra non è associato alcuno stemmer. Per individuare lo stemmer corretto, viene utilizzata la lingua delle colonne in cui viene eseguita la ricerca. Se si specifica language_term, viene utilizzato lo stemmer corrispondente a tale lingua.

  Un valore <simple_term> specificato incluso in un valore <generation_term> non potrà restituire sia sostantivi che verbi.

• THESAURUS
  Specifica che verrà utilizzato il thesaurus corrispondente alla lingua full-text per le colonne oppure alla lingua specificata nella query. Il derivato o i derivati più lunghi di <simple_term> vengono confrontati con il thesaurus e vengono generati termini aggiuntivi per espandere o sostituire il modello originale. Se non viene trovata alcuna corrispondenza per il valore <simple_term> o parte di esso, la parte per cui non è stata trovata alcuna corrispondenza viene considerata simple_term. Per ulteriori informazioni sul thesaurus per la ricerca full-text, vedere Configurazione del thesaurus.

• <proximity_term>
  Specifica che devono essere trovate parole o frasi incluse nel documento in cui viene eseguita la ricerca. Analogamente all'operatore AND, con <proximity_term> è necessario che entrambi i termini di ricerca siano presenti nel documento in cui viene eseguita la ricerca.

  • NEAR | ~
    Indica che le parole o le frasi a sinistra e a destra dell'operatore NEAR or ~ devono essere incluse in un documento in modo che venga restituita una corrispondenza. È possibile concatenare più termini, ad esempio a NEAR b NEAR c o a ~ b ~ c. I termini vicini concatenati devono essere tutti presenti nel documento perché venga restituita una corrispondenza.

    Se utilizzata nella funzione CONTAINSTABLE, la prossimità dei termini di ricerca influisce sulla pertinenza di ogni documento. Più vicini sono i termini di ricerca corrispondenti, maggiore sarà la pertinenza del documento. Se i termini di ricerca corrispondenti distano uno dall'altro oltre >50 termini, la pertinenza del documento sarà pari a 0.

    CONTAINS (nome_colonna, 'volpe NEAR pollo') e CONTAINSTABLE (nome_tabella, nome_colonna, 'volpe ~ pollo') restituiscono in entrambi i casi tutti i documenti inclusi nella colonna specificata che contengono entrambe le parole "volpe" e "pollo". CONTAINSTABLE, inoltre, restituisce una pertinenza per ogni documento in base alla prossimità delle parole "volpe" e "pollo". Se, ad esempio, un documento contiene la frase "La volpe sbranò il pollo", la pertinenza sarà maggiore.

    NEAR indica la distanza logica tra termini, piuttosto che la distanza assoluta tra loro. Ad esempio, i termini all'interno di espressioni o frasi diverse di un paragrafo vengono trattati come più lontani dei termini nella stessa espressione o frase, indipendentemente dalla loro prossimità effettiva, presupponendo che siano meno correlati. In modo analogo, i termini presenti in paragrafi diversi vengono trattati come ancora più lontani.

• <weighted_term>
  Specifica che le righe restituite dalla query corrispondono a un elenco di parole e di frasi, a ognuna delle quali può essere associato un valore di ponderazione facoltativo.

• ISABOUT
  Specifica la parola chiave <weighted_term>.

  • WEIGHT(weight_value)
    Specifica un valore di ponderazione compreso tra 0.0 e 1.0. Ogni componente in <weighted_term> può includere un valore weight_value. L'utilizzo di weight_value consente di modificare l'effetto delle varie parti di una query sul valore di pertinenza assegnato a ogni riga che soddisfa la query. WEIGHT non ha alcun effetto sui risultati delle query CONTAINS, ma ha effetto sul valore di pertinenza nelle query CONTAINSTABLE.

    Nota

    Il separatore decimale è sempre un punto, indipendentemente dalle impostazioni locali del sistema operativo.

• { AND | & } | { AND NOT | &! } | { OR | | }
  Specifica un'operazione logica tra due condizioni di ricerca del predicato CONTAINS.

  • AND | &
    Indica che devono essere soddisfatte entrambe le condizioni di ricerca del predicato CONTAINS. Per rappresentare l'operatore AND, è possibile utilizzare il carattere e commerciale (&) anziché la parola chiave AND.

  • AND NOT | &!
    Indica che la seconda condizione di ricerca non deve essere soddisfatta. Per rappresentare l'operatore AND NOT, è possibile utilizzare il carattere e commerciale seguito dal punto esclamativo (&!) anziché la parola chiave AND NOT.

  • OR | |
    Indica che deve essere soddisfatta una delle due condizioni di ricerca del predicato CONTAINS. Per rappresentare l'operatore OR, è possibile utilizzare il carattere barra (|) anziché la parola chiave OR.

    Quando <contains_search_condition> include gruppi tra parentesi, tali gruppi vengono valutati per primi. In seguito alla valutazione dei gruppi tra parentesi, gli operatori logici che includono condizioni di ricerca vengono valutati in base alle regole seguenti:

    • L'operatore NOT viene applicato prima dell'operatore AND.

    • L'operatore NOT può essere utilizzato solo dopo l'operatore AND, ad esempio in AND NOT. L'operatore OR NOT non è consentito. Non è possibile specificare l'operatore NOT prima del primo termine. CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ), ad esempio, non è valido:

    • L'operatore AND viene applicato prima dell'operatore OR.

    • Gli operatori booleani dello stesso tipo (AND, OR) sono associativi e pertanto possono essere applicati in qualsiasi ordine.

    • n
      Segnaposto che indica la possibilità di specificare più termini e più condizioni di ricerca CONTAINS.

Osservazioni

I predicati e le funzioni full-text possono essere utilizzati in una singola tabella, specificata in modo implicito nel predicato FROM. Per cercare in più tabelle, utilizzare una tabella unita in join nella clausola FROM, che consente di eseguire una ricerca in un set di risultati prodotto da due o più tabelle.

Il predicato CONTAINS non viene riconosciuto come parola chiave se il livello di compatibilità è inferiore a 70. Per ulteriori informazioni, vedere sp_dbcmptlevel (Transact-SQL).

I predicati full-text non sono consentiti nella clausola OUTPUT quando il livello di compatibilità del database è impostato su 100.

Confronto tra LIKE e la ricerca full-text

Contrariamente alla ricerca full-text, il predicato Transact-SQLLIKE funziona unicamente con le corrispondenze di caratteri. Non è inoltre possibile utilizzare il predicato LIKE per eseguire query su dati binari formattati. Inoltre, l'esecuzione di una query LIKE su una grande quantità di dati di testo non strutturati è molto più lenta dell'esecuzione di una query full-text equivalente sugli stessi dati. Una query LIKE eseguita su milioni di righe di dati di testo può richiedere diversi minuti, mentre per una query full-text sugli stessi dati possono essere necessari pochi secondi al massimo, a seconda del numero di righe restituite.

Esempi

A. Utilizzo di CONTAINS con <simple_term>

Nell'esempio seguente vengono trovati tutti i prodotti il cui prezzo è $80.99 e che includono la parola "Mountain".

USE AdventureWorks2008R2;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
   AND CONTAINS(Name, 'Mountain');
GO

B. Utilizzo di CONTAINS e di una frase in <simple_term>

Nell'esempio seguente vengono restituiti tutti i prodotti che contengono la frase "Mountain" o "Road".

USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Mountain" OR "Road" ')
GO

C. Utilizzo di CONTAINS con <prefix_term>

Nell'esempio seguente vengono restituiti tutti i nomi di prodotto della colonna Name contenenti almeno una parola che inizia con il prefisso chain.

USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
GO

D. Utilizzo di CONTAINS e OR con <prefix_term>

Nell'esempio seguente vengono restituite tutte le descrizioni di categoria contenenti stringhe con il prefisso "chain" o "full".

USE AdventureWorks2008R2;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO

E. Utilizzo di CONTAINS con <proximity_term>

Nell'esempio seguente vengono restituiti tutti i nomi di prodotto in cui la parola bike è vicina alla parola performance.

USE AdventureWorks2008R2;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');
GO

F. Utilizzo di CONTAINS con <generation_term>

Nell'esempio seguente viene eseguita la ricerca di tutti i prodotti con parole derivate da ride, ad esempio riding, ridden e così via.

USE AdventureWorks2008R2;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ');
GO

G. Utilizzo di CONTAINS con <weighted_term>

Nell'esempio seguente viene eseguita la ricerca di tutti i nomi di prodotto contenenti la parola performance, comfortable o smooth. Ogni parola viene ponderata in modo diverso.

USE AdventureWorks2008R2;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'ISABOUT (performance weight (.8), 
comfortable weight (.4), smooth weight (.2) )' );
GO

H. Utilizzo di CONTAINS con variabili

Nell'esempio seguente viene utilizzata una variabile anziché un termine di ricerca specifico.

USE AdventureWorks2008R2;
GO
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = N'Performance'
SELECT Description 
FROM Production.ProductDescription 
WHERE CONTAINS(Description, @SearchWord);
GO

I. Utilizzo di CONTAINS con un operatore logico (AND)

Nell'esempio seguente viene utilizzata la tabella ProductDescription del database AdventureWorks2008R2. Nella query viene utilizzato il predicato CONTAINS per cercare le descrizioni il cui ID sia diverso da 5 e che contengano le parole "Aluminium" e "splindle". Nella condizione di ricerca viene utilizzato l'operatore booleano AND.

USE AdventureWorks2008R2;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, ' Aluminum AND spindle');
GO

J. Utilizzo di CONTAINS per verificare l'inserimento di una riga

Nell'esempio seguente viene utilizzato il predicato CONTAINS in una sottoquery SELECT. Se si utilizza il database AdventureWorks2008R2, la query consente di ottenere il valore di commento per tutti i commenti della tabella ProductReview per un ciclo specifico. Nella condizione di ricerca viene utilizzato l'operatore booleano AND.

USE AdventureWorks2008R2;
GO
INSERT INTO Production.ProductReview 
(ProductID, ReviewerName, EmailAddress, Rating, Comments) 
VALUES
(780, 'John Smith', 'john@fourthcoffee.com', 5, 
'The Mountain-200 Silver from Adventure Works Cycles meets and exceeds expectations. I enjoyed the smooth ride down the roads of Redmond')
 
-- Given the full-text catalog for these tables is Adv_ft_ctlg, 
-- with change_tracking on so that the full-text indexes are updated automatically.
WAITFOR DELAY '00:00:30'   
-- Wait 30 seconds to make sure that the full-text index gets updated.
 
SELECT r.Comments, p.Name
FROM Production.ProductReview r
JOIN Production.Product p 
ON
 r.ProductID = p.ProductID
 
AND r.ProductID = (SELECT ProductID
                  FROM Production.ProductReview
                  WHERE CONTAINS (Comments, 
                                 ' Adventure Works AND 
                                   Redmond AND 
                                   "Mountain-200 Silver" '))

GO

Vedere anche

Attivitá

Procedura: Creazione di un catalogo full-text (SQL Server Management Studio)

Procedura: Avvio dell'Indicizzazione guidata full-text (SQL Server Management Studio)

Procedura: Creazione di query di ricerca full-text (Visual Database Tools)

Riferimento

CREATE FULLTEXT CATALOG (Transact-SQL)

CREATE FULLTEXT INDEX (Transact-SQL)

CONTAINSTABLE (Transact-SQL)

FREETEXT (Transact-SQL)

FREETEXTTABLE (Transact-SQL)

WHERE (Transact-SQL)

Concetti

Introduzione alla ricerca full-text

Cenni preliminari sull'indicizzazione full-text

Cenni preliminari su funzioni e predicati full-text

Nozioni fondamentali sui join

Cenni preliminari su funzioni e predicati full-text

Ricerca full-text (SQL Server)