Consultar con búsqueda de texto completo

Para definir búsquedas de texto completo, las consultas de texto completo de SQL Server utilizan los predicados (CONTAINS y FREETEXT) y las funciones (CONTAINSTABLE y FREETEXTTABLE) de texto completo. Estas funciones y predicados admiten la sintaxis Transact-SQL enriquecida, que es compatible con diversos formatos de términos de consulta. Para escribir consultas de texto completo, es necesario saber cuándo y cómo deben usarse estos predicados y funciones.

En este tema

• Información general de los predicados de texto completo (CONTAINS y FREETEXT)

• Información general de las funciones de texto completo (CONTAINSTABLE y FREETEXTTABLE)

• Usar los operadores booleanos AND, OR y AND NOT (en CONTAINS y CONTAINSTABLE)

• Consideraciones adicionales para consultas de texto completo

• Consultar columnas varbinary(max) y xml

• Formatos admitidos de términos de consulta

• Ver el resultado de la tokenización de una combinación entre un separador de palabras, un diccionario de sinónimos y una lista de palabras irrelevantes

Información general de los predicados de texto completo (CONTAINS y FREETEXT)

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. 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.

[!NOTA]

Para obtener más información sobre la sintaxis y los argumentos de estos predicados, vea CONTAINS (Transact-SQL) y FREETEXT (Transact-SQL).

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.

Se puede utilizar un nombre de cuatro partes en el predicado CONTAINS o FREETEXT para consultar columnas indizadas de texto completo de las tablas de destino en un servidor vinculado. Para preparar un servidor remoto para recibir consultas de texto completo, es necesario crear un índice de texto completo en las tablas y columnas de destino en el servidor remoto y, posteriormente, agregar el servidor remoto como un servidor vinculado.

[!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.

[ARRIBA]

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 AdventureWorks2012
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 AdventureWorks2012
GO

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

[ARRIBA]

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 solo 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.

[!NOTA]

Para obtener más información sobre la sintaxis y los argumentos de estas funciones, vea CONTAINSTABLE (Transact-SQL) y FREETEXTTABLE (Transact-SQL).

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 los resultados de la búsqueda con RANK.

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 se ejecutan consultas que utilizan las funciones CONTAINSTABLE y FREETEXTTABLE, se deben combinar de forma explícita las filas que se devuelven con las filas de la tabla base de SQL Server.

Normalmente, es necesario combinar el resultado de CONTAINSTABLE o FREETEXTTABLE con la tabla base. En esos casos, necesita conocer el nombre de la columna de clave única. Esta columna, que existe en todas las tablas habilitadas para texto completo, se usa para aplicar las filas únicas de la tabla (columna de clave única). Para obtener más información, vea Administrar índices de texto completo.

[ARRIBA]

Ejemplos

A.Usar CONTAINSTABLE

En el ejemplo siguiente se devuelven el identificador de descripción y la descripción de todos los productos para los que la columna Description contiene la palabra "aluminum" cerca de la palabra "light" o de la palabra "lightweight". Solo se devuelven las filas cuyo valor de rango sea igual o superior a 2.

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.Usar 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 ProductDescriptionID es la columna de clave única de la tabla ProductDescription.

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

A continuación se incluye una ampliación de la misma consulta en la que solo se devuelven las filas con un valor de rango de 10 o superior:

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

[ARRIBA]

Usar los operadores booleanos AND, OR y AND NOT en CONTAINS y CONTAINSTABLE

El predicado CONTAINS y la función CONTAINSTABLE usan 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 siguiente ejemplo se utiliza la tabla ProductDescription de la base de datos AdventureWorks2012 . La consulta usa 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 usa el operador booleano AND.

USE AdventureWorks2012
GO

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

[ARRIBA]

Consideraciones adicionales para consultas de texto completo

Al escribir consultas de texto completo hay que tener en cuenta también lo siguiente:

• La opción LANGUAGE

  Muchos términos 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 la opción LANGUAGE. Para obtener más información, vea Elegir un idioma al crear un índice de texto completo.

• Palabras irrelevantes

  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 Configurar y administrar palabras irrelevantes y listas de palabras irrelevantes para la búsqueda de texto completo.

• El diccionario de sinónimos

  Las consultas FREETEXT y FREETEXTTABLE usan de forma predeterminada el diccionario de sinónimos. CONTAINS y CONTAINSTABLE admiten un argumento THESAURUS opcional.

• Distinción de mayúsculas y minúsculas

  Las consultas de búsqueda de texto completo no distinguen mayúsculas de minúsculas. Sin embargo, en japonés, hay muchas ortografías fonéticas en las que el concepto de normalización ortográfica implica no distinguir las mayúsculas de las minúsculas (por ejemplo, las letras kana no tienen mayúsculas y minúsculas). Este tipo de normalización ortográfica no se admite.

[ARRIBA]

Consultar columnas varbinary(max) y xml

Si una columna varbinary(max), varbinary o xml está indizada con texto completo, se puede consultar usando los predicados de texto completo (CONTAINS y FREETEXT) y las funciones (CONTAINSTABLE y FREETEXTTABLE), igual que cualquier otra columna indizada de texto completo.

Importante
La búsqueda de texto completo también funciona con las columnas de imagen. Sin embargo, el tipo de datos image se quitará en una versión futura de SQL Server. Evite utilizar este tipo de datos en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente lo utilizan. En su lugar, use el tipo de datos varbinary(max).

datos varbinary(máximo) o varbinary

Una sola columna varbinary(max) o varbinary puede almacenar muchos tipos de documentos. SQL Server admite cualquier tipo de documento para el que tenga un filtro instalado que esté disponible en el sistema operativo. La extensión de archivo del documento identifica el tipo de cada documento. Por ejemplo, para la extensión de archivo .doc, la búsqueda de texto completo utiliza el filtro que admite los documentos de Microsoft Word. Para obtener una lista de estos tipos de documento, consulte la vista de catálogo sys.fulltext_document_types.

Observe que el motor de búsqueda de texto completo puede aprovechar los filtros existentes que se instalan en el sistema operativo. Para poder utilizar los separadores de palabras, los lematizadores y los filtros del sistema operativo, debe cargarlos en la instancia del servidor, como sigue:

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

Para crear un índice de texto completo en una columna varbinary(max), el motor de búsqueda de texto completo necesita acceso a las extensiones de archivo de los documentos en la columna varbinary(max). Esta información debe estar almacenada en una columna de la tabla, denominada columna de tipo, que debe estar asociada a la columna varbinary(max) en el índice de texto completo. Al indizar un documento, el motor de búsqueda de texto completo usa la extensión de archivo de la columna de tipo para identificar qué filtro usar.

[ARRIBA]

Datos xml

Una columna del tipo de datos xml solo almacena los documentos y fragmentos XML, y solo se usa el filtro XML para los documentos. Por consiguiente, una columna de tipo es innecesaria. En las columnas xml, el índice de texto completo indiza el contenido de los elementos XML, pero omite el formato XML. Los valores de los atributos se incluyen en el índice de texto completo a menos que sean valores numéricos. Las etiquetas de elemento se utilizan como límites de token. Se admiten fragmentos y documentos con formato XML o HTML correcto que contengan varios idiomas.

Para obtener más información sobre cómo consultar con una columna xml, vea Usar la búsqueda de texto completo con columnas XML.

[ARRIBA]

Formatos admitidos de términos de consulta

En esta sección se resume la compatibilidad que proporcionan los predicados de texto completo y las funciones de valores de conjunto de filas para cada formato de consulta.

[!NOTA]

Para obtener la sintaxis de un término de consulta dado, haga clic en los vínculos correspondientes de la columna Es compatible con de la siguiente tabla.

[ARRIBA]

Buscar palabras o frases específicas (término simple)

Puede usar CONTAINS, CONTAINSTABLE, FREETEXT o FREETEXTTABLE para buscar una frase específica en una tabla. Por ejemplo, si desea buscar en la tabla ProductReview de la base de datos AdventureWorks2012 todos los comentarios acerca de un producto que contengan la frase "learning curve", puede utilizar el predicado CONTAINS del siguiente modo:

USE AdventureWorks2012
GO

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

La condición de búsqueda, en este caso "learning curve", puede ser bastante compleja y estar formada por uno o varios términos.

[ARRIBA]

Realizar búsquedas de prefijos (término de prefijo)

Puede usar CONTAINS o CONTAINSTABLE para buscar palabras o frases con un prefijo especificado. Se devuelven todas las entradas de la columna que contenga el texto que comience por el prefijo especificado. Por ejemplo, para buscar todas las filas que contenga el prefijo top-, como en topple, topping y top. La consulta sería la siguiente:

USE AdventureWorks2012
GO

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

Se devuelve todo el texto que coincida con el texto especificado antes del asterisco (*). Si no se delimitan el texto completo y el asterisco con comillas dobles, como en CONTAINS (DESCRIPTION, 'top*'), en la búsqueda de texto completo no se considerará que el asterisco es un comodín.

Cuando el término prefijo es una frase, cada token de los que componen la frase se considera como un término prefijo separado. Se devuelven todas las filas que contienen palabras que empiezan con los términos prefijos. Por ejemplo, el término de prefijo "light bread*" buscará filas que contengan el texto "light breaded", "lightly breaded" o "light bread", pero no devolverá "lightly toasted bread".

[ARRIBA]

Buscar las formas con inflexión de una palabra específica (término de generación)

Puede usar CONTAINS, CONTAINSTABLE, FREETEXT o FREETEXTTABLE para buscar todos los tiempos y conjugaciones de un verbo o las formas tanto singular como plural de un nombre (búsqueda con inflexión). O bien, los sinónimos de una palabra especificada (búsqueda de diccionario de sinónimos).

En el siguiente ejemplo se busca cualquier forma de "foot" ("foot", "feet", etc.) en la columna Comments de la tabla ProductReview de la base de datos AdventureWorks.

USE AdventureWorks2012
GO

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

[!NOTA]

La búsqueda de texto completo usa lematizadores, que permiten buscar los distintos tiempos y conjugaciones de un verbo o las formas tanto singular como plural de un nombre. Para obtener más información sobre los lematizadores, vea Configurar y administrar separadores de palabras y lematizadores para la búsqueda.

[ARRIBA]

Buscar palabras o frases con valores ponderados (término ponderado)

Puede usar CONTAINSTABLE para buscar palabras o frases, y especificar un valor de ponderación. La ponderación, medida como número comprendido entre 0,0 y 1,0, indica la importancia de cada palabra y frase en un conjunto de palabras y frases. Una ponderación de 0,0 es la más baja y una ponderación de 1,0, la más alta.

En el ejemplo siguiente se muestra una consulta que busca las direcciones de todos los clientes, mediante valores ponderados, en las que cualquier texto que comience por la cadena "Bay" contenga "Street" o "View". Los resultados asignan un rango superior a las filas que contengan más palabras de las especificadas.

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

Se puede usar un término ponderado junto con cualquier término simple, de prefijo, de generación o de proximidad.

[ARRIBA]

Ver el resultado de la tokenización de una combinación entre un separador de palabras, un diccionario de sinónimos y una lista de palabras irrelevantes

Después de aplicar una combinación de separador de palabras determinado, diccionario de sinónimos y lista de palabras irrelevantes a la entrada de una cadena de consulta, puede ver el resultado de la tokenización mediante la vista de administración dinámica sys.dm_fts_parser. Para obtener más información, vea sys.dm_fts_parser (Transact-SQL).

[ARRIBA]

Vea también

Tasks

Crear consultas de búsqueda de texto completo (Visual Database Tools)

Referencia

CONTAINS (Transact-SQL)

CONTAINSTABLE (Transact-SQL)

FREETEXT (Transact-SQL)

FREETEXTTABLE (Transact-SQL)

Conceptos

Mejorar el rendimiento de las consultas de texto completo