Query con ricerca testuale completa

Per definire le ricerche full-text, le query full-text di SQL Server usano i predicati full-text (CONTAINS e FREETEXT) e le funzioni (CONTAINSTABLE e FREETEXTTABLE). Questi supportano una sintassi avanzata Transact-SQL che supporta un'ampia gamma di forme di termini di query. Per scrivere query full-text, è necessario apprendere quando e come usare questi predicati e le funzioni.

Panoramica dei predicati Full-Text (CONTAINS e FREETEXT)

I predicati CONTAINS e FREETEXT restituiscono un valore TRUE o FALSE. Possono essere usati solo per specificare i criteri di selezione per determinare se una determinata riga corrisponde alla query full-text. Le righe corrispondenti vengono restituite nel set di risultati. CONTAINS e FREETEXT vengono specificati nella clausola WHERE o HAVING di un'istruzione SELECT. Possono essere combinati con qualsiasi altro predicato Transact-SQL, ad esempio LIKE e BETWEEN.

Quando si usa CONTAINS o FREETEXT, è possibile specificare una singola colonna, un elenco di colonne o tutte le colonne della tabella da cercare. Facoltativamente, è possibile specificare la lingua le cui risorse verranno utilizzate dalla query full-text per l'interruzione delle parole, lo stemming, le ricerche nel thesaurus e la rimozione di termini comuni.

CONTAINS e FREETEXT sono utili per diversi tipi di corrispondenze, come segue:

• Usare CONTAINS (o CONTAINSTABLE) per corrispondenze precise o approssimative (meno precise) a singole parole e frasi, per valutare la prossimità di parole entro una certa distanza tra loro, o per corrispondenze ponderate. Quando si usa CONTAINS, è necessario specificare almeno una condizione di ricerca che specifica il testo cercato e le condizioni che determinano le corrispondenze.

  È possibile usare l'operazione logica tra le condizioni di ricerca. Per altre informazioni, vedere Uso degli operatori booleani AND, OR, AND NOT (in CONTAINS e CONTAINSTABLE) più avanti in questo argomento.

• Utilizzare FREETEXT (o FREETEXTTABLE) per la corrispondenza del significato, ma non della formulazione esatta, di parole, frasi o frasi specificate ( stringa di testo libero). Vengono generate corrispondenze se nell'indice full-text di una colonna specificata viene trovato un qualsiasi termine o formato di un qualsiasi termine.

È possibile usare un nome in quattro parti nel predicato CONTAINS o FREETEXT per eseguire query di colonne con indicizzazione full-text delle tabelle di destinazione in un server collegato. Per preparare un server remoto alla ricezione di query full-text, creare un indice full-text delle tabelle di destinazione e delle colonne nel server remoto, quindi aggiungere il server remoto come server collegato.

Esempi

Un. Uso di CONTAINS con <simple_term>

Nell'esempio seguente vengono trovati tutti i prodotti con un prezzo di $80.99 che contiene la parola "Mountain".

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

B. Utilizzo di FREETEXT per la ricerca di parole contenenti valori di carattere specificati

Nell'esempio seguente viene eseguita la ricerca di tutti i documenti contenenti le parole associate a "vital", "safety" e "components".

USE AdventureWorks2012  
GO  
  
SELECT Title  
FROM Production.Document  
WHERE FREETEXT (Document, 'vital safety components')  
GO  

Panoramica delle funzioni di Full-Text (CONTAINSTABLE e FREETEXTTABLE)

Alle funzioni CONTAINSTABLE e FREETEXTTABLE viene fatto riferimento come un nome di tabella normale nella clausola FROM di un'istruzione SELECT. Restituiscono una tabella contenente zero, una o più righe che corrispondono alla ricerca full-text. La tabella restituita contiene solo righe della tabella di base che corrispondono ai criteri di selezione specificati nella condizione di ricerca full-text della funzione.

Le query che usano una di queste funzioni restituiscono un valore di classificazione della pertinenza (RANK) e una chiave full-text (KEY) per ogni riga, come indicato di seguito:

• Colonna KEY

  La colonna KEY restituisce valori univoci delle righe restituite. La colonna CHIAVE può essere utilizzata per specificare i criteri di selezione.

• Colonna RANK

  La colonna RANK restituisce un valore di pertinenza per ogni riga che indica il livello di corrispondenza tra la riga e i criteri di selezione. Più alto è il valore di classifica del testo o del documento in una riga, più rilevante sarà la riga per una determinata query full-text. Si noti che le diverse righe possono essere classificate in modo identico. È possibile limitare il numero di corrispondenze da restituire specificando il parametro top_n_by_rank facoltativo. Per altre informazioni, vedere Limitare i risultati della ricerca con RANK.

Quando si usa una di queste funzioni, è necessario specificare la tabella di base in cui eseguire la ricerca full-text. Come per i predicati, è possibile specificare una singola colonna, un elenco di colonne o tutte le colonne della tabella in cui eseguire la ricerca e, facoltativamente, la lingua le cui risorse verranno usate dalla query full-text specificata.

CONTAINSTABLE è utile per gli stessi tipi di corrispondenze di CONTAINS e FREETEXTTABLE è utile per gli stessi tipi di corrispondenze di FREETEXT. Per altre informazioni, vedere Panoramica dei predicati Full-Text (CONTAINS e FREETEXT) più indietro in questo argomento. Quando si eseguono query che usano le funzioni CONTAINSTABLE e FREETEXTTABLE, è necessario unire in modo esplicito le righe restituite con le righe nella tabella di base di SQL Server.

In genere, il risultato di CONTAINSTABLE o FREETEXTTABLE deve essere unito alla tabella di base. In questi casi, è necessario conoscere il nome della colonna chiave univoca. Questa colonna, presente in ogni tabella abilitata per la funzionalità full-text, viene usata per applicare righe univoche per la tabella (la colonna chiave**univoca). Per altre informazioni, vedere Gestire indici full-text.

Esempi

A. Uso di CONTAINSTABLE

Nell'esempio seguente vengono restituiti l'ID descrizione e la descrizione di tutti i prodotti per i quali la colonna Description contiene la parola "alluminio" vicino alla parola "light" o alla parola "lightweight". Vengono restituite solo le righe con un valore di rango pari a 2 o superiore.

USE AdventureWorks2012  
GO  
  
SELECT FT_TBL.ProductDescriptionID,  
   FT_TBL.Description,   
   KEY_TBL.RANK  
FROM Production.ProductDescription AS FT_TBL INNER JOIN  
   CONTAINSTABLE (Production.ProductDescription,  
      Description,   
      '(light NEAR aluminum) OR  
      (lightweight NEAR aluminum)'  
   ) AS KEY_TBL  
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK > 2  
ORDER BY KEY_TBL.RANK DESC;  
GO  

B. Uso di FREETEXTTABLE

Nell'esempio seguente viene estesa una query FREETEXTTABLE in modo che vengano restituite per prime le righe con valore di pertinenza maggiore e che la classificazione di ogni riga venga aggiunta all'elenco di selezione. Per specificare la query, è necessario sapere che ProductDescriptionID è la colonna chiave univoca per la ProductDescription tabella.

USE AdventureWorks2012  
GO  
  
SELECT KEY_TBL.RANK, FT_TBL.Description  
FROM Production.ProductDescription AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(Production.ProductDescription, Description,  
                    'perfect all-around bike') AS KEY_TBL  
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC  
GO  

Di seguito è riportato un'estensione della stessa query che restituisce solo righe con un valore di rango pari o superiore a 10:

USE AdventureWorks2012  
GO  
  
SELECT KEY_TBL.RANK, FT_TBL.Description  
FROM Production.ProductDescription AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(Production.ProductDescription, Description,  
                    'perfect all-around bike') AS KEY_TBL  
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK >= 10  
ORDER BY KEY_TBL.RANK DESC  
GO  

Uso degli operatori booleani - AND, OR e NOT - in CONTAINS e CONTAINSTABLE

Il predicato CONTAINS e la funzione CONTAINSTABLE utilizzano le stesse condizioni di ricerca. Entrambi supportano la combinazione di diversi termini di ricerca usando operatori booleani-AND, OR, AND NOT-per eseguire operazioni logiche. È possibile usare AND, ad esempio, per trovare righe contenenti sia "latte" che "bagel in stile New York". È possibile usare AND NOT, ad esempio, per trovare le righe che contengono "bagel", ma non contengono "formaggio crema".

Per informazioni sulla combinazione di CONTAINS con altri predicati che usano gli operatori logici AND, OR e NOT, vedi Condizione di ricerca (Transact-SQL).

Esempio

Nell'esempio seguente viene utilizzata la tabella ProductDescription del database AdventureWorks2012 . La query usa il predicato CONTAINS per cercare descrizioni in cui l'ID descrizione non è uguale a 5 e la descrizione contiene sia la parola "Alluminio" che la parola "spindle". La condizione di ricerca usa l'operatore booleano AND.

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

Considerazioni aggiuntive per le query Full-Text

Quando si scrivono query full-text, prendere in considerazione anche le seguenti considerazioni:

• Opzione LANGUAGE

  Molti termini di query dipendono in larga misura dal comportamento del word breaker. Per assicurarsi di utilizzare correttamente il word breaker e lo stemmer, oltre al file del thesaurus, consigliamo di specificare l'opzione LANGUAGE. Per altre informazioni, vedere Scelta di una lingua durante la creazione di un indice full-text.

• Parole vuote

  Quando si definisce una query full-text, il Motore di Ricerca Full-Text scarta le parole non significative dai criteri di ricerca. Per "parole vuote" si intendono parole quali "un", "e", "è" o "il", che possono ricorrere di frequente ma che tipicamente non aiutano nella ricerca di testo specifico. Le parole di stop sono elencate in una lista di stop. Ogni indice full-text è associato a un elenco di stopwords (parole non significative) specifico, che determina quali parole devono essere omesse dalla query o dall'indice durante l'indicizzazione. Per ulteriori informazioni, consultare Configurare e gestire parole irrilevanti ed elenchi di parole irrilevanti per la ricerca full-text.

• Il thesaurus

  Per impostazione predefinita, le query FREETEXT e FREETEXTTABLE utilizzano il thesaurus. CONTAINS e CONTAINSTABLE supportano un argomento THESAURUS facoltativo.

• Distinzione tra maiuscole e minuscole

  Per le query di ricerca full-text non viene fatta distinzione tra maiuscole e minuscole. Tuttavia, nella lingua giapponese, ci sono diverse grafie fonetiche in cui la normalizzazione ortografica è assimilabile alla mancanza di distinzione tra maiuscole e minuscole, ad esempio kana = senza distinzione. Questo tipo di normalizzazione ortografica non è supportato.

Esecuzione di query su varbinary(max) e colonne xml

Se una colonna varbinary(max), varbinary o xml è indicizzata full-text, si possono eseguire query utilizzando i predicati full-text (CONTAINS e FREETEXT) e le funzioni (CONTAINSTABLE e FREETEXTTABLE), come su qualsiasi altra colonna indicizzata full-text.

i dati di tipo varbinary(max) o varbinary

Una singola varbinary(max) colonna o varbinary può archiviare molti tipi di documenti. SQL Server supporta qualsiasi tipo di documento per cui è installato un filtro disponibile nel sistema operativo. Il tipo di ogni documento è identificato dall'estensione file relativa. Per un'estensione file doc, ad esempio, la ricerca full-text utilizza il filtro che supporta i documenti di Microsoft Word. Per un elenco dei tipi di documento disponibili, eseguire una query sulla vista del catalogo sys.fulltext_document_types .

Si noti che il motore di Full-Text può sfruttare i filtri esistenti installati nel sistema operativo. Prima di poter utilizzare i filtri, i word breaker e gli stemmer del sistema operativo, è necessario caricarli nell'istanza del server, come illustrato di seguito:

EXEC sp_fulltext_service @action='load_os_resources', @value=1  

Per creare un indice full-text in una varbinary(max) colonna, il motore di Full-Text deve accedere alle estensioni di file dei documenti nella varbinary(max) colonna. Queste informazioni devono essere archiviate in una colonna di tabella, denominata colonna di tipo, che deve essere associata alla varbinary(max) colonna nell'indice full-text. Quando si esegue l'indicizzazione di un documento, il motore di ricerca full-text utilizza l'estensione del file nella colonna del tipo per identificare il filtro da utilizzare.

dati XML

Una xml colonna di tipo di dati archivia solo documenti e frammenti XML e solo il filtro XML viene utilizzato per i documenti. Una colonna del tipo non è pertanto necessaria. Nelle xml colonne l'indice full-text indicizza il contenuto degli elementi XML, ma ignora il markup XML. Ai valori di attributo viene applicata l'indicizzazione full-text a meno che non siano valori numerici. I tag degli elementi vengono utilizzati come limiti dei token. Sono supportati documenti e frammenti XML o HTML ben formati e contenenti più lingue.

Per altre informazioni sull'esecuzione di query su una xml colonna, vedere Usare Full-Text Ricerca con colonne XML.

Formati supportati di termini di query

Questa sezione riepiloga il supporto fornito per ogni forma di query in base ai predicati full-text e alle funzioni con valori di set di righe.

Ricerca di parole o frasi specifiche (termine semplice)

È possibile usare CONTAINS, CONTAINSTABLE, FREETEXT o FREETEXTTABLE per cercare una frase specifica in una tabella. Ad esempio, se si vuole cercare la ProductReview tabella nel database AdventureWorks2012 per trovare tutti i commenti relativi a un prodotto con la frase "curva di apprendimento", è possibile usare il predicato CONTAINS come indicato di seguito:

USE AdventureWorks2012  
GO  
  
SELECT Comments  
FROM Production.ProductReview  
WHERE CONTAINS(Comments, '"learning curve"')  
GO  

La condizione di ricerca, in questo caso "curva di apprendimento", può essere piuttosto complessa e può essere composta da uno o più termini

Esecuzione di ricerche con prefisso (termine prefisso)

È possibile usare CONTAINS o CONTAINSTABLE per cercare parole o frasi con un prefisso specificato. Vengono restituite tutte le voci nella colonna che contengono testo che inizia con il prefisso specificato. È possibile cercare, ad esempio, tutte le righe che contengono il prefisso top-, come in top``ple, top``pinge top. La query è simile alla seguente:

USE AdventureWorks2012  
GO  
  
SELECT Description, ProductDescriptionID  
FROM Production.ProductDescription  
WHERE CONTAINS (Description, '"top*"' )  
GO  

La query restituisce tutto il testo che corrisponde a quello specificato prima dell'asterisco (*). Se il testo e l'asterisco non sono delimitati da virgolette doppie, come in CONTAINS (DESCRIPTION, 'top*'), la ricerca nel testo completo non considera l'asterisco un carattere jolly.

Se il termine di prefisso è una frase, ogni token che la compone viene considerato come un termine di prefisso distinto e vengono restituite tutte le righe contenenti parole che iniziano con tali prefissi. Ad esempio, il termine con prefisso "light bread*" troverà righe con il testo "light breaded," "lightly breaded" o "light bread", ma non troverà "pane leggermente tostato".

Ricerca di forme flessive di una parola specifica (termine generato)

È possibile usare CONTAINS, CONTAINSTABLE, FREETEXTo FREETEXTTABLE per cercare tutti i diversi tempi e coniugazioni di un verbo o le forme sia singolare sia plurale di un sostantivo (ricerca di forme flessive) oppure i sinonimi di una parola specifica (ricerca thesaurus).

Nel seguente esempio si cerca qualsiasi forma di "foot" ("foot", "feet" e così via) nella colonna Comments della tabella ProductReview nel database AdventureWorks.

USE AdventureWorks2012  
GO  
  
SELECT Comments, ReviewerName  
FROM Production.ProductReview  
WHERE CONTAINS (Comments, 'FORMSOF(INFLECTIONAL, "foot")')  
GO  

Ricerca di parole o frasi usando valori ponderati (termine ponderato)

È possibile usare CONTAINSTABLE per cercare parole o frasi e specificare un valore ponderato. Il peso, espresso come numero compreso tra 0,0 e 1,0, indica l'importanza di ogni parola o frase all'interno di un set di parole o frasi. Il valore 0,0 corrisponde al peso minimo, mentre il valore 1,0 corrisponde al peso massimo.

L'esempio seguente mostra una query che cerca tutti gli indirizzi dei clienti, utilizzando pesi di rilevanza, in cui qualsiasi frase che inizia con la stringa "Bay" contiene "Street" o "View". I risultati assegnano un rango più alto a quelle righe che contengono più parole specificate.

USE AdventureWorks2012  
GO  
  
SELECT AddressLine1, KEY_TBL.RANK   
FROM Person.Address AS Address INNER JOIN  
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",   
         Street WEIGHT(0.9),   
         View WEIGHT(0.1)  
         ) ' ) AS KEY_TBL  
ON Address.AddressID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC  
GO  

È possibile utilizzare un termine ponderato insieme a qualsiasi termine semplice, termine di prefisso, termine di generazione o termine di prossimità.

Visualizzazione del risultato della tokenizzazione di una combinazione di word breaker, thesaurus e stoplist

Dopo aver applicato una determinata combinazione di separatore di parole, thesaurus e elenco di parole non significative a un input di stringa di query, è possibile usare la vista di gestione dinamica sys.dm_fts_parser per visualizzare il risultato della tokenizzazione. Per altre informazioni, vedi sys.dm_fts_parser (Transact-SQL).

Vedere anche

CONTAINS (Transact-SQL)
CONTAINSTABLE (Transact-SQL)
FREETEXT (Transact-SQL)
FREETEXTTABLE (Transact-SQL)
Creare le query di ricerca Full-Text (Visual Database Tools)
Migliorare le prestazioni delle query full-text