Menu

LIKE, ILIKE y expresiones regulares en PostgreSQL

LIKE compara texto con un patrón usando % y _, ILIKE hace lo mismo sin distinguir mayúsculas, y los operadores ~ aplican expresiones regulares POSIX.

Esta página incluye editores ejecutables: edita, ejecuta y ve el resultado al instante.

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

Ilustración de los lenguajes de programación de Coddy

Aprende a programar con Coddy

Comenzar