Comparteix a través de

CONTAINSTABLE (Transact-SQL)

  • Article

Se aplica a: SQL ServerAzure SQL Database Azure SQL Instancia administrada

Devuelve una tabla con una o más filas para aquellas columnas que contengan coincidencias exactas o aproximadas (menos precisas) con palabras simples o frases, palabras próximas a otra dada (dentro de una cierta distancia) o bien coincidencias ponderadas. CONTAINSTABLE se usa en la cláusula FROM de una instrucción SELECT de Transact-SQL y se hace referencia a ella como si fuera un nombre de tabla normal. Realiza una búsqueda de texto completo de SQL Server en columnas indexadas de texto completo que contienen tipos de datos basados en caracteres.

CONTAINSTABLE es útil para los mismos tipos de coincidencias que el predicado CONTAINS y usa las mismas condiciones de búsqueda que CONTAINS.

A diferencia de CONTAINS, las consultas que usan CONTAINSTABLE devuelven un valor de clasificación por relevancia (RANK) y un valor de clave de texto completo (KEY) por cada fila. Para obtener información sobre las formas de búsqueda de texto completo que se admiten en SQL Server, vea Consulta con búsqueda de texto completo.

Convenciones de sintaxis de Transact-SQL

Sintaxis

  
CONTAINSTABLE   
( table , { column_name | ( column_list ) | * } , ' <contains_search_condition> '   
     [ , LANGUAGE language_term]   
  [ , top_n_by_rank ]   
)   
  
<contains_search_condition> ::=   
    { <simple_term>   
    | <prefix_term>   
    | <generation_term>   
    | <generic_proximity_term>   
    | <custom_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 ] )   
  
<generic_proximity_term> ::=   
     { <simple_term> | <prefix_term> } { { { NEAR | ~ }   
     { <simple_term> | <prefix_term> } } [ ...n ] }  
  
<custom_proximity_term> ::=   
  NEAR (   
     {  
        { <simple_term> | <prefix_term> } [ ,...n ]  
     |  
        ( { <simple_term> | <prefix_term> } [ ,...n ] )   
      [, <maximum_distance> [, <match_order> ] ]  
     }  
       )   
  
      <maximum_distance> ::= { integer | MAX }  
      <match_order> ::= { TRUE | FALSE }   
  
<weighted_term> ::=   
     ISABOUT  
    ( { {   
  <simple_term>   
  | <prefix_term>   
  | <generation_term>   
  | <proximity_term>   
  }   
   [ WEIGHT ( weight_value ) ]   
   } [ ,...n ]   
    )

Argumentos

table
Es el nombre de una tabla que se ha sometido a una indización de texto completo. table puede ser un nombre de objeto de base de datos de uno, dos, tres o cuatro partes. Al consultar una vista, puede incluirse solo una tabla base indizada de texto completo.

table no puede especificar un nombre de servidor y no se puede usar en consultas en servidores vinculados.

column_name
Es el nombre de una o más columnas indizadas para la búsqueda de texto completo. Las columnas pueden ser de tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary o varbinary(max) .

lista_de_columnas
Indica que se pueden especificar varias columnas, separadas por una coma. column_list debe ir entre paréntesis. A menos que se especifique language_term, el idioma de todas las columnas de column_list debe ser el mismo.

*
Especifica que todas las columnas indizadas de texto completo de la tabla deben usarse para buscar la condición de búsqueda dada. A menos que se especifique language_term, el idioma de todas las columnas de la tabla debe ser el mismo.

LANGUAGE language_term
Es el idioma cuyos recursos se usarán para la eliminación de palabras de separación, lematización y diccionario de sinónimos y palabra irrelevante (o palabra irrelevante) como parte de la consulta. Este parámetro es opcional y puede especificarse como un valor hexadecimal, un entero o una cadena correspondiente al identificador de configuración regional (LCID) de un idioma. Si se especifica language_term, el idioma que representa se aplicará a todos los elementos de la condición de búsqueda. Si no se especifica ningún valor, se utiliza el idioma de texto completo de la columna.

Si se almacenan juntos documentos de idiomas diferentes como objetos binarios grandes (BLOB) en una sola columna, el identificador de configuración regional (LCID) de un documento determinado determina qué idioma se usa para indizar su contenido. Al consultar este tipo de columna, especificar LANGUAGE**language_term puede aumentar la probabilidad de encontrar una coincidencia acertada.

Cuando se especifica como una cadena, language_term corresponde al valor de la columna de alias en la vista de compatibilidad sys.syslanguages. La cadena debe estar delimitada con comillas sencillas, como en 'language_term'. Cuando se especifica como un entero, language_term es el LCID real que identifica el idioma. Cuando se especifica como un valor hexadecimal, language_term es 0x seguido del valor hexadecimal del LCID. El valor hexadecimal no puede superar los ocho dígitos, incluidos los ceros a la izquierda.

Si el valor está en formato DBCS (juego de caracteres de doble byte), Microsoft SQL Server lo convertirá a Unicode.

Si el idioma especificado no es válido o no hay recursos instalados que se correspondan con dicho idioma, SQL Server devuelve un error. Para usar recursos de idioma neutro, especifique 0x0 como language_term.

top_n_by_rank
Especifica que solo se devuelven las n coincidencias de clasificación más alta, en orden descendente. Solo se aplica cuando se especifica un valor entero, n. Si se combina top_n_by_rank con otros parámetros, es posible que la consulta devuelva menos filas de las que en realidad coinciden con todos los predicados. top_n_by_rank permite aumentar el rendimiento de las consultas recuperando solo los aciertos más relevantes.

<contains_search_condition>
Especifica el texto que se va a buscar en column_name y las condiciones para obtener coincidencias. Para obtener información sobre las condiciones de búsqueda, vea CONTAINS (Transact-SQL).

Comentarios

Los predicados y las funciones de texto completo operan en una única tabla, que se obtiene del predicado FROM. Para buscar en varias tablas, utilice una tabla combinada en la cláusula FROM a fin de buscar en un conjunto de resultados que sea el producto de dos o más tablas.

La tabla devuelta tiene una columna denominada KEY que contiene valores de clave de texto completo. Cada tabla indizada de texto completo tiene una columna cuyos valores se garantiza que sean únicos y los valores devueltos en la columna KEY son los valores de clave de texto completo de las filas que coinciden con los criterios de selección especificados en la condición de búsqueda. La propiedad TableFulltextKeyColumn , obtenida de la función OBJECTPROPERTYEX, proporciona la identidad de esta columna de clave única. Para obtener el identificador de la columna asociada a la clave de texto completo del índice de texto completo, use sys.fulltext_indexes. Para obtener más información, consulte sys.fulltext_indexes (Transact-SQL).

Para obtener las filas que desee de la tabla original, especifique una combinación con las filas de CONTAINSTABLE. La forma típica de la cláusula FROM de una instrucción SELECT que utiliza CONTAINSTABLE es la siguiente:

SELECT select_list  
FROM table AS FT_TBL INNER JOIN  
   CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL  
   ON FT_TBL.unique_key_column = KEY_TBL.[KEY];

La tabla generada por CONTAINSTABLE incluye una columna denominada RANK. La columna RANK es un valor (de 0 a 1000) para cada fila que indica cómo una fila coincide con los criterios de selección. Este valor de clasificación suele utilizarse en las instrucciones SELECT de una de estas maneras:

  • En la cláusula ORDER BY, para devolver las filas de mayor valor al principio de la tabla.

  • En la lista de selección, para ver el valor de clasificación asignado a cada fila.

Permisos

Solamente disponen de permisos de ejecución los usuarios que tienen los permisos SELECT adecuados en la tabla o en las columnas de la tabla a las que se hace referencia.

Ejemplos

A. Ejemplo sencillo

En el ejemplo siguiente se crea y rellena una tabla simple de dos columnas, que enumera 3 condados y los colores de sus marcas. crea y rellena un catálogo de texto completo y un índice en la tabla. A continuación, se muestra la sintaxis CONTAINSTABLE . En este ejemplo se muestra cómo aumenta el valor de clasificación cuando se cumple el valor de búsqueda varias veces. En la última consulta, Tanzania que contiene tanto verde como negro tiene una clasificación más alta que Italia que solo contiene uno de los colores consultados.

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));  
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);  
INSERT Flags VALUES ('France', 'Blue and White and Red');  
INSERT Flags VALUES ('Italy', 'Green and White and Red');  
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');  
SELECT * FROM Flags;  
GO  
  
CREATE FULLTEXT CATALOG TestFTCat;  
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;  
GO   
  
SELECT * FROM Flags;  
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;  
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;

B. Devolver valores de clasificación

En el ejemplo siguiente se buscan todos los nombres de producto que contienen las palabras “frame”, “wheel” o “tire” y se asignan ponderaciones distintas a cada palabra. Por cada fila devuelta que cumpla estos criterios de búsqueda, se muestra la precisión relativa (el valor de clasificación) de la coincidencia. Además, las filas cuyo valor de clasificación es más alto se devuelven primero.

USE AdventureWorks2022;  
GO  
  
SELECT FT_TBL.Name, KEY_TBL.RANK  
    FROM Production.Product AS FT_TBL   
        INNER JOIN CONTAINSTABLE(Production.Product, Name,   
        'ISABOUT (frame WEIGHT (.8),   
        wheel WEIGHT (.4), tire WEIGHT (.2) )' ) AS KEY_TBL  
            ON FT_TBL.ProductID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC;  
GO

C. Devolver valores de clasificación más altos que un valor especificado
Válido para : SQL Server 2012 (11.x) y versiones posteriores.

En el ejemplo siguiente se usa NEAR para buscar “bracket” y “reflector” cercanos entre sí en la tabla Production.Document. Solo se devuelven filas con un valor de clasificación de 50 o superior.

USE AdventureWorks2022  
GO  
  
SELECT DocumentNode, Title, DocumentSummary  
FROM Production.Document AS DocTable   
INNER JOIN CONTAINSTABLE(Production.Document, Document,  
  'NEAR(bracket, reflector)' ) AS KEY_TBL  
  ON DocTable.DocumentNode = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK > 50  
ORDER BY KEY_TBL.RANK DESC;  
GO

Nota:

Si en una consulta de texto completo no se especifica un entero como la distancia máxima, un documento que contiene solo los aciertos cuyo intervalo es mayor que 100 términos lógicos no cumplirá los requisitos NEAR, y la clasificación será 0.

D. Devolver los 5 mejores resultados utilizando top_n_by_rank

En el ejemplo siguiente se devuelve la descripción de los 5 productos más destacados, donde la columna Description contiene la palabra “aluminum” cerca de la palabra “light” o de la palabra “lightweight”.

USE AdventureWorks2022;  
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)',  
      5  
   ) AS KEY_TBL  
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY];  
GO

GO

E. Especificar el argumento LANGUAGE

En el siguiente ejemplo se muestra la forma de utilizar el argumento LANGUAGE.

USE AdventureWorks2022;  
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)',  
      LANGUAGE N'English',  
      5  
   ) AS KEY_TBL  
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY];  
GO

Nota:

Los argumentos language language_term no son necesarios para usar top_n_by_rank.

Consulte también

Limitar los resultados de la búsqueda con RANK
Consultar con búsqueda de texto completo
Crear consultas de búsqueda de texto completo (Visual Database Tools)
CONTAINS (Transact-SQL)
Consultar con búsqueda de texto completo
SELECT (Transact-SQL)
FROM (Transact-SQL)