El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Utilitzar LIKE per a coincidència de patrons

Següent ►
►

En tancar el mòdul 3 van quedar pendents dues meitats: afinar el filtratge i aprendre a agregar. Aquesta lliçó obre la primera. Fins ara, quan has filtrat text ho has fet amb =, és a dir, exigint una coincidència exacta, caràcter a caràcter. Això serveix per a pais = 'Portugal', on el valor és un codi tancat, però no serveix per a res del que un negoci pregunta de debò: "els productes que porten la paraula ecològic", "els clients amb correu portuguès", "els càrrecs que comencen per Responsable".

Per a això existeix LIKE: un operador que compara una cadena contra un patró en lloc de contra un valor. En aquesta lliçó n'aprendràs els dos comodins, el parany de les majúscules i les seves tres solucions, com buscar un % que sigui un percentatge de debò i no un comodí, les expressions regulars de PostgreSQL per als casos que LIKE no cobreix i —molt important— per què una cerca que comença per comodí pot enfonsar el rendiment d'una taula gran.

Contingut

  1. De la igualtat exacta al patró
  2. LIKE i NOT LIKE: sintaxi i comodins
  3. Taula de patrons: què casa i què no
  4. Exemples sobre BotigaVerda
  5. Majúscules i minúscules: ILIKE, LOWER i colacions
  6. Buscar un % o un _ literals: ESCAPE
  7. SIMILAR TO i les expressions regulars
  8. Rendiment: per què '%text%' no pot fer servir un índex
  9. Errors habituals i consells
  10. Exercicis
  11. Conclusió

  1. De la igualtat exacta al patró

El problema amb = és que no admet matisos. Si la direcció demana "el llistat de productes ecològics", WHERE nom = 'ecològic' retorna zero files: cap producte no es diu exactament així, la paraula és dins del nom.

SELECT id, nom
FROM productes
WHERE nom = 'ecològic';
(0 files)

LIKE ho resol comparant contra un patró, on certs caràcters deixen de representar-se a si mateixos i passen a significar "qualsevol cosa".

SELECT id,
       nom,
       categoria_id,
       preu
FROM productes
WHERE nom LIKE '%ecològic%'
ORDER BY id;
id nom categoria_id preu
2 Arròs integral ecològic 1 kg 1 3.90
5 Tomàquet triturat ecològic 400 g 1 1.95
10 Detergent ecològic concentrat 1 L 3 11.20
14 Infusió de camamilla ecològica 20 u 4 3.25

4 files. Fixa't en el detall del patró: no hem escrit '%ecològica%' sinó '%ecològic%', que és l'arrel comuna de la paraula. Així casen tant ecològic (masculí, productes 2, 5 i 10) com ecològica (femení, producte 14). Retallar el patró abans de la part variable de la paraula és un truc elemental i eficaç.

  1. LIKE i NOT LIKE: sintaxi i comodins

La forma general és:

<expressió de text> [NOT] LIKE '<patró>' [ESCAPE '<caràcter>']

El patró és una cadena normal en la qual dos caràcters tenen significat especial:

Comodí Significat Analogia
% Qualsevol seqüència de zero o més caràcters L'* dels fitxers del sistema operatiu
_ Exactament un caràcter, sigui el que sigui L'? dels fitxers

Qualsevol altre caràcter del patró es compara literalment. I hi ha una conseqüència que sorprèn molta gent: un patró sense comodins equival a =.

-- Aquestes dues condicions són equivalents
WHERE pais LIKE 'Portugal'
WHERE pais =    'Portugal'

El resultat de LIKE és un booleà, així que es pot combinar amb AND, OR, NOT i parèntesis exactament igual que les comparacions de 02-03. NOT LIKE n'és la negació:

SELECT id,
       nom,
       cognoms,
       carrec
FROM empleats
WHERE carrec NOT LIKE 'Responsable de%'
ORDER BY id;
id nom cognoms carrec
1 Rosa Alcázar Vives Directora general
4 Óscar Peris Blasco Comercial
5 Laia Puig Sanchis Comercial
6 Marc Estévez Roig Atenció al client
7 Irene Salvador Mira Operària de magatzem
8 Daniel Vercher Lluch Analista de dades

6 files de 8. En queden fora Andrés Company Talens (Responsable de vendes) i Beatriz Nadal Ripoll (Responsable de logística).

Avís de nuls, ja: NOT LIKE arrossega el mateix problema que <> a 02-03. Si la columna admet NULL, aquelles files no apareixen ni amb LIKE ni amb NOT LIKE, perquè la comparació retorna desconegut en tots dos casos. Aquí carrec és NOT NULL i no hi ha risc, però amb clients.ciutat sí que n'hi hauria. La lliçó 04-03 ho explica a fons.

  1. Taula de patrons: què casa i què no

Els quatre patrons canònics, amb el nom que reben a la pràctica:

Patró Nom Casa amb No casa amb
'Oli%' Prefix (comença per) Oli d'oliva verge…, Oli corporal… Bàlsam…, oli… (minúscula)
'%ml' Sufix (acaba en) …extra 500 ml …20 u, …1 kg
'%àloe%' Conté Crema facial d'àloe vera 50 ml Xampú sòlid de romaní 80 g
'____' Longitud exacta (4 caràcters) Rosa, Marc, Laia Óscar (5), Ana (3)

I els que barregen tots dos comodins:

Patró Casa amb Comentari
'B%a' Barcelona (a clients.ciutat) Comença per B i acaba en a; no casa Bosch Ferrer
'%(pack _)' …lufa (pack 3), …cotó (pack 5), …soja (pack 2) El _ substitueix el dígit
'A_a' Ana Tres lletres, la primera A, l'última a

Comprovem el tercer de la primera taula amb dades reals:

SELECT id, nom, preu
FROM productes
WHERE nom LIKE '%(pack _)'
ORDER BY id;
id nom preu
11 Fregall vegetal de lufa (pack 3) 5.50
12 Bosses reutilitzables de cotó (pack 5) 9.90
13 Espelmes de cera de soja (pack 2) 13.75

3 files, els tres productes que es venen en format multipack. El _ ha casat amb 3, 5 i 2 respectivament. Si haguéssim escrit '%(pack %)' hauríem obtingut el mateix, però també casaria (pack 12) o (pack familiar): _ és més estricte i per tant més precís quan saps que allà hi va un sol caràcter.

El comodí _ és fàcil d'oblidar

Un cas instructiu amb comandes.metode_pagament:

SELECT DISTINCT metode_pagament
FROM comandes
WHERE metode_pagament LIKE 'contra_eemborsament';
metode_pagament
contrareemborsament

Casa. I no perquè el valor tingui un guió baix —no en té—, sinó perquè el _ del patró ha casat amb la lletra r de contra**r**eemborsament. És l'error que fa que WHERE columna LIKE 'preu_unitari' trobi coses que no esperaves quan busques noms de columna en un catàleg de metadades. A la secció 6 veuràs com evitar-ho.

  1. Exemples sobre BotigaVerda

4.1. Productes per format: el parany del sufix

El catàleg codifica el format al final del nom. Els envasos líquids petits acaben en ml:

SELECT id,
       nom,
       categoria_id,
       preu
FROM productes
WHERE nom LIKE '%ml'
ORDER BY id;
id nom categoria_id preu
1 Oli d'oliva verge extra 500 ml 1 12.50
6 Crema facial d'àloe vera 50 ml 2 18.90
8 Oli corporal d'ametlles 200 ml 2 14.25
9 Bàlsam labial de calèndula 15 ml 2 4.60
16 Kombutxa de gingebre 750 ml 4 4.95

5 files. Ara els que es venen per pes en grams:

-- ⚠️ INCORRECTA: '%g' també casa amb 'kg'
SELECT id, nom
FROM productes
WHERE nom LIKE '%g'
ORDER BY id;
id nom
2 Arròs integral ecològic 1 kg
3 Mel de tarongina crua 500 g
4 Pasta d'espelta 500 g
5 Tomàquet triturat ecològic 400 g
7 Xampú sòlid de romaní 80 g
15 Te verd matcha cerimonial 30 g
19 Desodorant natural en barra 50 g

7 files, i una hi sobra: l'arròs es ven en quilos, no en grams. '%g' significa "acaba en la lletra g", i kg acaba en g. La correcció és incloure l'espai al patró:

-- ✅ CORRECTA
SELECT id, nom, preu
FROM productes
WHERE nom LIKE '% g'
ORDER BY id;
id nom preu
3 Mel de tarongina crua 500 g 9.75
4 Pasta d'espelta 500 g 2.80
5 Tomàquet triturat ecològic 400 g 1.95
7 Xampú sòlid de romaní 80 g 8.40
15 Te verd matcha cerimonial 30 g 22.00
19 Desodorant natural en barra 50 g 7.80

6 files. L'espai davant de la g és la diferència entre un informe correcte i un altre que barreja unitats. És exactament el tipus d'error que no dona cap avís.

4.2. Clients per domini de correu

BotigaVerda ven a tres països i els correus d'exemple reflecteixen el domini de cadascun. Els clients portuguesos:

SELECT id,
       nom || ' ' || cognoms AS client,
       email,
       ciutat,
       pais
FROM clients
WHERE email LIKE '%.pt'
ORDER BY id;
id client email ciutat pais
7 Sofia Moreira Costa [email protected] Lisboa Portugal
8 Tiago Almeida Nunes [email protected] Porto Portugal

I els francesos, combinant dos patrons amb OR:

SELECT id,
       nom || ' ' || cognoms AS client,
       email,
       pais
FROM clients
WHERE email LIKE '%.fr'
   OR email LIKE '%.pt'
ORDER BY pais, id;
id client email pais
9 Camille Dubois [email protected] França
10 Julien Moreau [email protected] França
7 Sofia Moreira Costa [email protected] Portugal
8 Tiago Almeida Nunes [email protected] Portugal

4 files: els quatre clients internacionals. Els altres 11 fan servir example.com.

Compte amb el que estàs mesurant. Filtrar pel domini del correu no és el mateix que filtrar per pais. Aquí coincideixen perquè el conjunt de dades està construït així, però en una base real hi ha portuguesos amb @gmail.com i espanyols amb @example.fr. Si la pregunta de negoci és "clients de Portugal", la resposta correcta és WHERE pais = 'Portugal', no un LIKE sobre el correu. LIKE és potent i per això és fàcil fer-lo servir per respondre una pregunta semblant però diferent.

4.3. Càrrecs de l'organigrama

SELECT id,
       nom || ' ' || cognoms AS empleat,
       carrec,
       salari
FROM empleats
WHERE carrec LIKE 'Responsable de%'
ORDER BY id;
id empleat carrec salari
2 Andrés Company Talens Responsable de vendes 41000.00
3 Beatriz Nadal Ripoll Responsable de logística 39500.00

2 files: els dos comandaments intermedis de la jerarquia que vas veure a 03-06. Aquest patró —prefix fix, resta lliure— és el més freqüent a la pràctica i, com veuràs a la secció 8, l'únic que pot aprofitar un índex.

  1. Majúscules i minúscules: ILIKE, LOWER i colacions

A 02-03 vas veure que pais = 'espanya' retorna zero files. LIKE hereta exactament el mateix comportament: a PostgreSQL, LIKE distingeix majúscules de minúscules.

SELECT id, nom FROM productes WHERE nom LIKE 'oli%';
(0 files)
SELECT id, nom FROM productes WHERE nom LIKE 'Oli%';
id nom
1 Oli d'oliva verge extra 500 ml
8 Oli corporal d'ametlles 200 ml

I això és un problema real, perquè qui escriu en un cercador no prem majúscules. Hi ha tres solucions.

Solució 1: ILIKE (extensió de PostgreSQL)

SELECT id, nom, preu
FROM productes
WHERE nom ILIKE '%oli%'
ORDER BY id;
id nom preu
1 Oli d'oliva verge extra 500 ml 12.50
8 Oli corporal d'ametlles 200 ml 14.25

La I és d'insensitive. La seva negació és NOT ILIKE. És l'opció més llegible i la que farem servir al curs quan treballem sobre PostgreSQL, amb un advertiment: no és SQL estàndard, així que una consulta amb ILIKE no és portable.

Solució 2: LOWER als dos costats

SELECT id, nom, preu
FROM productes
WHERE LOWER(nom) LIKE LOWER('%Oli%')
ORDER BY id;

Retorna les mateixes 2 files. És portable a qualsevol motor, i per això és la forma que veuràs en codi que ha de funcionar en diverses bases de dades.

Dos detalls importants:

  • LOWER va als dos costats. Si escrius LOWER(nom) LIKE '%Oli%' no hi casarà mai res, perquè la columna s'ha passat a minúscules però el patró no.
  • Aplicar una funció a la columna impedeix fer servir un índex normal. És el mateix avís de 02-03 sobre WHERE preu - cost > 5. La solució (un índex sobre l'expressió LOWER(nom)) és matèria del mòdul 8.

LOWER i UPPER s'estudien a fons, juntament amb la resta de funcions de cadena, a la lliçó 06-01. Aquí només les fem servir com a eina.

Solució 3: una colació insensible

PostgreSQL 12 i posteriors permeten definir colacions no deterministes, que fan que la comparació mateixa ignori les majúscules (i fins i tot els accents). Es declaren una vegada i afecten =, LIKE i ORDER BY sense tocar les consultes:

CREATE COLLATION ca_ci (
    provider = icu,
    locale   = 'ca-ES-u-ks-level2',
    deterministic = false
);

És la solució més neta quan tota una columna s'ha de comparar així, però és una decisió de disseny d'esquema (mòdul 5) i té contrapartides: amb una colació no determinista, LIKE sobre aquella columna no pot fer servir els índexs habituals.

Comportament per motor

Motor LIKE distingeix majúscules? Forma insensible idiomàtica
PostgreSQL Sí, sempre ILIKE, o LOWER(col) LIKE LOWER(pat), o colació no determinista
MySQL / MariaDB No per defecte: depèn de la colació, i l'habitual (utf8mb4_0900_ai_ci) és insensible a majúscules i a accents Ja ho és; per fer-lo sensible, LIKE ... COLLATE utf8mb4_0900_as_cs o BINARY
SQLite No per a ASCII, sí per a la resta: 'a' LIKE 'A' és cert, però 'á' LIKE 'Á' és fals LOWER(col) LIKE LOWER(pat), amb el mateix límit ASCII
SQL Server Depèn de la colació de la columna; les instal·lacions típiques són insensibles (_CI_) LOWER(...) o COLLATE ..._CI_AS explícit
Oracle Sí LOWER(...), o els paràmetres de sessió NLS_COMP/NLS_SORT

Conseqüència pràctica: una consulta amb LIKE que funciona perfectament a MySQL pot retornar zero files en portar-la a PostgreSQL, sense que res hagi canviat a les dades. És una de les diferències de dialecte que més temps fa perdre en migracions. Quan escriguis SQL que hagi de viatjar, fes servir LOWER() als dos costats i no depenguis del comportament per defecte de ningú.

  1. Buscar un % o un _ literals: ESCAPE

I si el que busques és un signe de percentatge? Com que % significa "qualsevol cosa", el patró '%%%' no busca un percentatge: casa amb absolutament tot.

La solució és escapar el comodí, marcant-lo amb un caràcter que li tregui el poder. A PostgreSQL (i a MySQL) el caràcter d'escapada per defecte és la barra invertida \:

SELECT contingut
FROM (VALUES ('Rebaixa del 20% en begudes'),
             ('Enviament gratis a partir de 50 EUR'),
             ('Pack 2x1 en cosmètica')) AS t(contingut)
WHERE contingut LIKE '%\%%';
contingut
Rebaixa del 20% en begudes

Llegeix el patró '%\%%' d'esquerra a dreta: % (qualsevol cosa) + \% (un signe de percentatge literal) + % (qualsevol cosa).

Si la barra invertida et resulta il·legible —o si les teves dades contenen barres invertides—, la clàusula ESCAPE permet triar un altre caràcter:

SELECT contingut
FROM (VALUES ('Rebaixa del 20% en begudes'),
             ('Enviament gratis a partir de 50 EUR'),
             ('Pack 2x1 en cosmètica')) AS t(contingut)
WHERE contingut LIKE '%!%%' ESCAPE '!';
contingut
Rebaixa del 20% en begudes

Mateix resultat, patró més llegible. ESCAPE funciona igual per al guió baix. Recuperant el cas de la secció 3, així es busca un _ de debò:

Consulta Què busca realment
LIKE 'contra_eemborsament' contra + qualsevol caràcter + eemborsament → casa amb contrareemborsament
LIKE 'contra\_eemborsament' contra_eemborsament literal → 0 files a BotigaVerda
LIKE 'preu\_unitari' El nom de columna exacte, amb el seu guió baix

Nota de dialecte: l'estàndard SQL no defineix cap caràcter d'escapada per defecte; obliga a declarar-lo amb ESCAPE. PostgreSQL i MySQL sí que en tenen un (\); SQL Server i Oracle no, així que allà LIKE '%\%%' busca literalment una barra seguida de qualsevol cosa i cal escriure ESCAPE '\' explícitament. Escriure sempre ESCAPE explícit és l'opció portable.

  1. SIMILAR TO i les expressions regulars

LIKE es queda curt tan bon punt la pregunta inclou alternatives ("acaba en ml, g, kg, L o u"), repeticions ("dos o més dígits") o classes de caràcters ("una lletra seguida d'un número"). PostgreSQL ofereix dues famílies més.

7.1. SIMILAR TO

És SQL estàndard i és un híbrid: fa servir els comodins de LIKE (% i _) més alguns operadors d'expressió regular (|, *, +, ?, (), [], {}).

SELECT id, nom
FROM productes
WHERE nom SIMILAR TO '%(ml|kg)'
ORDER BY id;
id nom
1 Oli d'oliva verge extra 500 ml
2 Arròs integral ecològic 1 kg
6 Crema facial d'àloe vera 50 ml
8 Oli corporal d'ametlles 200 ml
9 Bàlsam labial de calèndula 15 ml
16 Kombutxa de gingebre 750 ml

6 files: els 5 productes en mil·lilitres més l'arròs en quilos. Amb LIKE haurien calgut dues condicions unides per OR.

A la pràctica SIMILAR TO es fa servir poc: qui necessita aquesta potència sol preferir les expressions regulars completes, i qui no la necessita es queda amb LIKE. Convé conèixer-lo perquè apareix en codi heretat i perquè és l'única de les tres famílies que forma part de l'estàndard juntament amb LIKE.

7.2. Expressions regulars POSIX: ~, ~*, !~, !~*

Són els operadors natius de PostgreSQL i fan servir la sintaxi d'expressions regulars que ja coneixes de qualsevol llenguatge de programació:

Operador Significat
~ Casa l'expressió regular, distingint majúscules
~* Casa, sense distingir majúscules
!~ No casa, distingint majúscules
!~* No casa, sense distingir majúscules

Diferència fonamental amb LIKE: una expressió regular casa per defecte en qualsevol part de la cadena, no de principi a fi. Per això ^ (inici) i $ (final) són necessaris quan vols ancorar.

Exemple 1: validar el format d'un correu electrònic.

SELECT id,
       nom || ' ' || cognoms AS client,
       email
FROM clients
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
ORDER BY id;
(0 files)

Zero files, que aquí és la resposta desitjada: els 15 correus de BotigaVerda tenen format vàlid. Aquest patró —buscar allò que no compleix— és la forma habitual d'auditar la qualitat de les dades abans d'una migració o d'una campanya de correu.

Exemple 2: productes el nom dels quals acaba en una unitat de mesura.

SELECT id,
       nom,
       preu
FROM productes
WHERE nom !~ '[0-9]+ ?(ml|kg|g|L|u)$'
ORDER BY id;
id nom preu
11 Fregall vegetal de lufa (pack 3) 5.50
12 Bosses reutilitzables de cotó (pack 5) 9.90
13 Espelmes de cera de soja (pack 2) 13.75
18 Raspall de dents de bambú 3.50

4 files: els únics quatre productes del catàleg que no indiquen gramatge al final. Els altres 16 sí. Això és una comprovació de qualitat de catàleg real: si demà l'equip de continguts afegeix una referència sense format, aquesta consulta la delata. Amb LIKE caldria encadenar cinc condicions amb OR i tot i així no podries exigir que davant de la unitat hi hagués un número.

7.3. Les tres famílies comparades

LIKE / ILIKE SIMILAR TO ~ (POSIX)
Estàndard SQL Sí Sí No (PostgreSQL)
Comodins %, _ %, _ + operadors regex Sintaxi regex pura (., *, +, ?, [], (), |)
Ancorat Sempre a la cadena completa Sempre a la cadena completa No, llevat que facis servir ^ i $
Alternatives (a o b) No Sí, (a|b) Sí, a|b
Repeticions No Sí, {2,4} Sí, {2,4}
Insensible a majúscules ILIKE No directament ~*
Pot fer servir índex B-tree Sí, si el patró comença per text fix No Només amb ancoratge ^ i anàlisi del planificador
Llegibilitat Molt alta Mitjana Baixa per a qui no domina regex
Quan fer-lo servir El 90 % dels casos Gairebé mai Validacions i extraccions complexes

Nota de dialecte: les expressions regulars varien molt entre motors. MySQL fa servir REGEXP / RLIKE (i REGEXP_LIKE des de la 8.0), Oracle fa servir REGEXP_LIKE, i SQLite té l'operador REGEXP però la funció que l'implementa no ve inclosa: cal registrar-la des de l'aplicació. SIMILAR TO pràcticament només existeix a PostgreSQL. Si el teu SQL ha de ser portable, LIKE és l'única aposta segura.

  1. Rendiment: per què '%text%' no pot fer servir un índex

Aquest és el punt que separa una consulta que funciona amb 20 files d'una que funciona amb 20 milions.

Un índex B-tree —l'índex normal, el que estudiaràs al mòdul 8— desa els valors ordenats alfabèticament, com un diccionari. I això determina què pot i què no pot accelerar:

flowchart TD
    A["LIKE 'Oli%'"] --> B["L'índex està ordenat.<br/>Tot el que comença per 'Oli'<br/>està junt, en un tram contigu"]
    B --> C["✅ Salta directament al tram.<br/>Índex aprofitat"]
    D["LIKE '%oli%'"] --> E["El que conté 'oli' al mig<br/>pot estar en qualsevol punt<br/>de l'ordre alfabètic"]
    E --> F["❌ Cal llegir i comprovar<br/>TOTES les files: escaneig complet"]

Pensa-ho amb un diccionari en paper: buscar totes les paraules que comencen per oli és obrir per la O i llegir un tram. Buscar totes les que contenen oli obliga a llegir el diccionari sencer.

Patró Tipus Índex B-tree?
LIKE 'Oli%' Prefix fix Sí
LIKE 'Oli d%oliva%' Prefix fix + resta Sí, fa servir el prefix per acotar
LIKE '%oli' Sufix No
LIKE '%oli%' Conté No
LIKE '_li%' Comença per comodí No
ILIKE 'Oli%' Insensible No amb un índex normal
LOWER(nom) LIKE 'oli%' Funció sobre la columna No amb un índex normal, sí amb un índex sobre l'expressió

Hi ha dos matisos que convé conèixer des d'ara:

  1. Perquè LIKE 'prefix%' faci servir l'índex a PostgreSQL, l'índex ha de fer servir una classe d'operadors especial (text_pattern_ops) llevat que la base estigui en la colació C. És un detall que s'explica al mòdul 8, però explica per què de vegades "tinc l'índex i no el fa servir".
  2. Per a les cerques '%text%' hi ha una solució específica: l'extensió pg_trgm, que descompon el text en trigrames (grups de tres caràcters) i permet crear índexs GIN o GiST capaços d'accelerar LIKE '%text%' i ILIKE '%text%'. S'activa amb CREATE EXTENSION pg_trgm; i també es veu al mòdul 8.

I quan LIKE deixa de ser l'eina

LIKE compara caràcters, no paraules. No sap que ecològic i ecològics són la mateixa paraula, no ignora els accents, no ordena els resultats per rellevància i no entén que qui busca "oli oliva" vol les mateixes files que qui busca "oliva oli".

Per a això existeix la cerca de text complet (full-text search), que PostgreSQL incorpora de sèrie amb els tipus tsvector i tsquery i l'operador @@. Converteix el text en una llista d'arrels lèxiques, ignora les paraules buides i admet índexs GIN molt eficients. No entra en aquest curs, però convé que sàpigues que existeix: si et descobreixes encadenant cinc ILIKE '%…%' amb OR per fer un cercador, l'eina correcta ja no és LIKE.

Errors habituals i consells

  • Fer servir = quan volies LIKE. WHERE nom = '%ml' no dona error: busca literalment la cadena %ml i retorna zero files.
  • Oblidar que LIKE distingeix majúscules a PostgreSQL. Zero files i cap pista. Si véns de MySQL, és la primera sorpresa que t'enduràs.
  • Aplicar LOWER només a un costat. LOWER(nom) LIKE '%Oli%' no casa mai. Hi van tots dos.
  • Escriure '%g' quan volies '% g'. L'arròs d'1 kg s'esmuny al llistat de productes per grams. Ancora el patró amb el separador que correspongui.
  • Confiar que _ és un guió baix literal. És un comodí. Per buscar el caràcter, \_ o ESCAPE.
  • Buscar un % sense escapar-lo. LIKE '%%%' retorna totes les files de la taula.
  • Suposar que NOT LIKE retorna "tota la resta". Les files amb NULL no surten ni a un costat ni a l'altre. Comprova que les dues meitats sumin el total (04-03).
  • Fer servir LIKE sobre el correu per deduir el país. Respon una pregunta semblant, no la mateixa. Fes servir la columna que modela la dada.
  • Posar un comodí al principi en una taula gran. '%text%' força un escaneig complet. Amb 20 files no ho notes; amb 20 milions, la teva consulta triga minuts.
  • Consell: comença sempre pel patró més lax i ves ajustant. Llança ILIKE '%ol%', mira què surt, i només llavors afina. És més ràpid que endevinar el patró exacte a la primera.
  • Consell: compta les files de la condició i de la seva negació. Si LIKE dona 6 i NOT LIKE dona 14 sobre una taula de 20, la lògica tanca. Si no tanca, hi ha nuls.
  • Consell: si el patró ha de ser portable, escriu LOWER(col) LIKE LOWER(pat) ESCAPE '\'. És més verbós, però funciona igual als cinc motors.

Exercicis

Exercici 1

L'equip de continguts vol revisar com està escrit el catàleg. Escriu tres consultes sobre productes:

  1. Els productes el nom dels quals conté la paraula natural (en qualsevol posició i sense importar les majúscules).
  2. Els productes el nom dels quals comença per la lletra C.
  3. Els productes que no porten la paraula ecològic al nom però sí que pertanyen a la categoria 1 (Alimentació).

Indica en cada cas quantes files surten i per què has triat aquell patró.

Exercici 2

Màrqueting prepara una campanya per correu electrònic i necessita segmentar. Sobre clients:

  1. Compta quants clients tenen un correu del domini example.com fent servir LIKE.
  2. Escriu la consulta que retorna els clients el nom de pila dels quals té exactament 3 lletres.
  3. Un company proposa WHERE email LIKE '%@example.com%' per al primer apartat. Retorna el mateix? És equivalent? Quin preferiries i per què?

Exercici 3

Direcció vol una auditoria del catàleg. Escriu una única consulta que retorni, per a cada producte, el seu id, el seu nom i una columna format que valgui:

  • el nom mateix si no acaba en unitat de mesura (els quatre que vas veure a la secció 7.2),
  • i res més: només aquests quatre productes han d'aparèixer.

Resol-ho primer amb expressions regulars i després respon: ho hauries pogut fer només amb LIKE? Què et faltaria?

Solucions

Solució 1

1. Conté natural, sense importar les majúscules:

SELECT id, nom, categoria_id, preu
FROM productes
WHERE nom ILIKE '%natural%'
ORDER BY id;
id nom categoria_id preu
19 Desodorant natural en barra 50 g 5 7.80

1 fila. Es fa servir ILIKE perquè no sabem si la paraula apareix en majúscula al principi d'algun nom; amb LIKE '%natural%' el resultat seria el mateix en aquestes dades, però la consulta seria fràgil davant d'una futura referència anomenada "Natural…".

2. Comença per C:

SELECT id, nom, preu
FROM productes
WHERE nom LIKE 'C%'
ORDER BY id;
id nom preu
6 Crema facial d'àloe vera 50 ml 18.90
20 Càpsules d'espirulina 120 u 16.40

2 files. Fixa't que Càpsules hi entra: l'accent és a la segona lletra, i la primera continua sent una C normal. Aquí sí que convé LIKE i no ILIKE: els noms de producte comencen per majúscula per convenció, i ILIKE 'c%' no aportaria res mentre que sí que impediria aprofitar un índex.

3. Alimentació sense la paraula ecològic:

SELECT id, nom, categoria_id, preu
FROM productes
WHERE categoria_id = 1
  AND nom NOT LIKE '%ecològic%'
ORDER BY id;
id nom categoria_id preu
1 Oli d'oliva verge extra 500 ml 1 12.50
3 Mel de tarongina crua 500 g 1 9.75
4 Pasta d'espelta 500 g 1 2.80

3 files. La comprovació de coherència: la categoria 1 té 5 productes, dos dels quals (2 i 5) porten ecològic al nom, i 5 − 2 = 3. Tanca. Que tanqui és important justament perquè nom és NOT NULL; si admetés nuls, NOT LIKE els hauria descartat i el compte no quadraria.

Solució 2

1.

SELECT COUNT(*) AS clients_domini_com
FROM clients
WHERE email LIKE '%@example.com';
clients_domini_com
11

11 clients, els 15 menys els 2 portuguesos i els 2 francesos.

2. Nom de pila d'exactament 3 lletres: tres guions baixos i res més.

SELECT id, nom, cognoms, ciutat
FROM clients
WHERE nom LIKE '___'
ORDER BY id;
id nom cognoms ciutat
5 Ana Belmonte Roca Barcelona
6 Pau Llorens Vidal València

2 files. Amb LIKE '___' (tres _) exigeixes exactament tres caràcters, perquè el patró s'ancora a la cadena completa. Si haguessis escrit '___%' obtindries tots els noms de tres lletres o més, és a dir, gairebé tota la taula.

3. '%@example.com%' retorna el mateix aquí (11 files), però no és equivalent. El % final significa "i després qualsevol cosa", així que també casaria amb [email protected] o amb [email protected]. És un patró més lax que respon a "el correu conté @example.com", no a "el correu acaba en @example.com".

Preferible '%@example.com': diu exactament el que volem, no arrossega falsos positius i —detall gens menor— un patró que acaba sense comodí continua sense poder fer servir índex, però almenys no convida a errors lògics. La regla general: no posis comodins que no necessitis, cadascun amplia silenciosament el conjunt de respostes.

Solució 3

SELECT id,
       nom AS format
FROM productes
WHERE nom !~ '[0-9]+ ?(ml|kg|g|L|u)$'
ORDER BY id;
id format
11 Fregall vegetal de lufa (pack 3)
12 Bosses reutilitzables de cotó (pack 5)
13 Espelmes de cera de soja (pack 2)
18 Raspall de dents de bambú

4 files. Desglossament del patró:

Fragment Significat
[0-9]+ Un o més dígits
? Un espai opcional
(ml|kg|g|L|u) Qualsevol de les cinc unitats
$ Ancorat al final de la cadena
!~ Retorna les files que no casen

Es podria fer amb LIKE? Només a mitges. Podries escriure:

-- Aproximació amb LIKE: incompleta
WHERE nom NOT LIKE '%ml'
  AND nom NOT LIKE '% g'
  AND nom NOT LIKE '% kg'
  AND nom NOT LIKE '% L'
  AND nom NOT LIKE '% u'

i en aquestes dades donaria les mateixes 4 files. Però et falten tres coses que LIKE no sap expressar:

  1. Exigir que davant de la unitat hi hagi un número. Un producte anomenat "Sabó de mans ml" —una errada perfectament possible— quedaria classificat com a "té format", perquè LIKE '%ml' només mira les dues últimes lletres. L'expressió regular exigeix [0-9]+ al davant i ho detectaria.
  2. Tolerar variacions de majúscules. "Gel de dutxa 500 mL" no casa amb '%ml' a PostgreSQL; amb regex n'hi ha prou de canviar ~ per ~*.
  3. Mantenir-ho. Cinc condicions encadenades amb AND creixen a deu tan bon punt el catàleg afegeixi cl i oz; l'expressió regular només afegeix dues alternatives dins del parèntesi.

Aquest és el criteri de tria: LIKE mentre la condició sigui una sola forma fixa; regex quan apareguin alternatives, repeticions o classes de caràcters.

Conclusió

Ja saps buscar per patrons i, sobretot, saps quan no ho has de fer:

  • LIKE compara contra un patró, no contra un valor, i retorna un booleà combinable amb AND, OR i NOT. NOT LIKE n'és la negació, amb la mateixa ceguesa davant dels NULL que <>.
  • Els dos comodins són % (zero o més caràcters) i _ (exactament un). Un patró sense comodins equival a =, i el patró s'ancora sempre a la cadena completa.
  • A PostgreSQL LIKE distingeix majúscules. Les tres sortides són ILIKE (còmode però no estàndard), LOWER(col) LIKE LOWER(pat) (portable) i una colació no determinista (decisió d'esquema). A MySQL el comportament per defecte és el contrari i a SQLite només s'aplica a ASCII.
  • Per buscar un % o un _ literals cal escapar-los: \% per defecte a PostgreSQL, o el caràcter que declaris amb ESCAPE, que és la forma portable.
  • Quan la condició inclou alternatives, repeticions o classes de caràcters, LIKE es queda curt: hi entren SIMILAR TO (estàndard, poc usat) i sobretot les expressions regulars ~, ~*, !~, !~* de PostgreSQL, amb les quals has validat els 15 correus i detectat els 4 productes sense gramatge.
  • El rendiment depèn del primer caràcter del patró: 'text%' pot fer servir un índex B-tree; '%text%' obliga a llegir la taula sencera. Per a aquest cas hi ha pg_trgm (mòdul 8), i quan el que necessites és un cercador de debò, la cerca de text complet.

A la lliçó següent, operadors IN i BETWEEN, continuaràs afinant el filtratge però per un altre camí: en comptes de patrons de text, llistes de valors i rangs. Veuràs com IN substitueix una cadena interminable d'OR, com BETWEEN compacta un rang incloent-hi sempre tots dos extrems, i et trobaràs amb un dels errors més cars de tot SQL: NOT IN amb una llista que conté un NULL no retorna "la resta", retorna exactament zero files.

◄ Anterior
◄
Següent ►
►

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2026. Tots els drets reservats