Introducción a freetext y contains en SQL-Server

FULL TEXT SEARCH

Se utilizan en campos de texto de gran tamaño utilizando unos índices denominados catálogos. Estos catálogos sólo se pueden utilizar con tablas que tengan definidas claves primarias y almacenan todas las palabras del contenido de las tablas a excepción de los artículos, preposiciones, etc.

Los catálogos no se actualizan automáticamente ni se guardan junto con la base de datos y cada tabla puede tener un único catálogo.

Para la utilización de estos catálogos dentro de una consulta, podemos utilizar dos métodos, el primero consiste en incluir los criterios dentro de una cláusula WHERE (CONTAINS ó FREETEXT) y la segunda es utilizando una tabla temporal que contiene el ratio de acierto en la consulta (CONTAINSTABLE ó FREETEXTTABLE).

El predicado CONTAINS

Se utiliza este predicado para buscar un texto específico en una tabla. Su funcionamiento es similar al predicado LIKE, a diferencia que éste no puede realizar búsquedas en los campos grandes de texto. CONTAINS no diferencia entre mayúsculas y minúsculas.

Sintaxis:

SELECT <Campos> FROM <Tabla>
WHERE CONTAINS(<Campo>,<Cadena>) OR/AND CONTAINS(<Campo>,<Cadena>)

El predicado CONTAINS soporta sintaxis complejas para buscar:

• Una o más palabras utilizando los operadores lógicos AND/OR.
• Familias de palabras
• Una palabra o una frase que comiencen por un determinado texto.
• Palabras o frases que estén unas cerca de otra.

Para buscar una palabra en un campo:

SELECT title_id, title, notes FROM titles
WHERE CONTAINS(notes,’business’)

Para localizar una frase en un campo:

SELECT title_id, titles, notes FROM titles
WHERE CONTAINS(notes,’ "common business applications" ‘)

Para localizar una frase en todos los campos habilitados:

SELECT title_id, titles, notes FROM titles
WHERE CONTAINS(*, ‘ "common business applications" ‘)

Utilizando AND, OR y NOT

SELECT title, notes FROM titles
WHERE CONTAINS(notes, ‘ "favorite recipes" OR "gourmet recipes" ‘)

SELECT titles, notes FROM titles
WHERE CONTAINS(notes, ‘ cooking AND NOT ("computer*") ‘)

SELECT titles, notes FROM titles
WHERE CONTAINS(notes, ‘ beer AND ales ‘)

SELECT titles, notes FROM titles
WHERE CONTAINS(*, ‘("ice skating" OR hockey) AND NOT olympics’)

Utilizando caracteres comodines

SELECT titles, notes FROM titles
WHERE CONTAINS(notes,’ "ice*" ‘)

SELECT titles, notes FROM titles
WHERE CONTAINS(notes, ‘ "light bread*" ‘)

Búsqueda de palabras o frases indicando la importancia de las palabras:

Esta búsqueda permite indicar el peso que tendrá cada una de las palabras o frases que se buscan sobre el resultado de la búsqueda, el peso oscila entre el valor más bajo 0.0 y el valor más alto 1.0.

SELECT Cliente, Nombre, Direccion FROM Cliente
WHERE CONTAINS (Direccion, ‘ISABOUT ( "Calle*", Velazquez WEIGHT(0.5), Serrano(0.9)’)

(Se encontrarán todos aquellos registros que en el campo dirección exista la cadena calle seguida de cualquier valor, ordenando primero los de "Calle Serrano", luego los de "Calle Velázquez" y después el resto.

Búsqueda de palabras cercanas:

Podemos realizar búsquedas por dos palabras e indicar que se encuentren próximas una de otra. El orden de las palabras no altera el resultado de la búsqueda.

SELECT titulo, notas FROM libros
WHERE CONTAINS (notas, "usuario NEAR computadora")

SELECT titulo, notas FROM libros
WHERE CONTAINS (notas, "usuario ~ computadora")

Se pueden indicar tres palabras, de tal forma que la segunda y la primera deben estar próximas al igual que la segunda y la tercera.

SELECT titulo, notas FROM libros
WHERE CONTAINS (notas, "usuario ~ principiante ~ computadora")

Búsquedas con frases:

... WHERE CONTAINS(Descripcion, " salsas ~ "mezcl*" ")

... WHERE CONTAINS(Descripcion, " "carne*" ~ "salsa empanada*" ")

El predicado FREETEXT

Al utilizar este predicado se analizan todas las palabras de las frases y nos devuelve como resultado, aquellos registros que contiene la frase completa o algún fragmento de la misma. La sintaxis es igual que el predicado CONTAINS.

... FREETEXT(descripcion, ‘ "En un lugar de la mancha de cuyo nombre no quiero acordarme" ‘)

El predicado CONTAINSTABLE

Este predicado tiene igual funcionamiento y sintaxis que CONTAINS a diferencia que en este caso devuelve una tabla con dos columnas, la primera llamada [KEY] contiene el valor de la clave primaria de la tabla que la que buscamos, la segunda llamada RANK devuelve un valor indicando el porcentaje de acierto en la búsqueda para cada registro.

SELECT Preguntas.Pregunta, Preguntas.Respuesta, Resultado.RANK
FROM Preguntas, CONTAINSTABLE(Respuesta, " SQL Server") AS Resultado
WHERE Preguntas.IdPregunta = Resultado.[KEY]
ORDER BY Resultado.RANK Desc

SELECT Preguntas.Pregunta, Preguntas.Respuesta, Resultado.RANK
FROM Preguntas INNER JOIN CONTAINSTABLE (Respuesta, "SQL Server") AS Resultado
ON Preguntas.IdPregunta = Resultados.KEY

El predicado FREETEXTTABLE

Es el equivalente a CONTAINSTABLE pero realizando búsquedas de FREETEXT.