viernes, 8 de mayo de 2009

Buscar en campos de texto con T-SQL: mucho más que LIKE

Una de las primeras cosas que se aprende cuando se empieza con T-SQL es a usar la cláusula LIKE para hacer comparaciones en campos de tipo texto (char, varchar o text). Pero esta cláusula está bastante limitada y no ofrece un buen rendimiento, así que ¿qué podemos utilizar en vez de LIKE?

Una muy buena opción es CONTAINS (que para ser sincero, descubrí de casualidad :P), que nos permite afinar bastante más las comparaciones y ofrece mucho mejor rendimiento.

Para poner un ejemplo del uso de CONTAINS voy a usar la base de datos PUBS (que ofrece Microsoft como ejemplo con SQL Server y se puede descargar desde aquí) y vamos a buscar registros en la tabla jobs filtrando por el campo job_desc.

Para que os hagáis una idea (y si no os apetece descargaros las bases de datos de ejemplo) la tabla jobs tiene los siguientes registros:


job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
1 New Hire - Job not specified 10 10
2 Chief Executive Officer 200 250
3 Business Operations Manager 175 225
4 Chief Financial Officier 175 250
5 Publisher 150 250
6 Managing Editor 140 225
7 Marketing Manager 120 200
8 Public Relations Manager 100 175
9 Acquisitions Manager 75 175
10 Productions Manager 75 165
11 Operations Manager 75 150
12 Editor 25 100
13 Sales Representative 25 100
14 Designer 25 100


Para poder usar CONTAINS primero debemos crear un índice FULLTEXT en la tabla, que se hace con las siguientes instrucciones:


CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON Jobs(job_desc)
KEY INDEX PK__jobs__173876EA
GO


Siendo PK__jobs__173876EA el índice principal (PRIMARY KEY) de la tabla.

Una vez hecho esto podemos empezar con los ejemplos:

Buscar palabra exacta

Obtendremos todos los registros que contengas ‘manager’ en el campo job_desc. Fijaros que no distingue mayúsculas y minúsculas:


select * from jobs where CONTAINS( job_desc, 'manager' )

job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
3 Business Operations Manager 175 225
7 Marketing Manager 120 200
8 Public Relations Manager 100 175
9 Acquisitions Manager 75 175
10 Productions Manager 75 165
11 Operations Manager 75 150


Además se pueden buscar frases (más de 1 palabra) y en las búsquedas se omiten los separadores.


select * from jobs where CONTAINS (job_desc, '"hire job"');
job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
1 New Hire - Job not specified 10 10



Buscar por aproximación con varias opciones

Buscamos los registros que contengan alguna palabra que empiece por ‘pub’ o por ‘edit’.


select * from jobs where CONTAINS(job_desc, '"pub*" OR "edit*"');

job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
5 Publisher 150 250
6 Managing Editor 140 225
8 Public Relations Manager 100 175
12 Editor 25 100


CONTAINS admite los operadores AND, AND NOT, OR.

Buscar palabra aproximada cerca de otra

Obtenemos registros que contengan una palabra que empiece por ‘pub’ y que esa palabra esté cerca de ‘manager’.


select * from jobs where CONTAINS (job_desc, '"pub*" NEAR manager')

job_id job_desc min_lvl max_lvl
-------- -------------------------------------------------- ------- -------
8 Public Relations Manager 100 175



CONTAINS permite además buscar por palabras derivadas y sinónimos, pero para eso es necesario usar un diccionario de sinónimos.

Happy codding ;)

Bola extra
Además de CONTAINS tenemos otras opciones para este tipo de búsquedas como FREETEXT
y CONTAINSTABLE

2 comentarios:

Anónimo dijo...

como puedo hacer lo contrario a la palabra de busqueda, es decir where CONTAINS( a.CAMPO1, <> 'hola' )

algo parecido a esto pero con CONTAINS

Select a.*, b.* From Tabla1 a, Tabla2 b Where a.PK1 *= b.FK1 having a.ConstrainField <> 'DiscardMe'

Pablo Bouzada dijo...

Si miras primer link del posts verás que en la documentación de CONTAINS pone lo siguiente:

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

Por lo que en tu caso, no puedes únicamente comparar si algo NO es 'hola', pero sí puedes comprobar si empieza por 'h' y no es 'hola'.

Espero que te sirva de ayuda :)