SQL Server soporta dos predicados muy poderosos para
limitar el conjunto resultado de una consulta, por utilizar índices de texto
completo. Estos dos predicados son los predicados CONTAINS y FREETEXT. Ambos
soportan varios términos de búsqueda. Además de estos dos predicados, SQL
Server soporta dos funciones de valores de tabla, para búsquedas de texto
completo y tres funciones de valores de tabla, para búsquedas semánticas. Aprenderá
acerca de los dos predicados en esta lección y sobre las cinco funciones en la
próxima lección.
El Predicado CONTAINS
Con el predicado CONTAINS, puede buscar lo siguiente:
·
Palabras
y frases en el texto
·
Coincidencias
exactas o aproximadas
·
Formas
inflexionales de una palabra
·
Texto
en el cual una palabra de búsqueda está cerca de otra palabra de búsqueda
·
Sinónimos
de una palabra buscada
·
Un
prefijo de una palabra o una frase solamente
También puede agregar pesos personalizados, a las palabras
que está buscando. Utiliza el predicado CONTAINS, en la cláusula WHERE de sus
sentencias T-SQL.
Aquí están las formas más importantes de consultas con el
predicado CONTAINS en pseudocódigo, donde FTcolumn representa una columna
indexada de texto completo y ‘PalabraBusqueda?’ representa la palabra o frase
buscada:
·
SELECT…FROM...WHERE
CONTAINS(FTcolumn, 'PalabraBusqueda1'). Esta es la forma más simple. Está
buscando por filas donde la FTcolumn contiene una coincidencia exacta para 'PalabraBusqueda1'.
Este es un término simple.
· SELECT...FROM...WHERE CONTAINS(FTcolumn,
'PalabraBusqueda1 OR PalabraBusqueda2'). Está buscando por filas donde el FTcolumn contiene una
coincidencia exacta para 'PalabraBusqueda1' o para la palabra 'PalabraBusqueda2'.
También puede utilizar los operadores lógicos AND y AND NOT y cambiar el orden
de evaluación de los operadores en una expresión con paréntesis.
· SELECT...FROM...WHERE CONTAINS(FTcolumn,
'"PalabraBusqueda1 PalabraBusqueda2"'). Está buscando por filas donde el
FTcolumn contiene una coincidencia exacta para la frase "PalabraBusqueda1 PalabraBusqueda2".
· SELECT...FROM...WHERE CONTAINS(FTcolumn,
'"PalabraBusqueda1*"').
Está buscando por filas donde el FTcolumn contiene al menos una palabra que
empieza con las letras 'PalabraBusqueda1'. Este es un término prefijo.
· SELECT...FROM...WHERE CONTAINS(FTcolumn,
'NEAR(PalabraBusqueda1, PalabraBusqueda2)'). Está buscando por filas donde el FTcolumn contiene PalabraBusqueda1
y PalabraBusqueda2. Este es el término de proximidad personalizada más simple.
En esta versión más simple, sólo busca por ocurrencias de ambas palabras, sin
importar cuál es la distancia y el orden de los términos. El resultado es
similar a un término simple donde dos palabras o frases están conectadas con el
operador AND lógico.
· SELECT...FROM...WHERE CONTAINS(FTcolumn,
'NEAR((PalabraBusqueda1, PalabraBusqueda2), distance)'). Está buscando por filas donde el
FTcolumn contiene PalabraBusqueda1 y PalabraBusqueda2. El orden de las palabras
de búsqueda no es importante; sin embargo, la distancia es un entero que nos
dice cuántos términos no buscados pueden haber como máximo, entre los términos
buscados para calificar una fila, para el conjunto de resultado.
· SELECT...FROM...WHERE CONTAINS(FTcolumn,
'NEAR((PalabraBusqueda1, PalabraBusqueda2), distance, flag)'). Está buscando por filas donde el
FTcolumn contiene PalabraBusqueda1 y PalabraBusqueda2. Los dos términos
buscados deben estar más cerca juntos que la distancia. La bandera puede tomar
valores TRUE o FALSE; el predeterminado es FALSE. Si la bandera se establece a
TRUE, entonces el orden de los términos buscados es importante; PalabraBusqueda1
debe estar en el texto antes de PalabraBusqueda2.
· SELECT...FROM...WHERE
CONTAINS(FTcolumn, 'FORMSOF(INFLECTIONAL, PalabraBusqueda1)'). Este es el formato de término de generación
del predicado. Está buscando por filas donde el FTcolumn incluye alguna forma
inflexional de la palabra PalabraBusqueda1.
· SELECT...FROM...WHERE
CONTAINS(FTcolumn 'FORMSOF(THESAURUS, PalabraBusqueda1)'). Esto es de nuevo el formato de término de generación del predicado. Está
buscando por filas donde el FTcolumn incluye la palabra PalabraBusqueda1 o alguno
de los sinónimos para esta palabra, definida en el archivo thesaurus.
· SELECT...FROM...WHERE
CONTAINS(FTcolumn, 'ISABOUT (PalabraBusqueda1 weight(w1), PalabraBusqueda2 weight(w2))’). Este es un término ponderado. Los pesos
tienen influencia en el ranking de los documentos retornados. Sin embargo,
debido a que el predicado CONTAINS no rankea los resultados, esta forma no
tiene alguna influencia en él. La forma ponderada es útil para la función
CONTAINSTABLE.
· SELECT...FROM...WHERE
CONTAINS(PROPERTY (FTcolumn, 'PropertyName'), 'PalabraBusqueda1'). Esta es una propiedad de búsqueda. Necesita
tener documentos con algunas propiedades conocidas. En una consulta de este
tipo, está buscando por filas con documentos que tienen la propiedad PropertyName,
que contiene el valor PalabraBusqueda1.
El Predicado
FREETEXT
El predicado FREETEXT es menos específico y por lo tanto retorna
más filas que el predicado CONTAINS. Busca por los valores que coinciden con el
significado de una frase y no sólo con palabras exactas. Cuando utiliza el
predicado FREETEXT, el motor realiza una separación de palabras de la frase de
búsqueda, genera formas inflexionales (hace el stemming), e identifica una
lista de expansiones o de reemplazos para las palabras en el término buscado
con palabras del thesaurus. La forma es mucho más simple que la forma del
predicado CONTAINS: SELECT...FROM...WHERE FREETEXT(FTcolumn, 'PalabraBusqueda1 PalabraBusqueda2').
Con esto, está buscando por filas donde el FTcolumn incluye alguna de las
formas inflexionales y alguno de los sinónimos definidos de las palabras PalabraBusqueda1
y PalabraBusqueda2.
Practica:
Usando los Predicados CONTAINS y FREETEXT
Después de crear todos los componentes necesarios para
una solución de búsqueda de texto completo, es momento de empezar a utilizar la
búsqueda de texto completo.
Ejercicio
1: Utilice el predicado CONTAINS
En este ejercicio, se utiliza el predicado CONTAINS.
Además, se edita y utiliza un archivo thesaurus.
1.
Si
ha cerrado el SSMS, inícielo y conéctese a la instancia de SQL Server. Abra una
nueva ventana de consulta haciendo clic en el botón Nueva Consulta.
2.
Conéctese
a la base de datos TSQL2012.
3. Encuentre todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra "data".
Utilice la siguiente consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'data');
4. Encuentre todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra "data"
o la palabra "índex". Utilice la siguiente consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'data OR index');
5. Encuentre todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra "data"
y no la palabra "mining". Utilice la siguiente consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'data AND NOT mining');
6. Encuentra todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra "data"
o las palabras "fact" y "warehouse". Utilice la siguiente
consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'data OR (fact AND warehouse)');
7. Encuentra todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la frase "data
warehouse". Utilice la siguiente consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'"data warehouse"');
8. Encuentre todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye las palabras que
comienzan con el prefijo "add". Utilice la siguiente consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'"add*"');
9. Encuentre todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra
"problem" en cualquier lugar cerca de la palabra "data".
Utilice la siguiente consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'NEAR(problem, data)');
10. Encuentre todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra
"problem" en cualquier lugar cerca de la palabra "data".
Pruébelo con una consulta que busca fragmentos donde las palabras son menos de
cinco y luego con una consulta donde las palabras son menos de un término no
buscado de distancia. De las dos consultas siguientes, el primero debe retornar
una fila y el segundo ninguna fila.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'NEAR((problem, data),5)');
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'NEAR((problem, data),1)');
11. Encuentre todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra
"problem" en cualquier lugar cerca de la palabra "data".
Prueba con una consulta que busca fragmentos donde las palabras son menos de
cinco términos no buscados de distancia. Sin embargo, se especifica que la
palabra "problem" debe estar antes de la palabra "data".
Utilice la siguiente consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'NEAR((problem, data), 5, TRUE)');
12. Encuentra todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra
"presentation". Pruebe con una consulta que busca una coincidencia
exacta y con una consulta que busca cualquier forma de inflexional de la
palabra. De las siguientes dos consultas, la primera consulta no debe retornar alguna
fila y la segunda consulta una fila.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'presentation');
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'FORMSOF(INFLECTIONAL, presentation)');
Ejercicio
2: Usar Sinónimos y FREETEXT
En este ejercicio, edita y utiliza un archivo thesaurus
para añadir un sinónimo.
1. Utilice el Bloc de notas para editar
el archivo thesaurus para el idioma US English. Agregue un sinónimo "necessity"
para la palabra "need". El archivo a editar es el archivo tsenu.xml,
ubicado en una instalación predeterminada en la carpeta C:\Archivos de
programa\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ FTData. Si no utiliza
la ruta predeterminada para la instalación o utiliza una instancia no
predeterminada, entonces la ruta en una forma general es
SQL_Server_Install_Path\Microsoft SQL Server\MSSQL11.Instance_id\MSSQL\ FTData.
Elimine los comentarios XML del archivo.
Después de la edición, el contenido
del archivo debe ser el siguiente.
<XML ID="Microsoft Search
Thesaurus">
<thesaurus
xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>Internet
Explorer</sub>
<sub>IE</sub>
<sub>IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
<expansion>
<sub>need</sub>
<sub>necessity</sub>
</expansion>
</thesaurus>
</XML>
2. Cargue el archivo thesaurus para US English.
EXEC sys.sp_fulltext_load_thesaurus_file 1033;
3. Encuentre todas las filas donde la
columna extractodoc de la tabla dbo.Documentos incluye la palabra "need"
o su sinónimo. Pruebe con una consulta que busque por una coincidencia exacta y
con una consulta que busque por sinónimos de la palabra. De las siguientes dos
consultas, la primera consulta no debería retornar alguna fila y la segunda
consulta una fila.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'need');
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(extractodoc, N'FORMSOF(THESAURUS, need)');
4. Busque por todas las filas de la tabla
dbo.Documentos donde el documento en la columna contenidodoc contiene una
propiedad llamada "Authors" con un valor que incluye la palabra
"Dejan". Utilice la siguiente consulta.
SELECT id, titulo, extractodoc
FROM dbo.Documentos
WHERE CONTAINS(PROPERTY(contenidodoc,'Authors'), 'Dejan');
5. Por último, encontrar todas las filas donde
la columna extractodoc contiene alguna de las palabras "data",
"presentation", o "need". Las palabras pueden estar en
cualquier forma inflexional. Buscar sinónimos también. Utilice la siguiente
consulta.
SELECT id, titulo, tipodoc, extractodoc
FROM
dbo.Documentos
WHERE FREETEXT(extractodoc, N'data presentation need');
Este post describe el uso de los predicados CONTAINS Y FREETEXT en las busquedas completas (Full-Text). Espero les sea de utilidad.
ResponderBorrar