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

Funcions de cadena

Següent ►
►

Tancàvem el mòdul 5 amb una llista de mancances: continues retornant nom i cognoms en dues columnes quan en vols una, continues sense poder posar un text en majúscules, sense extreure el gramatge d'un nom de producte i sense treure el domini d'un correu. Aquest mòdul comença per aquí, perquè el text és el que més es manipula a l'hora de presentar resultats i el que més divergències té entre motors. Però abans de la primera funció cal aclarir una confusió que molta gent arrossega durant anys: la diferència entre una funció escalar i una funció d'agregació. Les d'agregació les vas veure a 04-04; les d'aquest mòdul són de l'altra família. Si aquesta distinció et queda clara, la resta és vocabulari.

Contingut

  1. Escalar enfront d'agregada: una fila entra, una fila surt
  2. Mesurar i canviar la caixa: LENGTH, UPPER, LOWER, INITCAP
  3. Netejar i emplenar: TRIM, LPAD, RPAD
  4. Extreure, localitzar i trossejar
  5. Compondre text: CONCAT_WS, FORMAT i el || de 02-02
  6. Extracció amb expressions regulars
  7. Casos reals de BotigaVerda
  8. Funcions sobre columnes i índexs
  9. Taula comparativa per motor
  10. Errors habituals i consells
  11. Exercicis
  12. Conclusió

  1. Escalar enfront d'agregada: una fila entra, una fila surt

Una funció escalar rep un o diversos valors d'una mateixa fila i retorna un valor per a aquella fila. Si la consulta llegeix 4 files, el resultat té 4 files.

SELECT id,
       nom,
       LENGTH(nom) AS longitud
FROM productes
WHERE categoria_id = 4
ORDER BY id;
id nom longitud
14 Infusió de camamilla ecològica 20 u 35
15 Te verd matcha cerimonial 30 g 30
16 Kombutxa de gingebre 750 ml 27
17 Suc de taronja premsat en fred 1 L 34

Una funció d'agregació rep un conjunt de files i retorna un sol valor:

SELECT COUNT(*)         AS productes,
       MAX(LENGTH(nom)) AS longitud_maxima,
       MIN(LENGTH(nom)) AS longitud_minima
FROM productes
WHERE categoria_id = 4;
productes longitud_maxima longitud_minima
4 35 27

Una sola fila. I fixa't en MAX(LENGTH(nom)): combina les dues famílies. Primer LENGTH s'aplica a cada fila (escalar), després MAX col·lapsa els quatre resultats en un (agregada). L'ordre mai no és a l'inrevés.

Funció escalar Funció d'agregació
Entrada Els valors d'una fila Els valors de moltes files
Sortida Un valor per fila Un valor per grup
Files del resultat Les mateixes que hi havia Una per grup (o una de sola sense GROUP BY)
Exemples LENGTH, UPPER, ROUND, COALESCE COUNT, SUM, AVG, MIN, MAX
Pot anar al WHERE? Sí No (per a això hi ha HAVING, 04-06)
Lliçó Mòdul 6 04-04

La regla en una frase: una funció escalar transforma; una d'agregació resumeix. UPPER(nom) transforma cada nom; COUNT(nom) els resumeix tots en un número. Totes les funcions del mòdul 6 són escalars.

  1. Mesurar i canviar la caixa

Funció Què fa Exemple Resultat
LENGTH(s), CHAR_LENGTH(s) Nombre de caràcters (sinònims) LENGTH('Infusió de camamilla ecològica 20 u') 35
OCTET_LENGTH(s) Nombre de bytes OCTET_LENGTH('Infusió de camamilla ecològica 20 u') 37
UPPER(s) Tot a majúscules UPPER('Mel de tarongina crua 500 g') MEL DE TARONGINA CRUA 500 G
LOWER(s) Tot a minúscules LOWER('Te Verd Matcha') te verd matcha
INITCAP(s) Primera lletra de cada paraula en majúscula, la resta en minúscula INITCAP('mel de tarongina crua') Mel De Tarongina Crua

Els dos primers números —35 i 37— resumeixen una font inesgotable d'errors: en UTF-8, un caràcter accentuat ocupa dos bytes.

SELECT nom, LENGTH(nom) AS caracters, OCTET_LENGTH(nom) AS bytes
FROM productes WHERE id IN (1, 7, 14) ORDER BY id;
nom caracters bytes
Oli d'oliva verge extra 500 ml 30 30
Xampú sòlid de romaní 80 g 26 29
Infusió de camamilla ecològica 20 u 35 37

El primer nom no porta accents i els dos números coincideixen; els altres dos, sí. Quan VARCHAR(n) limita caràcters (PostgreSQL) això és indiferent; quan limita bytes (MySQL amb certs tipus, Oracle amb VARCHAR2(n BYTE)) és la causa clàssica del value too long amb noms catalans.

Tres avisos sobre INITCAP: posa en majúscula totes les paraules, preposicions incloses (Mel De Tarongina, no Mel de tarongina), cosa que per a títols comercials queda malament i per a normalitzar ciutats és perfecta; respecta els accents (INITCAP('RASPALL DE DENTS DE BAMBÚ') → Raspall De Dents De Bambú); i no existeix a MySQL, SQLite ni SQL Server, i és la funció que més s'enyora quan es porta codi des de PostgreSQL o Oracle.

UPPER i LOWER tracten bé els accents perquè depenen de la col·lació de la base, la mateixa ca-ES-x-icu que vas configurar per a l'ORDER BY a 02-05.

  1. Netejar i emplenar

Funció Què fa Exemple Resultat
TRIM(s) Treu espais als dos costats TRIM(' València ') 'València'
LTRIM(s) / RTRIM(s) Només esquerra / només dreta RTRIM(' València ') ' València'
TRIM(BOTH 'x' FROM s) Treu el caràcter indicat, no espais TRIM(BOTH '0' FROM '00123400') '1234'
TRIM(LEADING '0' FROM s) / TRIM(TRAILING '0' FROM s) Només un costat TRIM(LEADING '0' FROM '00123400') '123400'
LPAD(s, n, farciment) Emplena per l'esquerra fins a n caràcters LPAD('7', 5, '0') '00007'
RPAD(s, n, farciment) Emplena per la dreta RPAD('València', 12, '.') 'València....'

TRIM(BOTH 'x' FROM s) és sintaxi SQL estàndard, no una crida normal, i per això porta paraules clau en lloc de comes. S'utilitza en netejar importacions: zeros a l'esquerra d'un codi, cometes sobreres d'un CSV, punts finals.

Dues trampes. TRIM(BOTH 'ab' FROM s) no treu la cadena 'ab': treu qualsevol dels caràcters a o b. I LPAD garanteix una longitud exacta: si la cadena és més llarga la retalla (LPAD('València', 5, '.') → 'Valèn').

  1. Extreure, localitzar i trossejar

Funció Què fa Exemple Resultat
SUBSTRING(s FROM p FOR n) n caràcters des de la posició p SUBSTRING('Oli d''oliva' FROM 1 FOR 3) 'Oli'
SUBSTRING(s FROM p) Des de p fins al final (SUBSTRING(s, p, n) és la variant amb comes) SUBSTRING('Oli d''oliva verge extra 500 ml' FROM 25) '500 ml'
LEFT(s, n) / RIGHT(s, n) Primers / últims n caràcters RIGHT('València', 3) 'cia'
LEFT(s, -n) Tots menys els n últims LEFT('València', -3) 'Valèn'
POSITION(sub IN s) Posició de la primera aparició POSITION('oliva' IN 'Oli d''oliva') 7
STRPOS(s, sub) Igual, arguments a l'inrevés STRPOS('[email protected]', '@') 15
REPLACE(s, vell, nou) Substitueix totes les aparicions REPLACE('… extra 500 ml', '500 ml', '1 L') '… extra 1 L'
SPLIT_PART(s, sep, n) Trosseja per un separador, retorna el tros n SPLIT_PART('[email protected]', '@', 2) 'example.com'
REVERSE(s) / REPEAT(s, n) Inverteix / repeteix n vegades REPEAT('*', 5) '*****'

Tres coses que cal memoritzar: les posicions comencen a 1, no a 0 (si véns d'un llenguatge de programació, és l'error de la primera setmana); POSITION i STRPOS retornen 0 quan no troben res, no NULL, així que els pots comparar sense arrossegar la lògica de tres valors de 04-03; i SPLIT_PART retorna la cadena buida si el tros n no existeix, tampoc NULL.

SPLIT_PART és una de les funcions més útils de PostgreSQL i no té equivalent directe a la majoria de motors. El que amb STRPOS + SUBSTRING + un - 1 fàcil d'oblidar costa dues expressions imbricades, amb ella és una sola crida:

SELECT email,
       SPLIT_PART(email, '@', 1) AS usuari,
       SPLIT_PART(email, '@', 2) AS domini
FROM clients WHERE id IN (1, 7, 9) ORDER BY id;
email usuari domini
[email protected] lucia.martinez example.com
[email protected] sofia.moreira example.pt
[email protected] camille.dubois example.fr

  1. Compondre text: CONCAT_WS, FORMAT i el || de 02-02

A 02-02 vas fer servir || per ajuntar nom i cognoms, i a 04-03 en vas descobrir la trampa: si un operand és NULL, el resultat sencer és NULL. Aquí tens les alternatives.

Funció Què fa Exemple Resultat
a || b Concatena. Propaga el NULL 'Lucía' || NULL *(null)*
CONCAT(a, b, c) Concatena i ignora els NULL CONCAT('Lucía', NULL, ' Soler') 'Lucía Soler'
CONCAT_WS(sep, a, b, c) Concatena amb separador i ignora els NULL CONCAT_WS(' ', 'Lucía', NULL, 'Soler') 'Lucía Soler'
FORMAT(patro, …) Plantilla amb marcadors %s FORMAT('%s: %s €', 'Oli', 12.50) 'Oli: 12.50 €'

CONCAT_WS (With Separator) no només ignora els nuls: tampoc no posa el separador on no hi ha valor. Reprenem el LEFT JOIN reflexiu de 04-03:

SELECT c.id,
       CONCAT_WS(' ', c.nom, c.cognoms)         AS client,
       ref.nom || ' ' || ref.cognoms            AS recomanador_pipe,
       CONCAT_WS(' ', ref.nom, ref.cognoms)     AS recomanador_ws
FROM clients AS c
LEFT JOIN clients AS ref ON c.referit_per_id = ref.id
WHERE c.id <= 5
ORDER BY c.id;
id client recomanador_pipe recomanador_ws
1 Lucía Martínez Soler (null)
2 Carlos Ferrer Ibáñez Lucía Martínez Soler Lucía Martínez Soler
3 Marta Sanchis Gil Lucía Martínez Soler Lucía Martínez Soler
4 Javier Ortega Ruiz (null)
5 Ana Belmonte Roca Carlos Ferrer Ibáñez Carlos Ferrer Ibáñez

Mira les files 1 i 4: recomanador_pipe és *(null)* i recomanador_ws és la cadena buida.

La conclusió honesta: CONCAT_WS resol el cas "un dels camps falta". No resol "no hi ha ningú allà", que necessita un text explícit com ara 'Registre directe'. Per a això cal COALESCE, a 06-04.

FORMAT construeix plantilles a l'estil de printf:

SELECT FORMAT('Producte %s: %s unitats a %s €', id, stock, preu) AS fitxa
FROM productes WHERE id IN (1, 13) ORDER BY id;
fitxa
Producte 1: 120 unitats a 12.50 €
Producte 13: 0 unitats a 13.75 €

Avantatges sobre ||: converteix els tipus tota sola (no cal ::TEXT), la plantilla es llegeix d'un cop d'ull i %s amb un argument NULL produeix la cadena buida en lloc d'anul·lar-ho tot. Els marcadors %I (identificador) i %L (literal entre cometes) serveixen per generar SQL dinàmic segur i apareixeran al mòdul 10.

  1. Extracció amb expressions regulars

A 04-01 vas fer servir LIKE, ILIKE i ~ per decidir si una cadena encaixa amb un patró. Aquí les expressions regulars fan una altra cosa: extreure i substituir.

Funció Què fa Exemple Resultat
REGEXP_REPLACE(s, patro, repl) Substitueix la primera coincidència REGEXP_REPLACE('a1b2c3', '[0-9]', '#') 'a#b2c3'
REGEXP_REPLACE(s, patro, repl, 'g') Substitueix totes (bandera global) REGEXP_REPLACE('a1b2c3', '[0-9]', '#', 'g') 'a#b#c#'
REGEXP_MATCHES(s, patro) Retorna un array amb els grups capturats REGEXP_MATCHES('500 ml', '(\d+) (\w+)') {500,ml}
SUBSTRING(s FROM patro) Extreu la primera coincidència com a text SUBSTRING('Te verd 30 g' FROM '\d+ ?\w+$') '30 g'

El cas pràctic: treure el gramatge del nom del producte. Els noms de BotigaVerda acaben amb el format (500 ml, 30 g, 1 kg, 20 u) llevat de quatre que no en porten.

SELECT id,
       nom,
       SUBSTRING(nom FROM '[0-9]+ ?(ml|kg|u|g|L)$')         AS gramatge,
       REGEXP_REPLACE(nom, '\s*[0-9]+ ?(ml|kg|u|g|L)$', '') AS nom_curt
FROM productes
WHERE id IN (1, 2, 11, 14, 18)
ORDER BY id;
id nom gramatge nom_curt
1 Oli d'oliva verge extra 500 ml 500 ml Oli d'oliva verge extra
2 Arròs integral ecològic 1 kg 1 kg Arròs integral ecològic
11 Fregall vegetal de lufa (pack 3) (null) Fregall vegetal de lufa (pack 3)
14 Infusió de camamilla ecològica 20 u 20 u Infusió de camamilla ecològica
18 Raspall de dents de bambú (null) Raspall de dents de bambú

Els quatre productes sense gramatge —11, 12, 13 i 18— retornen NULL a SUBSTRING i el nom intacte a REGEXP_REPLACE: si no hi ha coincidència, no hi ha substitució.

Detall de l'alternança (ml|kg|u|g|L): kg va abans que g. A l'inrevés, el motor encaixaria la g de kg i el resultat seria erroni. L'alternança prova les opcions en l'ordre escrit.

Trampa de REGEXP_MATCHES: retorna un conjunt de files, no un valor; posada al SELECT, les files sense coincidència desapareixen (els 20 productes quedarien en 16). Per obtenir un valor per fila fes servir SUBSTRING(… FROM patro) o, des de PostgreSQL 15, REGEXP_SUBSTR(s, patro).

  1. Casos reals de BotigaVerda

7.1. Fitxa de client

SELECT id,
       CONCAT_WS(' ', nom, cognoms)                 AS nom_complet,
       LEFT(nom, 1) || '.' || LEFT(cognoms, 1) || '.' AS inicials,
       UPPER(SPLIT_PART(cognoms, ' ', 1))           AS primer_cognom,
       SPLIT_PART(email, '@', 2)                    AS domini
FROM clients
WHERE id <= 4
ORDER BY id;
id nom_complet inicials primer_cognom domini
1 Lucía Martínez Soler L.M. MARTÍNEZ example.com
2 Carlos Ferrer Ibáñez C.F. FERRER example.com
3 Marta Sanchis Gil M.S. SANCHIS example.com
4 Javier Ortega Ruiz J.O. ORTEGA example.com

SPLIT_PART(cognoms, ' ', 1) aprofita que els dos cognoms van junts separats per un espai: és el "backfill que parteix una columna" anunciat a 05-06.

7.2. Repartiment de clients per domini

SELECT SPLIT_PART(email, '@', 2) AS domini,
       COUNT(*)                  AS clients
FROM clients
GROUP BY SPLIT_PART(email, '@', 2)
ORDER BY clients DESC, domini;
domini clients
example.com 11
example.fr 2
example.pt 2

Hi conviuen les dues famílies de la secció 1: SPLIT_PART és escalar i es calcula fila a fila; COUNT(*) resumeix cada grup. I el GROUP BY repeteix l'expressió completa, no l'àlies: és l'"agrupar per expressió" que 04-05 va deixar apuntat.

7.3. Normalitzar ciutats

Les dades de BotigaVerda estan netes, però un fitxer d'importació real no ho està mai. La recepta canònica és una cadena de tres funcions, en aquest ordre:

SELECT INITCAP(LOWER(TRIM('  vaLÈNcia  '))) AS ciutat_normalitzada;
ciutat_normalitzada
València

INITCAP(LOWER(TRIM(x))) converteix ' vaLÈNcia ', 'VALÈNCIA' i 'valència' en el mateix 'València': TRIM treu les vores, LOWER iguala la caixa i INITCAP la reconstrueix. És el patró que faràs servir sempre que agrupis per una columna de text introduïda a mà.

7.4. Ofuscar el correu electrònic

SELECT id,
       email,
       LEFT(SPLIT_PART(email, '@', 1), 2)
         || REPEAT('*', LENGTH(SPLIT_PART(email, '@', 1)) - 2)
         || '@' || SPLIT_PART(email, '@', 2) AS email_ofuscat
FROM clients
WHERE id <= 3
ORDER BY id;
id email email_ofuscat
1 [email protected] lu************@example.com
2 [email protected] ca***********@example.com
3 [email protected] ma***********@example.com

Avís important: això és emmascarament de presentació, no anonimització. La dada original continua íntegra a la taula i qualsevol persona amb accés la veu. L'anonimització real —pseudonimització, agregació amb llindar mínim, esborrat— és una qüestió legal (RGPD) i d'arquitectura, no de funcions de cadena. Fes-ho servir perquè un informe no mostri correus sencers, mai com a substitut d'una política de protecció de dades.

  1. Funcions sobre columnes i índexs

Un advertiment per al futur. Un filtre com ara WHERE LOWER(email) = '[email protected]' retorna la fila correcta, però en embolcallar la columna amb una funció el motor deixa de comparar valors de la columna i passa a comparar valors calculats, que cap índex normal no conté. La solució existeix —l'índex d'expressió, CREATE INDEX … ON clients (LOWER(email))— però els índexs són el mòdul 8 i allà ho veuràs mesurat amb EXPLAIN a 08-03. De moment queda't amb la regla: una funció sobre una columna al WHERE té un cost. Al SELECT no hi ha problema: transformar allò que ja has llegit és gratis comparat amb llegir-ho.

  1. Taula comparativa per motor

Tasca PostgreSQL 16 MySQL 8 SQLite SQL Server Oracle
Longitud en caràcters LENGTH, CHAR_LENGTH CHAR_LENGTH (LENGTH dona bytes) LENGTH LEN (ignora espais finals) LENGTH
Longitud en bytes OCTET_LENGTH LENGTH — DATALENGTH LENGTHB
Subcadena SUBSTRING(s FROM p FOR n) SUBSTRING, SUBSTR SUBSTR SUBSTRING(s,p,n) (n obligatori) SUBSTR(s,p,n)
Primers / últims LEFT, RIGHT LEFT, RIGHT SUBSTR LEFT, RIGHT SUBSTR
Capitalitzar paraules INITCAP no existeix no existeix no existeix INITCAP
Concatenar ||, CONCAT, CONCAT_WS CONCAT, CONCAT_WS (|| només amb PIPES_AS_CONCAT) || +, CONCAT, CONCAT_WS (2012+) ||, CONCAT (només 2 arguments)
Posició d'una subcadena POSITION, STRPOS LOCATE, INSTR INSTR CHARINDEX INSTR
Emplenar / retallar caràcter LPAD, RPAD, TRIM(BOTH 'x' FROM s) igual no existeix (printf); TRIM(s,'x') REPLICATE; TRIM('x' FROM s) (2022+) LPAD, RPAD, TRIM
Trossejar per separador SPLIT_PART SUBSTRING_INDEX no existeix STRING_SPLIT (retorna taula) REGEXP_SUBSTR
Substituir amb regex REGEXP_REPLACE REGEXP_REPLACE (8.0+) no existeix no existeix REGEXP_REPLACE
Repetir / invertir REPEAT, REVERSE REPEAT, REVERSE no existeixen REPLICATE, REVERSE RPAD, REVERSE

Dues trampes de portabilitat que costen tardes senceres. A Oracle, la cadena buida '' és NULL: LENGTH('') retorna NULL, no 0, i tot el que vas aprendre a 04-03 sobre distingir '' de NULL no s'hi aplica. I a SQL Server, LEN ignora els espais finals: LEN('València ') és 8, no 10; per comptar de debò cal DATALENGTH.

Errors habituals i consells

  • Confondre escalar amb agregada. LENGTH(nom) dona una fila per producte; MAX(LENGTH(nom)) en dona una de sola. Si el resultat té menys files de les esperades, mira si hi has ficat un agregat sense voler.
  • Comptar des de 0. En SQL les posicions de cadena comencen a 1. I LPAD garanteix una longitud exacta, no mínima: si la cadena és més llarga, la retalla.
  • Fer servir || amb columnes que admeten nuls. Un sol NULL anul·la l'expressió sencera. CONCAT_WS per ajuntar camps, COALESCE (06-04) per posar-hi un text per omissió.
  • Creure que CONCAT_WS resol tots els nuls. Amb tots els arguments nuls retorna '', que en un informe es llegeix com una cel·la buida. I REGEXP_MATCHES al SELECT fa desaparèixer les files sense coincidència: fes servir SUBSTRING(… FROM patro).
  • Ordenar malament l'alternança d'una regex. (g|kg) encaixa la g de kg. El més específic va primer.
  • Confondre TRIM(BOTH 'ab' FROM s) amb treure la cadena 'ab'. Treu els caràcters a i b solts. I compte: a PostgreSQL LENGTH compta caràcters, a MySQL compta bytes.
  • Consell: normalitza sempre amb INITCAP(LOWER(TRIM(x))), en aquest ordre, abans d'agrupar per text introduït a mà.
  • Consell: prefereix SPLIT_PART a STRPOS + SUBSTRING quan el separador sigui fix: es llegeix millor i no té el - 1 que tothom oblida. I fes servir FORMAT per a plantilles de més de dos trossos.

Exercicis

Exercici 1

Màrqueting vol etiquetes per al catàleg. Per a cada producte actiu de les categories 1 i 4, retorna l'id amb format BV-00001 (prefix, guionet i cinc dígits amb zeros a l'esquerra), el nom sense el gramatge i en format títol, el gramatge per separat (o NULL si no en porta) i la longitud en caràcters del nom original. Ordena per id.

Exercici 2

Atenció al client necessita una vista de contacte ofuscada. Retorna, per a cada client, el nom complet en una columna, les inicials del nom i dels dos cognoms (L.M.S. per a Lucía Martínez Soler) i el correu amb l'usuari amagat llevat de les seves dues primeres lletres. Afegeix-hi el domini de primer nivell (com, pt, fr) i, en una segona consulta, compta quants clients hi ha de cadascun. (Pista: els cognoms van separats per un espai; amb SPLIT_PART i LEFT n'hi ha prou.)

Exercici 3

Un company ha escrit LEFT(cognoms, POSITION(' ' IN cognoms)) AS primer_cognom sobre clients. (1) Quins dos problemes té el resultat? Fixa't en els clients 9 i 10. (2) Corregeix-ho fent servir POSITION. (3) Corregeix-ho fent servir SPLIT_PART i explica per què aquesta versió no té cap dels dos problemes.

Solucions

Solució 1

SELECT 'BV-' || LPAD(id::TEXT, 5, '0')                                 AS referencia,
       INITCAP(REGEXP_REPLACE(nom, '\s*[0-9]+ ?(ml|kg|u|g|L)$', ''))   AS titol,
       SUBSTRING(nom FROM '[0-9]+ ?(ml|kg|u|g|L)$')                    AS gramatge,
       LENGTH(nom)                                                     AS longitud
FROM productes
WHERE actiu = TRUE
  AND categoria_id IN (1, 4)
ORDER BY id;
referencia titol gramatge longitud
BV-00001 Oli D'Oliva Verge Extra 500 ml 30
BV-00002 Arròs Integral Ecològic 1 kg 28
BV-00003 Mel De Tarongina Crua 500 g 27
BV-00004 Pasta D'Espelta 500 g 21
BV-00005 Tomàquet Triturat Ecològic 400 g 32
BV-00014 Infusió De Camamilla Ecològica 20 u 35
BV-00015 Te Verd Matcha Cerimonial 30 g 30
BV-00016 Kombutxa De Gingebre 750 ml 27
BV-00017 Suc De Taronja Premsat En Fred 1 L 34

9 productes: 5 d'Alimentació i 4 de Begudes. El ::TEXT és obligatori perquè LPAD espera text i id és un enter (conversions: 06-04). I aquí es veu el defecte d'INITCAP de la secció 2: Mel De Tarongina, amb la preposició en majúscula, i fins i tot D'Oliva, on l'apòstrof compta com a separador de paraules; corregir-ho requereix CASE (06-05).

Solució 2

SELECT CONCAT_WS(' ', nom, cognoms) AS client,
       LEFT(nom, 1) || '.'
         || LEFT(SPLIT_PART(cognoms, ' ', 1), 1) || '.'
         || LEFT(SPLIT_PART(cognoms, ' ', 2), 1) || '.' AS inicials,
       LEFT(SPLIT_PART(email, '@', 1), 2)
         || REPEAT('*', LENGTH(SPLIT_PART(email, '@', 1)) - 2)
         || '@' || SPLIT_PART(email, '@', 2)            AS contacte,
       SPLIT_PART(email, '.', 3)                        AS pais_domini
FROM clients
WHERE id IN (1, 9, 10)
ORDER BY id;
client inicials contacte pais_domini
Lucía Martínez Soler L.M.S. lu************@example.com com
Camille Dubois C.D.. ca************@example.fr fr
Julien Moreau J.M.. ju***********@example.fr fr

Els dos clients francesos tenen un sol cognom, i SPLIT_PART(cognoms, ' ', 2) retorna la cadena buida: d'aquí el C.D.. amb dos punts seguits. SPLIT_PART no falla ni retorna NULL, retorna '', i el || ho concatena sense protestar. Arreglar-ho exigeix preguntar "hi ha segon cognom?", és a dir NULLIF (06-04) o CASE (06-05).

El recompte —GROUP BY SPLIT_PART(email, '.', 3) amb COUNT(*)— dona 11 clients com, 2 fr i 2 pt, els mateixos 15 repartits que a la secció 7.2.

Solució 3

1. Executada sobre els clients 1, 9 i 10 dona Martínez (amb espai final) per al primer i la cadena buida per als altres dos.

  • Problema A: POSITION retorna la posició de l'espai, així que LEFT també se l'emporta. Hi falta un - 1.
  • Problema B: quan no hi ha espai, POSITION retorna 0 i LEFT(s, 0) és la cadena buida: els clients 9 i 10, amb un sol cognom, perden el cognom sencer. Amb el - 1 encara seria pitjor, perquè LEFT(s, -1) retorna tot menys l'últim caràcter: Duboi.

2 i 3. Amb POSITION cal garantir que sempre hi hagi un espai; amb SPLIT_PART no cal res:

-- ✅ CORRECTA, però necessita un truc que cal comentar
SELECT id, cognoms,
       LEFT(cognoms || ' ', POSITION(' ' IN cognoms || ' ') - 1) AS primer_cognom
FROM clients WHERE id IN (1, 9, 10) ORDER BY id;

-- ✅ CORRECTA i llegible
SELECT id, cognoms, SPLIT_PART(cognoms, ' ', 1) AS primer_cognom
FROM clients WHERE id IN (1, 9, 10) ORDER BY id;

Totes dues retornen el mateix:

id cognoms primer_cognom
1 Martínez Soler Martínez
9 Dubois Dubois
10 Moreau Moreau

La segona no té cap dels dos problemes perquè SPLIT_PART no treballa amb posicions: trosseja pel separador i retorna el tros demanat. Si no hi ha separador, el tros 1 és la cadena sencera. Ni - 1 per oblidar, ni cas especial per tractar. Aquesta és la moralitat: quan existeix una funció que expressa la teva intenció, fes-la servir en lloc de reconstruir-la amb aritmètica de posicions.

Conclusió

Ja saps transformar text:

  • Distingeixes una funció escalar d'una d'agregació: la primera transforma fila a fila, la segona resumeix moltes files en una. Tot el mòdul 6 és escalar.
  • Mesures amb LENGTH (caràcters) i OCTET_LENGTH (bytes), canvies la caixa amb UPPER, LOWER i INITCAP i normalitzes amb INITCAP(LOWER(TRIM(x))).
  • Neteges amb TRIM i les seves variants, emplenes amb LPAD/RPAD (que també retallen), extreus amb SUBSTRING/LEFT/RIGHT recordant que les posicions comencen a 1, localitzes amb POSITION/STRPOS (que retornen 0 si no troben res) i trosseges amb SPLIT_PART, la funció que elimina tota l'aritmètica de posicions.
  • Composes amb CONCAT_WS, que resol la trampa del NULL de 02-02 —llevat de quan tots els arguments són nuls, cas que espera COALESCE a 06-04— i amb FORMAT per a plantilles.
  • Extreus i substitueixes amb REGEXP_REPLACE i SUBSTRING(… FROM patro), diferents dels LIKE i ~ de 04-01, que només decidien si una cosa encaixava. I saps que una funció sobre una columna al WHERE té un cost que es mesura al mòdul 8.

A la lliçó següent, funcions numèriques, la mateixa idea aplicada als números: ROUND amb les seves germanes TRUNC, CEIL i FLOOR, l'aritmètica amb MOD, POWER i ABS, i dues trampes que costen diners de debò. La primera: 10 / 3 no val 3.33, val 3, i hi ha tres maneres diferents d'arreglar-ho. La segona, més greu: ROUND(2.5) i ROUND(2.5::DOUBLE PRECISION) no retornen el mateix a PostgreSQL, i aquesta diferència d'un cèntim, multiplicada per un milió de línies de factura, és la raó per la qual a 01-04 es va dir que els diners mai no es desen en coma flotant. Ho demostrarem.

◄ 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