LIKE compara texto con un patrón en el que % coincide con cualquier secuencia de caracteres y _ con exactamente uno, así que email LIKE '%@gmail.com' encuentra direcciones de Gmail. LIKE distingue mayúsculas. ILIKE hace la misma comparación sin distinguirlas, y ~ y ~* aplican expresiones regulares POSIX.
Devuelve a Asha y a Bella: ILIKE ignora las mayúsculas de la dirección de Bella, y el .co de Chen no coincide con el final.
Coincidencias con % y _
El patrón tiene que coincidir con el texto completo, así que un patrón sin % funciona como =. NOT LIKE y NOT ILIKE invierten el resultado.
A-___ coincide con A-100 pero no con A-2000, porque cada _ representa exactamente un carácter. El a-300 en minúsculas solo pasa la prueba de ILIKE.
Buscar un % o un _ literal
Una barra invertida escapa el siguiente carácter de un patrón, así que '%50\%%' encuentra textos que contienen 50%. Cuando los datos incluyen barras invertidas, elige otro carácter de escape con ESCAPE:
SAVE\_% solo coincide con SAVE_10. Sin el escape, _ también coincidiría con el 1 de SAVE10.
Expresiones regulares con ~
Los operadores de regex son ~ (coincide), ~* (coincide sin distinguir mayúsculas), !~ y !~* (no coincide). Una regex coincide en cualquier parte del texto, salvo que la ancles con ^ y $:
Solo 555-0101 tiene el formato con guion. regexp_replace con la opción g quita todo lo que no es un dígito, así que (555) 0199 pasa a ser 5550199 y call me queda como un texto vacío.
Extraer y contar coincidencias
Otras funciones de regex devuelven lo que coincidió en lugar de verdadero o falso:
| Función | Devuelve |
|---|---|
substring(s from 'pattern') | La primera coincidencia, o el primer grupo entre paréntesis |
regexp_match(s, 'pattern') | Los grupos de la primera coincidencia como text[] |
regexp_matches(s, 'pattern', 'g') | Una fila por coincidencia |
regexp_count(s, 'pattern') | El número de coincidencias (desde PostgreSQL 15) |
regexp_substr(s, 'pattern') | La primera coincidencia (desde PostgreSQL 15) |
regexp_like(s, 'pattern') | Lo mismo que ~, como función (desde PostgreSQL 15) |
regexp_split_to_table(s, 'pattern') | Una fila por cada trozo entre coincidencias |
substring('Order 4521 shipped' from '\d+') devuelve 4521. El capítulo de coincidencia de patrones del manual detalla las opciones y la sintaxis de las regex.
Que las búsquedas por patrón usen un índice
Un índice B-tree puede servir un patrón anclado al principio, como LIKE 'abc%', cuando la base de datos usa la intercalación C o el índice se crea con text_pattern_ops:
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
Un patrón que empieza con %, ILIKE y la mayoría de las regex no pueden usar un B-tree. Para esos casos, la extensión pg_trgm añade índices de trigramas que sirven LIKE, ILIKE, ~ y ~*:
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
Para una igualdad sin distinguir mayúsculas, sin patrones, lo más sencillo es un índice sobre lower(email), consultado con lower(email) = 'ben@work.io'. La página de funciones de texto explica lower, position y split_part.
Preguntas frecuentes
¿Cómo compruebo si un texto contiene una subcadena en PostgreSQL?
Usa col LIKE '%text%', o col ILIKE '%text%' para ignorar mayúsculas. position('text' in col) > 0 y strpos(col, 'text') > 0 también funcionan y no obligan a escapar % ni _.
¿LIKE distingue mayúsculas en PostgreSQL?
Sí. 'Apple' LIKE 'a%' es falso. Usa ILIKE, compara lower(col) LIKE 'a%' o usa el operador de regex sin distinción de mayúsculas ~*.
¿Cómo busco varios patrones a la vez?
Combina LIKE ANY con un array: name LIKE ANY (ARRAY['a%', 'b%']) es verdadero cuando coincide cualquier patrón, y LIKE ALL exige que coincidan todos. Una alternancia de regex como name ~ '^(a|b)' hace lo mismo.
¿Qué es SIMILAR TO en PostgreSQL?
Una mezcla de LIKE y expresiones regulares del estándar SQL: usa % y _ más |, *, + y grupos, y tiene que coincidir con el texto completo. LIKE o los operadores ~ suelen ser más claros.