[sql] Búsqueda por palabras en una frase

Holas de nuevo.

Esta vez les he traído un tip especial para principiantes ultra novatos en desarrollo de consultas a bases de datos ^^. Se trata del método que uso para poder hacer consultas a una tabla, filtrando los resultados de acuerdo una frase de búsqueda. Usaré sentencias en TSQL y lenguaje PHP para hacer los ejemplos.

Todos sabemos cómo hacer una consulta teniendo una palabra o frase exacta de búsqueda:

SELECT *
FROM tabla
WHERE campo LIKE '%palabra o frase clave%';

Si queremos aplicar la palabra o frase de búsqueda en varias columnas, la sintaxis sería:

SELECT *
FROM tabla
WHERE campo1 LIKE '%palabra o frase clave%'
OR campo2 LIKE '%palabra o frase clave%';

...añadiendo todas las columnas deseadas a continuación, siempre separando con el OR. Si hubiesen más condiciones de filtro, sería encapsular todos los elementos separados por OR, dentro paréntesis redondo:

SELECT *
FROM tabla
WHERE
campoX = 'valor'
AND
(campo1 LIKE '%palabra o frase clave%'
OR campo2 LIKE '%palabra o frase clave%');

También podríamos realizar la búsqueda en varias columnas concatenadas, si así nos conviene:

SELECT *
FROM tabla
WHERE campo1 + ' ' + campo2 LIKE '%palabra o frase clave%';

Ahora bien, todo esto va perfecto, pero... ¿qué pasa si queremos que la búsqueda usando la "frase clave" se realice buscando cada palabra de la frase, dentro del texto de la columna individual o las columnas concatenadas? Me explico con un ejemplo: la clásica búsqueda de personas mediante su nombre y apellidos.

Tenemos la tabla persona, teniendo varias columnas, y entre ellas: nombre1, nombre2, apellido1 y apellido2.
Esta tabla contiene los siguientes registros:

------- - ------- - --------- - ---------
nombre1 - nombre2 - apellido1 - apellido2
------- - ------- - --------- - ---------
Juan - Antonio - Pérez - Cortés
María - Inés - González - Rojas
Luis - Alberto - Rojas - Morales
Karina - Pamela - Pérez - González
Diego - Juan - Contreras - Pérez
------- - ------- - --------- - ---------

Si en mi buscador yo quisiera buscar a "Juan Pérez", podría hacerlo de cualquiera de las siguientes maneras:

A) Buscador detallado.
Colocar en el formulario de búsqueda una caja de texto para cada uno de los campos a consultar. O sea, una caja para hacer la búsqueda en la columna nombre1; otra, para la columna nombre2; y así etc.

Búsqueda
Primer Nombre :
Segundo Nombre :
Primer Apellido :
Segundo Apellido :

Luego, cuando por programación recupere el valor de las cuatro cajas de texto, podría armar la consulta por programación así:

En lenguaje PHP:

$consulta = "SELECT * FROM persona WHERE ";
$consulta .= (!empty($_POST["nombre1"]) ? " nombre1 LIKE '%".$_POST["nombre1"]."%'": "");
$consulta .= (!empty($_POST["nombre2"]) ? (!empty($_POST["nombre1"]) ? " AND ": "")." nombre2 LIKE '%".$_POST["nombre2"]."%'": "");

Y así continuar con los apellidos, etc...

Ahora bien, ¿qué ocurre si el usuario no sabe si "Juan" es el primer o segundo nombre, y/o lo mismo con el apellido "Pérez"? Más aún, ¿qué ocurrirá si por extraña razón hay una persona a la que han bautizado "Pérez", o que uno de sus apellidos es "Juan"? Etc...

En ese sentido, esta solución, aunque efectiva, resulta aparatosa y engorrosa, tanto para el usuario como el programador.

B) Buscador de Frase Exacta.
Un formulario de búsqueda con una caja de texto única.

Ingrese Palabras : 

Por programación, recibiremos el valor de la caja de texto y podríamos crear una gran consulta donde abarcar la mayor cantidad de alternativas de uso de la frase exacta de búsqueda:

En lenguaje PHP:

$consulta = "SELECT * FROM persona WHERE ";
$consulta .= " nombre1 LIKE '%".$_POST["frase"]."%'";
$consulta .= " OR nombre2 LIKE '%".$_POST["frase"]."%'";
$consulta .= " OR apellido1 LIKE '%".$_POST["frase"]."%'";
$consulta .= " OR apellido2 LIKE '%".$_POST["frase"]."%'";

Incluso podemos aplicar la concatenación de columnas:

$consulta .= " OR nombre1 + ' ' + nombre2 + ' ' + apellido1 + ' ' + apellido2 LIKE '%".$_POST["frase"]."%'";

El resultado, no obstante, no será el esperado, sea cual sea la condición usada. Si las analizamos una por una, tenemos que:

"Juan Pérez" en Nombre1 => Falso
"Juan Pérez" en Nombre2 => Falso
"Juan Pérez" en Apellido1 => Falso
"Juan Pérez" en Apellido2 => Falso
"Juan Pérez" en Nombre1 + Nombre2 + Apellido1 + Apellido2 => Falso

Para obtener los resultados esperados, sería necesario programar desarme de la frase y generar todas las combinaciones de columnas x palabras de búsqueda posibles. Por eso, es mejor pasar directo a la opción C).

C) Buscador de frase, por palabra (ordenado)
Teniendo el mismo formulario de búsqueda que en B), sólo tenemos que cambiar la consulta SQL armada por programación, de modo que la búsqueda sea realizada en todas las columnas, pero tomando todas las palabras de la frase por separado, aunque respetando el orden en el que fueron escritas.

En lenguaje PHP:

$consulta = "SELECT * FROM persona WHERE ";
$consulta .= " nombre1 + ' ' + nombre2 + ' ' + apellido1 + ' ' + apellido2 LIKE '%".str_replace(" ", "%", $_POST["frase"])."%'";

En SQL, la consulta generada tendría la siguiente apariencia:

SELECT *
FROM persona
WHERE nombre1 + ' ' + nombre2 + ' ' + apellido1 + ' ' + apellido2 LIKE '%Juan%Perez%';

Ya que hemos colocado esos comodines entre las palabras de la frase, SQL buscará dentro de la concatenación de columnas, todos los registros que contengan las palabras "Juan" y "Perez" en su interior. El resultado de esta consulta, nos retornará 2 registros:

------- - ------- - --------- - ---------
nombre1 - nombre2 - apellido1 - apellido2
------- - ------- - --------- - ---------
Juan - Antonio - Pérez - Cortés
Diego - Juan - Contreras - Pérez
------- - ------- - --------- - ---------

...ya que "Juan" fue encontrado en el nombre1 del primer registro y en el nombre2 del quinto registro; y "Perez" fue encontrado en el apellido1 del primer registro y en el apellido2 del quinto registro.

Finalmente, si analizamos esta consulta SQL armada por programación, podremos notar que incluso podríamos haber prescindido de dicha programación: aprovechando las funcionalidades del lenguaje de SQL, podemos armar la misma consulta de la forma:

SELECT *
FROM persona
WHERE nombre1 + ' ' + nombre2 + ' ' + apellido1 + ' ' + apellido2 LIKE '%' + REPLACE('Juan Perez', ' ', '%') + '%';

Y si trabajamos netamente en SQL, usando variables TSQL, también podría quedar:

DECLARE @frase VARCHAR(255);
SET @frase = 'Juan Perez';
SELECT *
FROM persona
WHERE nombre1 + ' ' + nombre2 + ' ' + apellido1 + ' ' + apellido2 LIKE '%' + REPLACE(@frase, ' ', '%') + '%';

¡Esto nos ayudará mucho si trabajamos las búsquedas dentro de procedimientos y/o funciones almacenadas!

Observación:
Es importante recalcar que la forma de separar la frase, explicada con anterioridad, asume que queremos buscar las palabras en el mismo orden en que fueron escritas las palabras de la frase la primera vez.

Si por alguna razón quisiéramos que no tomara en cuenta el orden, y que buscara indistintamente por todas las palabras, tendríamos que separar previamente por programación cada palabra de la frase y repetir la condición de búsqueda por cada palabra encontrada.

Esto lo podemos hacer por programación:

En lenguaje PHP:

$consulta = "SELECT * FROM persona WHERE ";
$palabras = explode(" ", $_POST["frase"]);
$c = 0;
foreach ($palabras as $palabra)
{
$c++;
$consulta .= " nombre1 + ' ' + nombre2 + ' ' + apellido1 + ' ' + apellido2 LIKE '%".$palabra."%' ";
$consulta .= (count($palabras) == $c ? "": " OR ");
}

Lo que daría por resultado:

SELECT *
FROM persona
WHERE
nombre1 + ' ' + nombre2 + ' ' + apellido1 + ' ' + apellido2 LIKE '%Juan%'
OR nombre1 + ' ' + nombre2 + ' ' + apellido1 + ' ' + apellido2 LIKE '%Perez%';

Para hacerlo por SQL, podrían valerse de la forma que explico en mi tip Pasar array a parámetro de procedimiento almacenado, aunque sólo es efectivo cuando las columnas contienen un solo valor (una palabra sola o una frase que sea no-separable, como nombres compuestos, etc.).

Si existe una forma correcta de hacerlo en SQL, sería bueno leerlo en los comentarios ^_^ Al menos por ahora no conozco una función tipo split o explode en SQL.

Y eso sería. Resultó largo de explicar, pero espero que les sirva de alguito ^^.
Saludos y gracias por leer!