Buscar texto: LIKE, ILIKE y expresiones regulares

1. Qué es

= compara texto exacto. Cuando no sabes el valor entero —quieres "los que empiezan por A", "los que contienen Manager"— hace falta un operador de patrón.

PostgreSQL tiene tres niveles, de menos a más potencia:

Empieza siempre por LIKE. Solo cuando se quede corto, sube.

2. Sintaxis

LIKE — dos comodines y ya

NOT LIKE niega. ILIKE es igual que LIKE pero ignora mayúsculas (la I es de insensitive) — y es exclusivo de PostgreSQL.

Regex — lo mínimo que hace falta

Diferencia clave con LIKE: la regex busca dentro del texto por defecto. LIKE 'Ch%' significa "empieza por Ch"; ~ 'Ch' significa "contiene Ch". Para anclar hay que poner ^.

3. Ejemplos sobre Northwind

Empieza por:

SELECT company_name FROM customers WHERE company_name LIKE 'A%';

            company_name
------------------------------------
 Alfreds Futterkiste
 Ana Trujillo Emparedados y helados
 Antonio Moreno Taquería
 Around the Horn

Contiene:

SELECT contact_title, count(*)
FROM customers
WHERE contact_title LIKE '%Manager%'
GROUP BY contact_title
ORDER BY 2 DESC;

   contact_title    | count
--------------------+-------
 Marketing Manager  |    12
 Sales Manager      |    11
 Accounting Manager |    10

El comodín de un solo carácter. Productos cuya segunda letra es h:

SELECT product_name FROM products WHERE product_name LIKE '_h%';

         product_name
------------------------------
 Chai
 Chang
 Chef Anton's Cajun Seasoning
 Chef Anton's Gumbo Mix
 Thüringer Rostbratwurst
 Chartreuse verte
 Chocolade
 Rhönbräu Klosterbier

8 productos. _ ocupa exactamente un hueco: ni cero, ni dos.

Mayúsculas: LIKE vs ILIKE

SELECT count(*) FROM customers WHERE company_name LIKE  'a%';   -- 0
SELECT count(*) FROM customers WHERE company_name ILIKE 'a%';   -- 4

Y el caso que enseña de verdad por qué importa:

SELECT company_name FROM customers WHERE company_name NOT LIKE '%a%' LIMIT 5;

       company_name
--------------------------
 Alfreds Futterkiste
 Around the Horn
 Blondesddsl père et fils
 Chop-suey Chinese
 Comércio Mineiro

Alfreds Futterkiste aparece en la lista de "no contiene la letra a". Y es correcto: contiene A, no a. Si lo que querías era "sin la letra a", el patrón correcto era NOT ILIKE '%a%'.

Los acentos son otro carácter

SELECT 'Antonio Moreno Taquería' LIKE '%Taqueria%' AS sin_tilde,
       'Antonio Moreno Taquería' LIKE '%Taquería%' AS con_tilde;

 sin_tilde | con_tilde
-----------+-----------
 f         | t

Ni LIKE ni ILIKE ignoran acentos. Northwind está lleno de Taquería, Côte de Blaye, Rhönbräu, Comércio. Un buscador que exija la tilde exacta es un buscador roto.

La solución de verdad es la extensión unaccent, disponible pero no instalada:

CREATE EXTENSION unaccent;                          -- una vez, por base
SELECT unaccent('Taquería');                        -- 'Taqueria'
SELECT * FROM customers WHERE unaccent(company_name) ILIKE unaccent('%taqueria%');

No la instales todavía en northwind. Cambiar la base fuera de seed.sql rompe la promesa de que se puede resetear. Cuando toque, va al seed.sql del repo (postgresql-northwind).

Buscar un comodín literal

Si el texto contiene _ o % de verdad, hay que escaparlo con \:

SELECT 'lote_2024' LIKE '%\_%' AS busca_guion_bajo,
       'lote 2024' LIKE '%\_%' AS con_espacio,
       'lote 2024' LIKE '%_%'  AS comodin;

 busca_guion_bajo | con_espacio | comodin
------------------+-------------+---------
 t                | f           | t

La tercera columna es la trampa: '%_%' no busca un guion bajo, busca "cualquier texto de al menos un carácter", así que es cierto para casi todo.

Cuando LIKE se queda corto: regex

Productos que empiezan por C o por B. Con LIKE harían falta dos condiciones. Con regex, una:

SELECT product_name FROM products WHERE product_name ~ '^[CB]' LIMIT 6;

         product_name
------------------------------
 Chai
 Chang
 Chef Anton's Cajun Seasoning
 Chef Anton's Gumbo Mix
 Carnarvon Tigers
 Côte de Blaye

Y sin importar mayúsculas, con ~*:

SELECT product_name FROM products WHERE product_name ~* '^ch';

4. Errores comunes

1. Usar = con comodines. WHERE nombre = 'A%' busca el texto literal A%. No da error; devuelve cero filas.
2. Olvidar que LIKE distingue mayúsculas. Ver el caso de Alfreds Futterkiste.
3. Olvidar los acentos. Igual de silencioso.
4. Creer que _ significa guion bajo. Significa un carácter cualquiera.
5. Poner % al principio y esperar velocidad. LIKE 'A%' puede usar un índice; LIKE '%A%' no puede, obliga a leer la tabla entera. Con 91 clientes da igual; con 20 millones, no. La solución en ese caso es un índice pg_trgm — ver indices.
6. Aplicar LIKE a una columna con nulos y esperar el complemento. NOT LIKE deja fuera los NULL, igual que <>. Ver null-y-la-logica-de-tres-valores.

5. Diferencias con SQL Server

Esta es la sección más útil del tema, porque es donde más se separan los dos motores.

Los corchetes de LIKE son una extensión de SQL Server, no del estándar SQL. En PostgreSQL, LIKE '[CB]%' no da error: busca literalmente un texto que empiece por el carácter [. Devuelve cero filas y no avisa. Es exactamente el tipo de consulta que se copia de un curso de SQL Server, parece funcionar y no funciona.

SIMILAR TO sí acepta corchetes y es la traducción literal:

SELECT product_name FROM products WHERE product_name SIMILAR TO '[CB]%';

Da el mismo resultado que ~ '^[CB]'. Aun así, usa la regex: SIMILAR TO es un híbrido raro (comodines de LIKE + sintaxis de regex) que casi nadie lee bien, y la propia documentación de PostgreSQL desaconseja su uso.

6. Ejercicios

1. Clientes cuyo nombre de empresa termina en s.
2. Productos que contienen la palabra Chocolade o Chocolate, sin importar mayúsculas. ¿Cuántos hay de cada uno?
3. Empleados cuyo cargo (title) contiene Sales, y cuántos son de cada cargo.
4. Traduce a PostgreSQL esta consulta de SQL Server, que busca clientes cuya ciudad empieza por una letra entre la A y la C: sql SELECT * FROM customers WHERE city LIKE '[A-C]%';
5. Clientes cuyo código postal es exactamente de 5 caracteres. Hazlo con LIKE y luego con regex, y di cuál prefieres.

7. Pregunta de negocio

Marketing quiere lanzar una campaña a los clientes "de tipo restaurante o comida": cualquiera cuyo nombre de empresa sugiera que está en hostelería. Te piden la lista.

No hay una columna que diga "sector". Hay que fabricarla desde el texto — y decir en qué no te fías.