CONTAINS (Transact-SQL)

Es un predicado que se utiliza en una cláusula WHERE para buscar, en columnas que contengan tipos de datos basados en caracteres, coincidencias exactas o aproximadas (menos precisas) con palabras o frases, palabras próximas a otra dada en una cierta distancia, o coincidencias ponderadas.

En SQL Server, puede utilizar nombres de cuatro partes en los predicados de texto completo CONTAINS o FREETEXT para ejecutar consultas en relación con los servidores vinculados.

CONTAINS puede buscar:

• Una palabra o una frase.

• El prefijo de una palabra o una frase.

• Una palabra cerca de otra palabra.

• Una palabra que sea una inflexión de otra (por ejemplo, las palabras controles, controladores, controlando y controlado son inflexiones de control).

• Una palabra que sea un sinónimo de otra mediante un diccionario de sinónimos (por ejemplo, la palabra metal puede tener sinónimos como aluminio y acero).

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

• column_name
  Es el nombre de una columna indizada de texto completo de la tabla especificada en la cláusula FROM. Las columnas pueden ser de tipo char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary o varbinary(max).

• column_list
  Especifica dos o más columnas, separadas por comas. column_list se debe incluir 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 la consulta buscará la condición de búsqueda especificada en todas las columnas indizadas de texto completo de la tabla especificada en la cláusula FROM. Las columnas de la cláusula CONTAINS deben proceder de una tabla única que tenga un índice de texto completo. 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 que se usará para la separación de palabras, la lematización, las expansiones del diccionario de sinónimos y los reemplazos, y la eliminación de palabras irrelevantes) como parte de la consulta. Este parámetro es opcional.

  Si se almacenan juntos documentos de idiomas diferentes como objetos binarios (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.

  language_term se puede especificar como una cadena, un entero o un valor hexadecimal correspondiente al identificador de configuración regional (LCID) de un idioma. Si se especifica language_term, el idioma que representa se aplica 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 el valor especificado es una cadena, language_term corresponde al valor de columna alias de la vista de compatibilidad sys.syslanguages (Transact-SQL). La cadena debe ir entre comillas simples, como en 'language_term'. Si el valor especificado es un entero, language_term es el LCID real que identifica el idioma. Si se especifica como un valor hexadecimal, language_term es 0x seguido del valor hexadecimal del LCID. El valor hexadecimal no puede tener más de ocho dígitos, incluidos los ceros a la izquierda.

  Si el valor está en formato DBCS (juego de caracteres de doble byte), 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 utilizar recursos de idioma neutral, especifique 0x0 como language_term.

• <contains_search_condition>
  Especifica el texto que se va a buscar en column_name y las condiciones para obtener coincidencias.

  <contains_search_condition>nvarchar es de tipo . Se realiza una conversión implícita cuando se usa otro tipo de datos de carácter como entrada. En el siguiente ejemplo, la variable @SearchWord, definida como una variable de tipo varchar(30), provoca una conversión implícita en el predicado CONTAINS.

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

  Como el "examen de parámetros" no funciona con la conversión, use nvarchar para obtener mejor rendimiento. En el ejemplo, declare @SearchWord como nvarchar(30).

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

  También puede usar la sugerencia de consulta OPTIMIZE FOR para los casos en los que se genera un plan poco óptimo.

• word
  Es una cadena de caracteres sin espacios ni signos de puntuación.

• phrase
  Es una o varias palabras con espacios entre cada una de ellas.

  [!NOTA]

  Algunos idiomas, como los de algunas partes de Asia, pueden tener frases que contengan una o varias palabras sin espacios entre ellas.

• <simple_term>
  Especifica una coincidencia para una palabra o frase exactas. Ejemplos de términos simples válidos son "blue berry", blueberry y "Microsoft SQL Server". Las frases tienen que ir entre comillas dobles (""). Las palabras de una frase tienen que aparecer en la columna de la base de datos en el mismo orden que el especificado en <contains_search_condition>. La búsqueda de caracteres en la palabra o la frase no distingue mayúsculas de minúsculas. Las palabras irrelevantes (como un, y, el o la) de las columnas indizadas de texto completo no se almacenan en el índice de texto completo. Si se utiliza una palabra irrelevante en la búsqueda de una sola palabra, SQL Server devuelve un mensaje de error que indica que la consulta contiene solo palabras irrelevantes. SQL Server incluye una lista estándar de palabras irrelevantes en el directorio \Mssql\Binn\FTERef de cada instancia de SQL Server.

  Los signos de puntuación se omiten. Por lo tanto, CONTAINS(testing, "computer failure") coincide con una fila que contiene el valor "Where is my computer? Failure to find it would be expensive". Para obtener más información sobre el comportamiento de los separadores de palabras, vea Separadores de palabras y lematizadores.

• <prefix_term>
  Especifica la coincidencia de palabras o frases que comiencen con el texto especificado. Incluya un término prefijo entre comillas dobles ("") y agregue un asterisco (*) delante de las comillas de cierre, de modo que se haga coincidir con cualquier texto que comience con el término sencillo especificado antes del asterisco. La cláusula debe especificarse de esta manera:CONTAINS (column, '"text*"'). El asterisco representa cero, uno o más caracteres (de la palabra raíz o de las palabras de la palabra o la frase). Si el texto y el asterisco no se delimitan con comillas dobles de modo que el predicado sea CONTAINS (column, 'text*'), la búsqueda de texto completo considera el asterisco un carácter y busca coincidencias exactas con text*. El motor de texto completo no encontrará palabras con el carácter de asterisco (*) porque los separadores de palabras suelen omitir dichos caracteres.

  Cuando <prefix_term> es una frase, todas las palabras de dicha frase se consideran prefijos. Por tanto, una consulta que especifique el prefijo "local wine*" hace que se devuelvan todas las filas que contengan el texto "local winery", "locally wined and dined", etc.

• <generation_term>
  Especifica la coincidencia de palabras cuando los términos simples incluyen variaciones de la palabra original que se busca.

• INFLECTIONAL
  Especifica que se va a utilizar el analizador lingüístico dependiente del idioma en el término simple especificado. El comportamiento del analizador lingüístico se define en función de las reglas de análisis lingüístico de cada idioma concreto. El idioma neutro no tiene ningún analizador lingüístico asociado. El idioma de las columnas que se van a consultar se utiliza para hacer referencia al analizador lingüístico deseado. Si se especifica language_term, se utiliza el analizador lingüístico correspondiente a dicho idioma.

  Un <simple_term><generation_term> determinado dentro de un no coincidirá con nombres y verbos.

• THESAURUS
  Especifica que se utiliza el diccionario de sinónimos correspondiente al idioma de texto completo de la columna o el idioma especificado en la consulta. El patrón o patrones más largos de <simple_term> se hacen coincidir con el diccionario de sinónimos y se generan términos adicionales para expandir o reemplazar el patrón original. Si no se encuentra ninguna coincidencia para todo o parte de <simple_term>simple_term, la parte no coincidente se trata como un . Para obtener más información sobre el diccionario de sinónimos de búsqueda de texto completo, vea Configuración del diccionario de sinónimos.

• <proximity_term>
  Especifica una coincidencia de palabras o frases que deben estar en el documento en el que se busca. Al igual que el operador AND, <proximity_term> exige que ambos términos de búsqueda existan en el documento en el que se buscan.

  • NEAR | ~
    Indica que la palabra o frase de cada lado del operador NEAR o ~ debe aparecer en un documento para que se devuelva una coincidencia. Se pueden encadenar varias condiciones de proximidad, como en a NEAR b NEAR c o a ~ b ~ c. Las condiciones de proximidad encadenadas deben estar todas en el documento para que se devuelva una coincidencia.

    Cuando se utiliza en la función CONTAINSTABLE, la proximidad de los términos de búsqueda afecta al rango de cada documento. Cuanto más cerca están los términos de búsqueda coincidentes en un documento, mayor es la clasificación del mismo. Si los términos de búsqueda coincidentes están separados más de 50 términos, el rango devuelto en el documento es 0.

    Por ejemplo, CONTAINS (nombre_de_columna, zorro NEAR pollo') y CONTAINSTABLE (nombre_de_tabla, nombre_de_columna, 'zorro ~ pollo') devolverían ambos cualquier documento de la columna especificada que contuviera "zorro" y "pollo". Además, CONTAINSTABLE devuelve un rango para cada documento según la proximidad de "zorro" y "pollo". Por ejemplo, si un documento contiene la frase, "El zorro se comió el pollo", su clasificación sería alta.

    NEAR indica la distancia lógica entre los términos, en lugar de la distancia absoluta entre ellos. Por ejemplo, los términos dentro de frases diferentes o las sentencias dentro de un párrafo se tratan como más alejados que los términos de la misma frase o sentencia, sin tener en cuenta su proximidad real, suponiéndose que están menos relacionados. Igualmente, los términos párrafos diferentes se tratan como todavía más alejados.

• <weighted_term>
  Especifica que las filas coincidentes (devueltas por la consulta) coinciden con una lista de palabras y frases a las que se asigna opcionalmente un valor ponderado.

• ISABOUT
  Especifica la palabra clave <weighted_term>.

  • WEIGHT(weight_value)
    Especifica el valor de ponderación como un número entre 0,0 y 1,0. Cada componente de <weighted_term>weight_value puede incluir un weight_value. es una forma de modificar cómo varias partes de una consulta afectan al valor de rango asignado a cada fila que coincide con la consulta. WEIGHT no influye en los resultados de las consultas CONTAINS, pero sí en el rango de las consultas CONTAINSTABLE.

    [!NOTA]

    El separador decimal siempre es un punto, independientemente de la configuración regional del sistema operativo.

• { AND | & } | { AND NOT | &! } | { OR | | }
  Especifica una operación lógica entre dos condiciones de búsqueda.

  • AND | &
    Indica que en ambos casos se incluyen condiciones de búsqueda que se deben cumplir para encontrar coincidencias. Se puede utilizar el símbolo de "y" comercial (&) en lugar de la palabra clave AND para representar el operador AND.

  • AND NOT | &!
    Indica que la segunda condición de búsqueda no puede estar presente para encontrar coincidencias. Puede usarse el carácter de "y" comercial seguido del signo de exclamación de cierre (&!) en lugar de la palabra clave AND NOT para representar el operador AND NOT.

  • OR | |
    Indica que en uno de los dos casos se incluyen condiciones de búsqueda que se deben cumplir para encontrar coincidencias. Se puede utilizar el símbolo de barra (|) en lugar de la palabra clave OR para representar el operador OR.

    Cuando <contains_search_condition> contiene grupos entre paréntesis, estos se evalúan primero. Después de evaluar los grupos entre paréntesis, se aplican las reglas siguientes cuando se utilizan estos operadores lógicos con condiciones de búsqueda:

    • NOT se aplica antes que AND.

    • NOT sólo puede estar a continuación de AND, como en AND NOT. No se acepta el operador OR NOT. NOT no se puede especificar antes del primer término. Por ejemplo, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ) no es válido.

    • AND se aplica antes que OR.

    • Los operadores booleanos del mismo tipo (AND, OR) son asociativos y, por tanto, se pueden aplicar en cualquier orden.

    • n
      Es un marcador de posición que indica que se pueden especificar varias condiciones de búsqueda de CONTAINS y términos dentro de ellas.

Notas

Los predicados de texto completo y las funciones 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 para buscar en un conjunto de resultados que sea el producto de dos o más tablas.

CONTAINS no se reconoce como palabra clave si el nivel de compatibilidad es inferior a 70. Para obtener más información, vea sp_dbcmptlevel (Transact-SQL).

Los predicados de texto completo no se permiten en la cláusula OUTPUT cuando el nivel de compatibilidad de la base de datos está establecido en 100.

Comparación de LIKE con la búsqueda de texto completo

A diferencia de la búsqueda de texto completo, el predicado de Transact-SQLLIKE funciona solamente en patrones de caracteres. Además, no es posible utilizar el predicado de LIKE para consultar datos binarios con formato. Por otro lado, una consulta LIKE contra una cantidad grande de datos de texto no estructurados es mucho más lenta que una consulta de texto completo equivalente contra los mismos datos. Una consulta LIKE realizada en millones de filas de datos de texto puede tardar minutos en devolver resultados, mientras que una consulta de texto completo en los mismos datos puede tardar únicamente segundos, en función del número de filas que se devuelvan.

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 CONTAINS y una frase en <simple_term>

En el siguiente ejemplo se obtienen todos los productos que contienen la palabra "Mountain" o "Road".

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

C. Usar CONTAINS con <prefix_term>

En el siguiente ejemplo se obtienen todos los nombres de producto con una palabra como mínimo que empiece por el prefijo "chain" en la columna Name.

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

D. Usar CONTAINS y OR con <prefix_term>

En el siguiente ejemplo se obtienen todas las descripciones de categorías que contienen cadenas con los prefijos "chain" o "full".

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

E. Usar CONTAINS con <proximity_term>

En el siguiente ejemplo se obtienen todos los nombres de los productos que tengan la palabra bike cerca de la palabra performance.

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

F. Usar CONTAINS con <generation_term>

En el siguiente ejemplo se buscan todos los productos que tengan palabras derivadas de ride: riding, ridden, etc.

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

G. Usar CONTAINS con <weighted_term>

En el siguiente ejemplo se buscan todos los nombres de productos que contengan las palabras performance, comfortable o smooth; se asignan pesos distintos a cada palabra.

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

H. Usar CONTAINS con variables

En el siguiente ejemplo se utiliza una variable en lugar de un término de búsqueda específico.

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

I. Usar CONTAINS con un operador lógico (AND)

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

J. Usar CONTAINS para comprobar una inserción de fila

En el ejemplo siguiente se usa CONTAINS dentro de una subconsulta SELECT. Si se utiliza la base de datos AdventureWorks, la consulta obtiene el valor de comentario de todos los comentarios de la tabla ProductReview en un determinado ciclo. La condición de búsqueda utiliza el operador booleano AND.

USE AdventureWorks;
GO
INSERT INTO Production.ProductReview 
(ProductID, ReviewerName, EmailAddress, Rating, Comments) 
VALUES
(780, 'John Smith', 'john@fourthcoffee.com', 5, 
'The Mountain-200 Silver from AdventureWorks 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, 
                                 ' AdventureWorks AND 
                                   Redmond AND 
                                   "Mountain-200 Silver" '))

GO

Vea también

Tasks

Cómo crear un catálogo de texto (SQL Server Management Studio)

Cómo iniciar el Asistente para indización de texto completo (SQL Server Management Studio)

Cómo crear consultas de búsqueda de texto completo (Visual Database Tools)

Referencia

CREATE FULLTEXT CATALOG (Transact-SQL)

CREATE FULLTEXT INDEX (Transact-SQL)

CONTAINSTABLE (Transact-SQL)

FREETEXT (Transact-SQL)

FREETEXTTABLE (Transact-SQL)

WHERE (Transact-SQL)

Conceptos

Introducción a la búsqueda de texto

Introducción a la indización de texto completo

Información general de funciones y predicados de texto completo

Aspectos básicos de las combinaciones

Información general de funciones y predicados de texto completo

Búsqueda de texto completo (SQL Server)