Cerrábamos el módulo 5 con una lista de carencias: sigues devolviendo nombre y apellidos en dos columnas cuando quieres una, sigues sin poder poner un texto en mayúsculas, sin extraer el gramaje de un nombre de producto y sin sacar el dominio de un correo. Este módulo empieza por ahí, porque el texto es lo que más se manipula al presentar resultados y lo que más divergencias tiene entre motores. Pero antes de la primera función hay que aclarar una confusión que arrastra mucha gente durante años: la diferencia entre una función escalar y una función de agregación. Las de agregación las viste en 04-04; las de este módulo son de la otra familia. Si esa distinción te queda clara, el resto es vocabulario.

1. Escalar frente a agregada: una fila entra, una fila sale
2. Medir y cambiar la caja: LENGTH, UPPER, LOWER, INITCAP
3. Limpiar y rellenar: TRIM, LPAD, RPAD
4. Extraer, localizar y trocear
5. Componer texto: CONCAT_WS, FORMAT y el || de 02-02
6. Extracción con expresiones regulares
7. Casos reales de TiendaVerde
8. Funciones sobre columnas e índices
9. Tabla comparativa por motor
10. Errores Comunes y Consejos
11. Ejercicios
12. Conclusión

Una función escalar recibe uno o varios valores de una misma fila y devuelve un valor para esa fila. Si la consulta lee 4 filas, el resultado tiene 4 filas.

Una función de agregación recibe un conjunto de filas y devuelve un solo valor:

Una sola fila. Y fíjate en MAX(LENGTH(nombre)): combina las dos familias. Primero LENGTH se aplica a cada fila (escalar), después MAX colapsa los cuatro resultados en uno (agregada). El orden nunca es al revés.

La regla en una frase: una función escalar transforma; una de agregación resume. UPPER(nombre) transforma cada nombre; COUNT(nombre) resume todos en un número. Todas las funciones del módulo 6 son escalares.

Los dos primeros números —31 y 32— resumen una fuente inagotable de errores: en UTF-8, un carácter acentuado ocupa dos bytes.

El primer nombre no tiene tildes y los dos números coinciden; los otros dos, sí. Cuando VARCHAR(n) limita caracteres (PostgreSQL) esto da igual; cuando limita bytes (MySQL con ciertos tipos, Oracle con VARCHAR2(n BYTE)) es la causa clásica del value too long con nombres españoles.

Tres avisos sobre INITCAP: capitaliza todas las palabras, incluidas preposiciones (Miel De Azahar, no Miel de azahar), lo que para títulos comerciales queda mal y para normalizar ciudades es perfecto; respeta los acentos (INITCAP('CEPILLO DE DIENTES DE BAMBÚ') → Cepillo De Dientes De Bambú); y no existe en MySQL, SQLite ni SQL Server, siendo la función más echada de menos al portar código desde PostgreSQL u Oracle.

UPPER y LOWER tratan bien las tildes porque dependen de la colación de la base, la misma es-ES-x-icu que configuraste para el ORDER BY en 02-05.

TRIM(BOTH 'x' FROM s) es sintaxis SQL estándar, no una llamada normal, y por eso lleva palabras clave en lugar de comas. Se usa al limpiar importaciones: ceros a la izquierda de un código, comillas sobrantes de un CSV, puntos finales.

Dos trampas. TRIM(BOTH 'ab' FROM s) no quita la cadena 'ab': quita cualquiera de los caracteres a o b. Y LPAD garantiza longitud exacta: si la cadena es más larga la recorta (LPAD('Valencia', 5, '.') → 'Valen').

Tres cosas que hay que memorizar: las posiciones empiezan en 1, no en 0 (si vienes de un lenguaje de programación, es el error de la primera semana); POSITION y STRPOS devuelven 0 cuando no encuentran, no NULL, así que puedes compararlos sin arrastrar la lógica de tres valores de 04-03; y SPLIT_PART devuelve la cadena vacía si el trozo n no existe, tampoco NULL.

SPLIT_PART es una de las funciones más útiles de PostgreSQL y no tiene equivalente directo en la mayoría de motores. Lo que con STRPOS + SUBSTRING + un - 1 fácil de olvidar cuesta dos expresiones anidadas, con ella es una llamada:

En 02-02 usaste || para juntar nombre y apellidos, y en 04-03 descubriste su trampa: si un operando es NULL, el resultado entero es NULL. Aquí están las alternativas.

CONCAT_WS (With Separator) no solo ignora los nulos: tampoco pone el separador donde no hay valor. Retomamos el LEFT JOIN reflexivo de 04-03:

Mira las filas 1 y 4: recomendador_pipe es *(null)* y recomendador_ws es la cadena vacía.

La conclusión honesta: CONCAT_WS resuelve el caso "uno de los campos falta". No resuelve "no hay nadie ahí", que necesita un texto explícito como 'Registro directo'. Para eso hace falta COALESCE, en 06-04.

FORMAT construye plantillas al estilo de printf:

Ventajas sobre ||: convierte los tipos sola (no hace falta ::TEXT), la plantilla se lee de un vistazo y %s con un argumento NULL produce la cadena vacía en lugar de anular todo. Los marcadores %I (identificador) y %L (literal entrecomillado) sirven para generar SQL dinámico seguro y aparecerán en el módulo 10.

En 04-01 usaste LIKE, ILIKE y ~ para decidir si una cadena encaja con un patrón. Aquí las expresiones regulares hacen otra cosa: extraer y sustituir.

El caso práctico: sacar el gramaje del nombre del producto. Los nombres de TiendaVerde terminan con formato (500 ml, 30 g, 1 kg, 20 uds) salvo cuatro que no lo llevan.

Los cuatro productos sin gramaje —11, 12, 13 y 18— devuelven NULL en SUBSTRING y el nombre intacto en REGEXP_REPLACE: si no hay coincidencia, no hay sustitución.

Detalle de la alternancia (ml|kg|uds|g|L): kg va antes que g. Al revés, el motor encajaría la g de kg y el resultado sería erróneo. La alternancia prueba las opciones en el orden escrito.

Trampa de REGEXP_MATCHES: devuelve un conjunto de filas, no un valor; puesta en el SELECT, las filas sin coincidencia desaparecen (los 20 productos quedarían en 16). Para un valor por fila usa SUBSTRING(… FROM patron) o, desde PostgreSQL 15, REGEXP_SUBSTR(s, patron).

SPLIT_PART(apellidos, ' ', 1) aprovecha que los dos apellidos van juntos separados por un espacio: es el "backfill que parte una columna" anunciado en 05-06.

Conviven las dos familias de la sección 1: SPLIT_PART es escalar y se calcula fila a fila; COUNT(*) resume cada grupo. Y el GROUP BY repite la expresión completa, no el alias: es el "agrupar por expresión" que 04-05 dejó apuntado.

Los datos de TiendaVerde están limpios, pero un fichero de importación real nunca lo está. La receta canónica es una cadena de tres funciones, en este orden:

INITCAP(LOWER(TRIM(x))) convierte ' vaLENcia ', 'VALENCIA' y 'valencia' en el mismo 'Valencia': TRIM quita los bordes, LOWER iguala la caja y INITCAP la reconstruye. Es el patrón que usarás siempre que agrupes por una columna de texto introducida a mano.

Aviso importante: esto es enmascaramiento de presentación, no anonimización. El dato original sigue íntegro en la tabla y cualquiera con acceso lo ve. La anonimización real —seudonimización, agregación con umbral mínimo, borrado— es una cuestión legal (RGPD) y de arquitectura, no de funciones de cadena. Úsalo para que un informe no muestre correos completos, nunca como sustituto de una política de protección de datos.

Una advertencia para el futuro. Un filtro como WHERE LOWER(email) = '[email protected]' devuelve la fila correcta, pero al envolver la columna en una función el motor deja de comparar valores de la columna y pasa a comparar valores calculados, que ningún índice normal contiene. La solución existe —el índice de expresión, CREATE INDEX … ON clientes (LOWER(email))— pero los índices son el módulo 8 y allí lo verás medido con EXPLAIN en 08-03. De momento quédate con la regla: una función sobre una columna en el WHERE tiene coste. En el SELECT no hay problema: transformar lo que ya has leído es gratis comparado con leerlo.

Dos trampas de portabilidad que cuestan tardes enteras. En Oracle, la cadena vacía '' es NULL: LENGTH('') devuelve NULL, no 0, y todo lo que aprendiste en 04-03 sobre distinguir '' de NULL no aplica allí. Y en SQL Server, LEN ignora los espacios finales: LEN('Valencia ') es 8, no 10; para contar de verdad hace falta DATALENGTH.

• Confundir escalar con agregada. LENGTH(nombre) da una fila por producto; MAX(LENGTH(nombre)) da una sola. Si el resultado tiene menos filas de las esperadas, mira si has metido un agregado sin querer.
• Contar desde 0. En SQL las posiciones de cadena empiezan en 1. Y LPAD garantiza longitud exacta, no mínima: si la cadena es más larga, la recorta.
• Usar || con columnas nulables. Un solo NULL anula la expresión entera. CONCAT_WS para juntar campos, COALESCE (06-04) para poner un texto por defecto.
• Creer que CONCAT_WS resuelve todos los nulos. Con todos los argumentos nulos devuelve '', que en un informe se lee como celda vacía. Y REGEXP_MATCHES en el SELECT hace desaparecer las filas sin coincidencia: usa SUBSTRING(… FROM patron).
• Ordenar mal la alternancia de una regex. (g|kg) encaja la g de kg. Lo más específico va primero.
• Confundir TRIM(BOTH 'ab' FROM s) con quitar la cadena 'ab'. Quita los caracteres a y b sueltos. Y ojo: en PostgreSQL LENGTH cuenta caracteres, en MySQL cuenta bytes.
• Consejo: normaliza siempre con INITCAP(LOWER(TRIM(x))), en ese orden, antes de agrupar por texto introducido a mano.
• Consejo: prefiere SPLIT_PART a STRPOS + SUBSTRING cuando el separador sea fijo: se lee mejor y no tiene el - 1 que todo el mundo olvida. Y usa FORMAT para plantillas de más de dos trozos.

Marketing quiere etiquetas para el catálogo. Para cada producto activo de las categorías 1 y 4, devuelve el id con formato TV-00001 (prefijo, guion y cinco dígitos con ceros a la izquierda), el nombre sin el gramaje y en formato título, el gramaje por separado (o NULL si no lo lleva) y la longitud en caracteres del nombre original. Ordena por id.

Atención al cliente necesita una vista de contacto ofuscada. Devuelve, para cada cliente, el nombre completo en una columna, las iniciales de nombre y de los dos apellidos (L.M.S. para Lucía Martínez Soler) y el correo con el usuario oculto salvo sus dos primeras letras. Añade el dominio de primer nivel (com, pt, fr) y, en una segunda consulta, cuenta cuántos clientes hay de cada uno. (Pista: los apellidos van separados por un espacio; SPLIT_PART y LEFT bastan.)

Un compañero ha escrito LEFT(apellidos, POSITION(' ' IN apellidos)) AS primer_apellido sobre clientes. (1) ¿Qué dos problemas tiene el resultado? Fíjate en los clientes 9 y 10. (2) Corrígelo usando POSITION. (3) Corrígelo usando SPLIT_PART y explica por qué esa versión no tiene ninguno de los dos problemas.

Solución 1

9 productos: 5 de Alimentación y 4 de Bebidas. El ::TEXT es obligatorio porque LPAD espera texto y id es entero (conversiones: 06-04). Y ahí se ve el defecto de INITCAP de la sección 2: Aceite De Oliva, con la preposición capitalizada; corregirlo requiere CASE (06-05).

Solución 2

Los dos clientes franceses tienen un solo apellido, y SPLIT_PART(apellidos, ' ', 2) devuelve la cadena vacía: de ahí el C.D.. con dos puntos seguidos. SPLIT_PART no falla ni devuelve NULL, devuelve '', y el || lo concatena sin protestar. Arreglarlo exige preguntar "¿hay segundo apellido?", es decir NULLIF (06-04) o CASE (06-05).

El recuento —GROUP BY SPLIT_PART(email, '.', 3) con COUNT(*)— da 11 clientes com, 2 fr y 2 pt, los mismos 15 repartidos que en la sección 7.2.

Solución 3

1. Ejecutada sobre los clientes 1, 9 y 10 da Martínez (con espacio final) para el primero y la cadena vacía para los otros dos.

• Problema A: POSITION devuelve la posición del espacio, así que LEFT se lo lleva también. Falta un - 1.
• Problema B: cuando no hay espacio, POSITION devuelve 0 y LEFT(s, 0) es la cadena vacía: los clientes 9 y 10, con un solo apellido, pierden el apellido entero. Con el - 1 sería aún peor, porque LEFT(s, -1) devuelve todo menos el último carácter: Duboi.

2 y 3. Con POSITION hay que garantizar que siempre haya un espacio; con SPLIT_PART no hace falta nada:

Ambas devuelven lo mismo:

La segunda no tiene ninguno de los dos problemas porque SPLIT_PART no trabaja con posiciones: trocea por el separador y devuelve el trozo pedido. Si no hay separador, el trozo 1 es la cadena entera. Ni - 1 que olvidar, ni caso especial que tratar. Esa es la moraleja: cuando existe una función que expresa tu intención, úsala en lugar de reconstruirla con aritmética de posiciones.

Ya sabes transformar texto:

• Distingues una función escalar de una de agregación: la primera transforma fila a fila, la segunda resume muchas filas en una. Todo el módulo 6 es escalar.
• Mides con LENGTH (caracteres) y OCTET_LENGTH (bytes), cambias la caja con UPPER, LOWER e INITCAP y normalizas con INITCAP(LOWER(TRIM(x))).
• Limpias con TRIM y sus variantes, rellenas con LPAD/RPAD (que también recortan), extraes con SUBSTRING/LEFT/RIGHT recordando que las posiciones empiezan en 1, localizas con POSITION/STRPOS (que devuelven 0 si no encuentran) y troceas con SPLIT_PART, la función que elimina toda la aritmética de posiciones.
• Compones con CONCAT_WS, que resuelve la trampa del NULL de 02-02 —salvo cuando todos los argumentos son nulos, caso que espera a COALESCE en 06-04— y con FORMAT para plantillas.
• Extraes y sustituyes con REGEXP_REPLACE y SUBSTRING(… FROM patron), distintos de los LIKE y ~ de 04-01, que solo decidían si algo encajaba. Y sabes que una función sobre una columna en el WHERE tiene un coste que se mide en el módulo 8.

En la lección siguiente, funciones numéricas, la misma idea aplicada a los números: ROUND con sus hermanas TRUNC, CEIL y FLOOR, la aritmética con MOD, POWER y ABS, y dos trampas que cuestan dinero de verdad. La primera: 10 / 3 no vale 3.33, vale 3, y hay tres formas distintas de arreglarlo. La segunda, más grave: ROUND(2.5) y ROUND(2.5::DOUBLE PRECISION) no devuelven lo mismo en PostgreSQL, y esa diferencia de un céntimo, multiplicada por un millón de líneas de factura, es la razón por la que en 01-04 se dijo que el dinero nunca se guarda en coma flotante. Vamos a demostrarlo.