Información general de funciones y predicados de texto completo
Las consultas de texto completo utilizan funciones (CONTAINSTABLE y FREETEXTTABLE) y predicados (CONTAINS y FREETEXT) de texto completo. Estas funciones y predicados admiten la sintaxis Transact-SQL enriquecida, que es compatible con diversos formatos de condiciones de consulta. Para escribir consultas de texto completo, es necesario saber cuándo y cómo deben utilizarse estos predicados y funciones. En este tema se resumen los predicados y las funciones y se explican los puntos en común que existen entre el predicado CONTAINS y la función CONTAINSTABLE.
Información general de los predicados de texto completo (CONTAINS y FREETEXT)
CONTAINS y FREETEXT se especifican en la cláusula WHERE o HAVING de una instrucción SELECT. Pueden combinarse con cualquier otro predicado Transact-SQL, como LIKE y BETWEEN.
Los predicados de CONTAINS y FREETEXT devuelven un valor TRUE o FALSE. Se pueden utilizar exclusivamente para especificar los criterios de selección a la hora de determinar si una fila determinada coincide con la consulta de texto completo. Las filas que coinciden se devuelven en el conjunto de resultados.
Al utilizar CONTAINS o FREETEXT, se puede especificar una única columna, una lista de columnas o todas las columnas de la tabla que se van a buscar. También se puede especificar el lenguaje cuyos recursos se utilizarán en la consulta de texto completo para la lematización y separación de palabras, las consultas del diccionario de sinónimos y la eliminación de palabras irrelevantes.
CONTAINS y FREETEXT son útiles para diferentes tipos de coincidencias, tal y como se detalla a continuación:
Utilice CONTAINS (o CONTAINSTABLE) para obtener coincidencias precisas o aproximadas (menos precisas) de palabras o frases, para obtener la proximidad de las palabras que se encuentran a cierta distancia de otra o para obtener coincidencias ponderadas. Al utilizar CONTAINS, debe establecer al menos una condición de búsqueda que especifique el texto que está buscando y las condiciones que determinan las coincidencias.
Puede utilizar una operación lógica entre las condiciones de búsqueda. Para obtener más información, vea Utilizar los operadores booleanos AND, OR y AND NOT (en CONTAINS y CONTAINSTABLE), más adelante en este mismo tema.
Utilice FREETEXT (o FREETEXTTABLE) para buscar coincidencias de significado en lugar de coincidencias literales con las palabras, frases o sintagmas especificados (cadena Freetext). Se generarán coincidencias si se encuentra algún término o las formas de algún término en el índice de texto completo de una columna especificada.
Para ejecutar consultas en servidores vinculados, puede utilizar nombres de cuatro partes en CONTAINS o FREETEXT. Para obtener más información, vea Consultar servidores vinculados (búsqueda de texto completo).
Para obtener más información sobre la sintaxis y los argumentos de estos predicados, vea CONTAINS (Transact-SQL) y FREETEXT (Transact-SQL).
[!NOTA]
Los predicados de texto completo no están permitidos en la cláusula OUTPUT cuando el nivel de compatibilidad de la base de datos está establecido en 100.
Ejemplos
A. Usar CONTAINS con <simple_term>
En este ejemplo se buscan todos los productos con un precio de $80.99 que contengan la palabra "Mountain".
USE AdventureWorks;
GO
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice = 80.99
AND CONTAINS(Name, 'Mountain');
GO
B. Usar FREETEXT para buscar palabras que contengan los valores de carácter especificados
En el siguiente ejemplo se buscan todos los documentos que contienen las palabras relacionadas con vital, safety, components.
USE AdventureWorks;
GO
SELECT Title
FROM Production.Document
WHERE FREETEXT (Document, 'vital safety components' );
GO
Información general de las funciones de texto completo (CONTAINSTABLE y FREETEXTTABLE)
Las referencias a las funciones CONTAINSTABLE y FREETEXTTABLE se establecen como un nombre de tabla normal en la cláusula FROM de una instrucción SELECT. Devuelven una tabla con ninguna, una o varias filas que coinciden con la consulta de texto completo. La tabla devuelta sólo contiene las filas de la tabla base que coinciden con los criterios de selección especificados en la condición de búsqueda de texto completo de la función.
Las consultas que utilizan una de estas funciones devuelven un valor de clasificación por relevancia (RANK) y una clave de texto completo (KEY) para cada fila, tal y como se muestra a continuación:
Columna KEY
La columna KEY devuelve valores únicos de las filas devueltas. La columna KEY se puede utilizar para especificar los criterios de selección.
Columna RANK
La columna RANK contiene un valor de clasificación para cada fila que indica el grado de coincidencia de la fila con los criterios de selección. Cuanto mayor sea el valor de clasificación del texto o documento de una fila, mayor importancia tendrá la fila en la consulta de texto completo especificada. Tenga en cuenta que puede haber filas distintas con la misma clasificación. Puede limitar el número de coincidencias que se van a devolver con el parámetro opcional top_n_by_rank. Para obtener más información, vea Limitar conjuntos de resultados clasificados (búsqueda de texto completo) y Cómo se clasifican los resultados de la consulta de búsqueda (búsqueda de texto completo).
Cuando utilice una de estas funciones, debe especificar la tabla base en la que se realizará la búsqueda de texto completo. Al igual que en los predicados, puede especificar una única columna, una lista de columnas o todas las columnas de la tabla en la que se va a buscar y, de forma opcional, el lenguaje cuyos recursos se utilizarán en la consulta de texto completo.
CONTAINSTABLE es útil para los mismos tipos de coincidencias que CONTAINS, mientras que FREETEXTTABLE es útil para los mismos tipos de coincidencias que FREETEXT. Para obtener más información, vea Información general de los predicados de texto completo (CONTAINS y FREETEXT), anteriormente en este tema. Cuando ejecute consultas que utilicen las funciones CONTAINSTABLE y FREETEXTTABLE, deberá unir explícitamente las filas devueltas con las filas de la tabla base de SQL Server.
Para obtener más información sobre la sintaxis y los argumentos de estas funciones, vea CONTAINSTABLE (Transact-SQL) y FREETEXTTABLE (Transact-SQL).
Ejemplos
A. Utilizar CONTAINSTABLE
En el siguiente ejemplo se devuelve la descripción y el nombre de categoría de todas las categorías de alimentos cuya columna Description contenga las palabras "sweet and savory" cerca de la palabra "sauces" o de la palabra "candies". Todas las filas cuyo nombre de categoría sea "Seafood" no se devuelven. Sólo se devuelven las filas cuyo valor de distancia sea igual o superior a 2.
USE Northwind;
GO
SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
AND FT_TBL.CategoryName <> 'Seafood'
ORDER BY KEY_TBL.RANK DESC;
GO
B. Utilizar FREETEXTTABLE
En el ejemplo siguiente se amplía una consulta FREETEXTTABLE para que devuelva primero las filas con clasificación superior y agregue la clasificación de cada fila a la lista de selección. Para especificar la consulta, debe saber que CategoryID es la columna de clave única de la tabla Categories.
USE Northwind;
GO
SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories AS FT_TBL
INNER JOIN
FREETEXTTABLE(Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
GO
A continuación se incluye una ampliación de la misma consulta en la que sólo se devuelven las filas con un valor de distancia de 10 o superior:
USE Northwind;
GO
SELECT KEY_TBL.RANK, FT_TBL.Description
FROM Categories FT_TBL
INNER JOIN
FREETEXTTABLE (Categories, Description,
'How can I make my own beers and ales?') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK >= 10
ORDER BY KEY_TBL.RANK DESC;
GO
Utilizar los operadores booleanos AND, OR y AND NOT (en CONTAINS y CONTAINSTABLE)
El predicado CONTAINS y la función CONTAINSTABLE utilizan las mismas condiciones de búsqueda. Los dos admiten que se combinen varias condiciones de búsqueda con los operadores booleanos AND, OR y AND NOT para realizar operaciones lógicas. Por ejemplo, puede utilizar AND para buscar filas que contienen "latte" y "New York-style bagel". También puede, por ejemplo, utilizar AND NOT para buscar las filas que contienen "bagel" pero no contienen "cream cheese".
[!NOTA]
Por el contrario, FREETEXT y FREETEXTTABLE tratan los términos booleanos como las palabras que se van a buscar.
Para obtener información sobre la combinación de CONTAINS con otros predicados que utilizan los operadores lógicos AND, OR y NOT, vea Condiciones de búsqueda (Transact-SQL).
Ejemplo
En el ejemplo siguiente se utiliza la tabla ProductDescription de la base de datos AdventureWorks. La consulta utiliza el predicado CONTAINS para buscar las descripciones en las que el identificador de la descripción no es igual a 5 y la descripción contiene las palabras "Aluminum" y "spindle". La condición de búsqueda utiliza el operador booleano AND.
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');
GO
Consideraciones adicionales
Todas las tablas habilitadas para texto completo tienen una columna que se utiliza para aplicar las filas únicas de la tabla (columna de clave única). Cuando se escribe el tipo CONTAINSTABLE o FREETEXTTABLE de las consultas de texto completo, necesita el nombre de la columna de clave única. Para obtener más información, vea Cómo consultar sobre la columna de clave de texto completo (Transact-SQL).
Muchas condiciones de consulta dependen en gran medida del comportamiento del separador de palabras. Para asegurarse de que usa el archivo del separador de palabras (y lematizador) y del diccionario de sinónimos correcto, se recomienda especificar el argumento LANGUAGE. Para obtener más información, vea Prácticas recomendadas para elegir un idioma al crear un índice de texto completo.
Al definir una consulta de texto completo, el motor de texto completo descarta las palabras irrelevantes de los criterios de búsqueda. Las palabras irrelevantes son aquellas como "a", "and", "is" o "the", que suelen aparecer con frecuencia pero que normalmente no ayudan en la búsqueda de un texto determinado. Las palabras irrelevantes se muestran en una lista de palabras irrelevantes. Cada índice de texto completo está asociado a una lista de palabras irrelevantes concreta, que determina qué palabras irrelevantes se omiten de la consulta o del índice en el momento de la indización. Para obtener más información, vea Palabras irrelevantes y listas de palabras irrelevantes.
La comprobación de coincidencias con el diccionario de sinónimos sólo tiene lugar con las consultas Transact-SQL CONTAINS y CONTAINSTABLE que especifican la cláusula FORMSOF THESAURUS y con las consultas FREETEXT y FREETEXTABLE de forma predeterminada.
Vea también