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