Tutorial SQL: Funciones de texto

Concatenación y extracción

La manipulación de cadenas de texto es una tarea fundamental en SQL que nos permite transformar, combinar y extraer información de los datos almacenados. En esta sección exploraremos las funciones de concatenación y extracción de texto, que son esenciales para el procesamiento de datos textuales en MySQL y PostgreSQL.

Concatenación de cadenas

La concatenación nos permite combinar dos o más cadenas de texto en una sola. Tanto MySQL como PostgreSQL ofrecen diferentes métodos para realizar esta operación.

Operador de concatenación

En MySQL, podemos usar el operador CONCAT() para unir cadenas:

SELECT CONCAT('Hola', ' ', 'mundo') AS saludo;
-- Resultado: 'Hola mundo'

Para casos más complejos con múltiples columnas:

SELECT CONCAT(nombre, ' ', apellido) AS nombre_completo 
FROM empleados;

PostgreSQL ofrece dos alternativas para la concatenación:

-- Usando el operador ||
SELECT 'Hola' || ' ' || 'mundo' AS saludo;

-- Usando la función CONCAT
SELECT CONCAT('Hola', ' ', 'mundo') AS saludo;

Concatenación con separadores

Si necesitamos unir múltiples valores con un mismo separador, podemos usar CONCAT_WS() (Concatenate With Separator):

-- MySQL y PostgreSQL
SELECT CONCAT_WS(' ', 'Juan', 'Carlos', 'Pérez') AS nombre_completo;
-- Resultado: 'Juan Carlos Pérez'

Esta función es especialmente útil cuando trabajamos con múltiples columnas:

SELECT CONCAT_WS(', ', calle, numero, ciudad, codigo_postal) AS direccion_completa
FROM direcciones;

Una ventaja importante de CONCAT_WS() es que ignora automáticamente los valores NULL, lo que evita errores comunes en la concatenación.

Extracción de texto

Las funciones de extracción nos permiten obtener partes específicas de una cadena de texto. Estas funciones son fundamentales para el análisis y procesamiento de datos textuales.

Extracción por posición

Para extraer caracteres desde una posición específica:

-- MySQL: SUBSTRING(cadena, posición, longitud)
SELECT SUBSTRING('Programación SQL', 13, 3) AS extracto;
-- Resultado: 'SQL'

-- PostgreSQL: SUBSTRING(cadena FROM posición FOR longitud)
SELECT SUBSTRING('Programación SQL' FROM 13 FOR 3) AS extracto;
-- Resultado: 'SQL'

También podemos usar la función SUBSTR() que funciona de manera similar:

-- MySQL y PostgreSQL
SELECT SUBSTR('Programación SQL', 1, 12) AS extracto;
-- Resultado: 'Programación'

Es importante recordar que en SQL, la indexación de caracteres comienza en 1, no en 0 como en muchos lenguajes de programación.

Extracción desde el inicio o final

Para extraer caracteres desde el inicio de una cadena:

-- MySQL y PostgreSQL
SELECT LEFT('Programación SQL', 12) AS extracto;
-- Resultado: 'Programación'

Para extraer caracteres desde el final:

-- MySQL y PostgreSQL
SELECT RIGHT('Programación SQL', 3) AS extracto;
-- Resultado: 'SQL'

Estas funciones son útiles cuando necesitamos extraer prefijos o sufijos de valores textuales.

Aplicaciones prácticas

Veamos algunos ejemplos prácticos de cómo usar estas funciones en situaciones reales:

Formateo de nombres

-- Crear iniciales a partir de nombres completos
SELECT 
    nombre,
    apellido,
    CONCAT(LEFT(nombre, 1), '. ', LEFT(apellido, 1), '.') AS iniciales
FROM empleados;

Extracción de información de correos electrónicos

-- Extraer el nombre de usuario y dominio de un email
SELECT 
    email,
    SUBSTRING_INDEX(email, '@', 1) AS usuario,
    SUBSTRING_INDEX(email, '@', -1) AS dominio
FROM usuarios;

En PostgreSQL, podemos usar SPLIT_PART():

SELECT 
    email,
    SPLIT_PART(email, '@', 1) AS usuario,
    SPLIT_PART(email, '@', 2) AS dominio
FROM usuarios;

Formateo de códigos y referencias

-- Crear un código de producto con formato específico
SELECT 
    id_producto,
    nombre_producto,
    CONCAT('PROD-', LPAD(id_producto, 5, '0')) AS codigo_producto
FROM productos;

La función LPAD() rellena una cadena por la izquierda hasta alcanzar una longitud determinada, lo que es útil para crear códigos con formato uniforme.

Manejo de valores NULL en concatenación

Un aspecto importante a considerar es el comportamiento con valores NULL:

-- MySQL: CONCAT() devuelve NULL si algún argumento es NULL
SELECT CONCAT('Precio: ', NULL, ' euros') AS resultado;
-- Resultado: NULL

-- Solución en MySQL: usar IFNULL o COALESCE
SELECT CONCAT('Precio: ', IFNULL(precio, 'no disponible'), ' euros') 
FROM productos;

-- PostgreSQL: el operador || trata NULL como cadena vacía
SELECT 'Precio: ' || NULL || ' euros' AS resultado;
-- Resultado: 'Precio:  euros'

Para un comportamiento consistente entre sistemas, es recomendable usar COALESCE() que funciona igual en ambos sistemas:

-- MySQL y PostgreSQL
SELECT CONCAT('Precio: ', COALESCE(precio, 'no disponible'), ' euros') 
FROM productos;

Extracción con expresiones regulares

Para extracciones más complejas, podemos usar funciones basadas en expresiones regulares:

-- PostgreSQL: extraer el código de área de un número telefónico
SELECT 
    telefono,
    SUBSTRING(telefono FROM '^\(([0-9]{3})\)') AS codigo_area
FROM contactos;

En MySQL, podemos usar REGEXP_SUBSTR():

-- MySQL 8.0+
SELECT 
    telefono,
    REGEXP_SUBSTR(telefono, '\\(([0-9]{3})\\)', 1, 1, '', 1) AS codigo_area
FROM contactos;

Estas funciones son extremadamente potentes para extraer patrones específicos de texto, como códigos postales, números de identificación o cualquier texto con formato predefinido.

Búsqueda y reemplazo

Las operaciones de búsqueda y reemplazo son fundamentales para la manipulación efectiva de datos textuales en SQL. Estas funciones nos permiten localizar patrones específicos dentro de cadenas de texto y modificarlos según nuestras necesidades, lo que resulta esencial para la limpieza, normalización y transformación de datos.

Funciones de búsqueda

Antes de reemplazar texto, necesitamos poder localizar la información dentro de nuestras cadenas. MySQL y PostgreSQL ofrecen varias funciones para este propósito.

Localización de subcadenas

La función POSITION() nos permite encontrar la posición de una subcadena dentro de un texto:

-- PostgreSQL y MySQL
SELECT POSITION('SQL' IN 'Aprendiendo SQL básico');
-- Resultado: 12

En MySQL también podemos usar la función LOCATE(), que ofrece la ventaja de especificar una posición de inicio para la búsqueda:

-- MySQL
SELECT LOCATE('SQL', 'Aprendiendo SQL básico y SQL avanzado', 15);
-- Resultado: 26

Para verificar si una cadena contiene una subcadena específica:

-- MySQL
SELECT 
    nombre_producto,
    CASE 
        WHEN LOCATE('premium', LOWER(descripcion)) > 0 THEN 'Producto Premium'
        ELSE 'Producto Estándar'
    END AS categoria
FROM productos;

En PostgreSQL, podemos usar el operador LIKE o ILIKE (insensible a mayúsculas/minúsculas):

-- PostgreSQL
SELECT 
    nombre_producto,
    CASE 
        WHEN descripcion ILIKE '%premium%' THEN 'Producto Premium'
        ELSE 'Producto Estándar'
    END AS categoria
FROM productos;

Búsqueda de patrones con LIKE

El operador LIKE es una herramienta versátil para búsquedas de patrones simples:

-- MySQL y PostgreSQL
SELECT 
    email,
    CASE 
        WHEN email LIKE '%.com' THEN 'Dominio comercial'
        WHEN email LIKE '%.org' THEN 'Organización'
        WHEN email LIKE '%.edu' THEN 'Educativo'
        ELSE 'Otro dominio'
    END AS tipo_dominio
FROM usuarios;

Los caracteres comodín más utilizados con LIKE son:

• % - Coincide con cualquier secuencia de caracteres (incluso vacía)
• _ - Coincide con exactamente un carácter

-- Buscar nombres que empiezan con 'A'
SELECT * FROM clientes WHERE nombre LIKE 'A%';

-- Buscar códigos con formato específico (ej: A-1234)
SELECT * FROM productos WHERE codigo LIKE '_-____';

Funciones de reemplazo

Una vez localizado el texto que queremos modificar, podemos usar funciones de reemplazo para transformarlo.

Reemplazo básico con REPLACE

La función REPLACE() nos permite sustituir todas las ocurrencias de una subcadena por otra:

-- MySQL y PostgreSQL
SELECT REPLACE('SQL es un lenguaje SQL estándar', 'SQL', 'Structured Query Language');
-- Resultado: 'Structured Query Language es un lenguaje Structured Query Language estándar'

Esta función es útil para corregir errores comunes o estandarizar formatos:

-- Normalizar números telefónicos
SELECT 
    telefono,
    REPLACE(REPLACE(REPLACE(telefono, '(', ''), ')', ''), '-', '') AS telefono_limpio
FROM contactos;

En este ejemplo, eliminamos paréntesis y guiones de los números telefónicos para almacenarlos en un formato consistente.

Reemplazo selectivo

Para reemplazos más específicos, podemos combinar funciones de búsqueda y reemplazo:

-- Reemplazar solo la primera ocurrencia en MySQL
SELECT 
    descripcion,
    CONCAT(
        SUBSTRING(descripcion, 1, LOCATE('error', descripcion) - 1),
        'problema',
        SUBSTRING(descripcion, LOCATE('error', descripcion) + 5)
    ) AS descripcion_corregida
FROM reportes
WHERE LOCATE('error', descripcion) > 0;

En PostgreSQL, podemos usar expresiones regulares para reemplazos más avanzados:

-- PostgreSQL: reemplazar solo la primera ocurrencia
SELECT REGEXP_REPLACE('Error de conexión y error de autenticación', 'error', 'problema', 'i');
-- Resultado: 'problema de conexión y error de autenticación'

El parámetro 'i' hace que la búsqueda sea insensible a mayúsculas/minúsculas.

Reemplazo con expresiones regulares

Las expresiones regulares proporcionan una forma potente para realizar reemplazos complejos:

-- PostgreSQL: extraer y formatear números telefónicos
SELECT 
    telefono,
    REGEXP_REPLACE(telefono, '.*?(\d{3}).*?(\d{3}).*?(\d{4}).*', '(\1) \2-\3') AS telefono_formateado
FROM contactos;

Este ejemplo busca tres grupos de dígitos (códigos de área y número) y los reformatea con un patrón específico.

En MySQL 8.0+, podemos usar REGEXP_REPLACE() de manera similar:

-- MySQL 8.0+: convertir fechas de formato MM/DD/YYYY a YYYY-MM-DD
SELECT 
    fecha_texto,
    REGEXP_REPLACE(fecha_texto, '([0-9]{1,2})/([0-9]{1,2})/([0-9]{4})', '$3-$1-$2') AS fecha_iso
FROM datos_importados;

Casos de uso prácticos

Veamos algunos escenarios reales donde estas funciones son especialmente útiles:

Limpieza de datos importados

-- Eliminar espacios en blanco extras
UPDATE datos_importados
SET nombre = TRIM(REGEXP_REPLACE(nombre, '\\s+', ' '))
WHERE nombre LIKE '%  %';

Este ejemplo elimina espacios múltiples y los reemplaza por un solo espacio, luego usa TRIM() para eliminar espacios al inicio y final.

Enmascaramiento de datos sensibles

-- Enmascarar números de tarjeta de crédito
SELECT 
    numero_tarjeta,
    CONCAT(
        LEFT(numero_tarjeta, 4),
        REPEAT('*', LENGTH(numero_tarjeta) - 8),
        RIGHT(numero_tarjeta, 4)
    ) AS tarjeta_enmascarada
FROM pagos;

Este ejemplo muestra solo los primeros y últimos 4 dígitos de un número de tarjeta, reemplazando los dígitos intermedios con asteriscos.

Normalización de URLs

-- Asegurar que todas las URLs comienzan con https://
SELECT 
    sitio_web,
    CASE 
        WHEN sitio_web LIKE 'http://%' THEN REPLACE(sitio_web, 'http://', 'https://')
        WHEN sitio_web NOT LIKE 'https://%' THEN CONCAT('https://', sitio_web)
        ELSE sitio_web
    END AS sitio_web_seguro
FROM empresas;

Extracción y transformación de información

-- Extraer y formatear nombres de usuario de emails
SELECT 
    email,
    INITCAP(REPLACE(SUBSTRING_INDEX(email, '@', 1), '.', ' ')) AS nombre_usuario
FROM usuarios;

En PostgreSQL, usaríamos:

-- PostgreSQL
SELECT 
    email,
    INITCAP(REPLACE(SPLIT_PART(email, '@', 1), '.', ' ')) AS nombre_usuario
FROM usuarios;

Este ejemplo extrae la parte del usuario del email, reemplaza puntos por espacios y capitaliza cada palabra, convirtiendo "juan.perez@ejemplo.com" en "Juan Perez".

Diferencias entre MySQL y PostgreSQL

Aunque ambos sistemas ofrecen funcionalidades similares, existen algunas diferencias importantes:

• Expresiones regulares: PostgreSQL tiene soporte nativo más robusto para expresiones regulares con funciones como REGEXP_REPLACE(), REGEXP_MATCHES() y REGEXP_SPLIT_TO_TABLE(). MySQL ha mejorado su soporte en versiones recientes (8.0+).

• Sensibilidad a mayúsculas/minúsculas: En PostgreSQL, las búsquedas con LIKE son sensibles a mayúsculas/minúsculas por defecto, mientras que ILIKE es insensible. En MySQL, LIKE es insensible por defecto (dependiendo de la configuración de collation).

• Funciones específicas: PostgreSQL ofrece OVERLAY() para reemplazar porciones de texto en posiciones específicas, mientras que MySQL tiene INSERT() para una funcionalidad similar.

-- PostgreSQL: reemplazar caracteres en posiciones específicas
SELECT OVERLAY('abcdefghi' PLACING 'XXX' FROM 4 FOR 3);
-- Resultado: 'abcXXXghi'

-- MySQL: función equivalente
SELECT INSERT('abcdefghi', 4, 3, 'XXX');
-- Resultado: 'abcXXXghi'

Optimización de búsquedas y reemplazos

Para mejorar el rendimiento de operaciones de búsqueda y reemplazo en grandes conjuntos de datos:

• Considere crear índices de texto completo para búsquedas frecuentes en columnas de texto.
• Utilice la función BINARY en MySQL para hacer búsquedas sensibles a mayúsculas/minúsculas cuando sea necesario.
• Para operaciones de actualización masiva, considere hacerlas por lotes para evitar bloqueos prolongados.

-- Crear un índice de texto completo en MySQL
CREATE FULLTEXT INDEX idx_descripcion ON productos(descripcion);

-- Búsqueda utilizando el índice
SELECT * FROM productos 
WHERE MATCH(descripcion) AGAINST('premium calidad');

Las funciones de búsqueda y reemplazo son herramientas esenciales para la manipulación de datos textuales en SQL, permitiéndonos transformar información para satisfacer requisitos específicos de formato, limpieza y presentación.

Transformación de texto

La transformación de texto es una operación fundamental en el procesamiento de datos que nos permite modificar cadenas para adaptarlas a formatos específicos, estandarizar información o prepararla para análisis posteriores. SQL ofrece diversas funciones especializadas para estas transformaciones, que van más allá de la simple concatenación, extracción o reemplazo.

Cambio de mayúsculas y minúsculas

Una de las transformaciones más comunes es la modificación del caso de los caracteres. Tanto MySQL como PostgreSQL ofrecen funciones específicas para este propósito:

-- Convertir a mayúsculas
SELECT UPPER('texto de ejemplo') AS texto_mayusculas;
-- Resultado: 'TEXTO DE EJEMPLO'

-- Convertir a minúsculas
SELECT LOWER('TEXTO DE EJEMPLO') AS texto_minusculas;
-- Resultado: 'texto de ejemplo'

Para capitalizar la primera letra de cada palabra, ambos sistemas ofrecen soluciones:

-- PostgreSQL: función nativa
SELECT INITCAP('juan garcía lópez') AS nombre_formateado;
-- Resultado: 'Juan García López'

-- MySQL: combinación de funciones
SELECT CONCAT(
    UPPER(SUBSTRING(nombre, 1, 1)),
    LOWER(SUBSTRING(nombre, 2))
) AS nombre_capitalizado
FROM clientes;

Estas funciones son especialmente útiles para estandarizar datos de entrada del usuario o para presentación:

-- Normalizar nombres de productos
UPDATE productos
SET nombre = INITCAP(nombre)
WHERE nombre = LOWER(nombre) OR nombre = UPPER(nombre);

Eliminación de espacios

La limpieza de espacios innecesarios es crucial para la normalización de datos textuales:

-- Eliminar espacios al inicio y final
SELECT TRIM('  texto con espacios  ') AS texto_limpio;
-- Resultado: 'texto con espacios'

-- Eliminar solo espacios iniciales
SELECT LTRIM('  texto con espacios') AS sin_espacios_iniciales;
-- Resultado: 'texto con espacios'

-- Eliminar solo espacios finales
SELECT RTRIM('texto con espacios  ') AS sin_espacios_finales;
-- Resultado: 'texto con espacios'

En PostgreSQL, la función TRIM es más versátil y permite especificar los caracteres a eliminar:

-- PostgreSQL: eliminar caracteres específicos
SELECT TRIM(BOTH '0' FROM '00123.450') AS numero_limpio;
-- Resultado: '123.45'

MySQL ofrece funciones similares:

-- MySQL: eliminar caracteres específicos
SELECT TRIM(LEADING '0' FROM '00123.450') AS numero_limpio;
-- Resultado: '123.450'

Relleno de cadenas

Para crear cadenas de longitud uniforme, podemos usar funciones de relleno:

-- Rellenar por la izquierda
SELECT LPAD('123', 6, '0') AS codigo_formateado;
-- Resultado: '000123'

-- Rellenar por la derecha
SELECT RPAD('Precio: ', 10, '.') AS etiqueta;
-- Resultado: 'Precio: ..'

Estas funciones son particularmente útiles para crear códigos con formato específico o alinear texto en informes:

-- Crear códigos de producto uniformes
SELECT 
    id_producto,
    CONCAT('PROD-', LPAD(CAST(id_producto AS VARCHAR), 5, '0')) AS codigo_producto
FROM productos;

Inversión de texto

Para invertir el orden de los caracteres en una cadena:

-- MySQL
SELECT REVERSE('abcdef') AS texto_invertido;
-- Resultado: 'fedcba'

-- PostgreSQL
SELECT REVERSE('abcdef') AS texto_invertido;
-- Resultado: 'fedcba'

Esta función puede ser útil para análisis de patrones o para crear claves de búsqueda alternativas.

Conversión de tipos de datos

La transformación entre tipos de datos es una operación común en SQL:

-- Convertir número a texto
SELECT CAST(12345.67 AS VARCHAR) AS numero_como_texto;
-- Resultado: '12345.67'

-- Convertir texto a número
SELECT CAST('12345.67' AS DECIMAL(10,2)) AS texto_como_numero;
-- Resultado: 12345.67

En MySQL, también podemos usar las funciones específicas:

-- MySQL: conversiones específicas
SELECT 
    CONVERT('2023-05-15', DATE) AS fecha,
    CONVERT('123.45', DECIMAL(10,2)) AS numero;

Formateo de números

Para presentar números con formato específico:

-- PostgreSQL: formato con TO_CHAR
SELECT TO_CHAR(1234.56, '9,999.99') AS numero_formateado;
-- Resultado: ' 1,234.56'

-- MySQL: formato con FORMAT
SELECT FORMAT(1234.56, 2) AS numero_formateado;
-- Resultado: '1,234.56'

La función FORMAT en MySQL también permite especificar la configuración regional:

-- MySQL: formato con configuración regional
SELECT FORMAT(1234.56, 2, 'es_ES') AS numero_formateado_espanol;
-- Resultado: '1.234,56'

Extracción de palabras

Para trabajar con palabras individuales dentro de una cadena:

-- PostgreSQL: extraer palabras con regexp_split_to_table
SELECT regexp_split_to_table('uno dos tres', '\s+') AS palabra;
-- Resultado: tres filas con 'uno', 'dos', 'tres'

-- MySQL: extraer palabras con SUBSTRING_INDEX
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX('uno dos tres', ' ', 1), ' ', -1) AS primera_palabra,
    SUBSTRING_INDEX(SUBSTRING_INDEX('uno dos tres', ' ', 2), ' ', -1) AS segunda_palabra,
    SUBSTRING_INDEX(SUBSTRING_INDEX('uno dos tres', ' ', 3), ' ', -1) AS tercera_palabra;

Cálculo de similitud y distancia

PostgreSQL ofrece extensiones para calcular la similitud entre cadenas:

-- PostgreSQL: similitud con la extensión pg_trgm
CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT 
    palabra1,
    palabra2,
    similarity(palabra1, palabra2) AS similitud
FROM (
    VALUES ('casa', 'caza'), ('perro', 'perro'), ('gato', 'pato')
) AS palabras(palabra1, palabra2);

Esta funcionalidad es útil para búsquedas difusas o corrección de errores ortográficos.

Transformación con expresiones regulares

Las expresiones regulares permiten transformaciones avanzadas:

-- PostgreSQL: extraer solo dígitos
SELECT regexp_replace('ABC123DEF456', '[^0-9]', '', 'g') AS solo_digitos;
-- Resultado: '123456'

-- MySQL 8.0+: extraer solo letras
SELECT regexp_replace('ABC123DEF456', '[^a-zA-Z]', '', 'g') AS solo_letras;
-- Resultado: 'ABCDEF'

Casos de uso prácticos

Normalización de nombres propios

-- Normalizar nombres propios (eliminar espacios extras y capitalizar)
UPDATE clientes
SET 
    nombre = INITCAP(TRIM(regexp_replace(nombre, '\s+', ' ', 'g'))),
    apellido = INITCAP(TRIM(regexp_replace(apellido, '\s+', ' ', 'g')))
WHERE id IN (
    SELECT id FROM clientes 
    WHERE nombre LIKE '%  %' OR apellido LIKE '%  %'
    OR nombre = LOWER(nombre) OR apellido = LOWER(apellido)
);

Formateo de códigos postales

-- Asegurar formato uniforme para códigos postales
SELECT 
    codigo_postal,
    CASE 
        WHEN LENGTH(TRIM(codigo_postal)) = 4 THEN LPAD(TRIM(codigo_postal), 5, '0')
        WHEN LENGTH(TRIM(codigo_postal)) = 5 THEN TRIM(codigo_postal)
        ELSE NULL -- Código postal inválido
    END AS codigo_postal_formateado
FROM direcciones;

Transformación de nombres de usuario

-- Crear nombres de usuario a partir de nombres y apellidos
SELECT 
    nombre,
    apellido,
    LOWER(
        CONCAT(
            LEFT(nombre, 1),
            REPLACE(apellido, ' ', ''),
            '@empresa.com'
        )
    ) AS email_corporativo
FROM empleados;

Formateo de números telefónicos

-- Formatear números telefónicos
SELECT 
    telefono,
    CASE 
        WHEN LENGTH(REGEXP_REPLACE(telefono, '[^0-9]', '', 'g')) = 9 
        THEN CONCAT(
            SUBSTRING(REGEXP_REPLACE(telefono, '[^0-9]', '', 'g'), 1, 3),
            '-',
            SUBSTRING(REGEXP_REPLACE(telefono, '[^0-9]', '', 'g'), 4, 3),
            '-',
            SUBSTRING(REGEXP_REPLACE(telefono, '[^0-9]', '', 'g'), 7, 3)
        )
        ELSE telefono
    END AS telefono_formateado
FROM contactos;

Transformación de JSON

En las versiones modernas tanto de MySQL como PostgreSQL, podemos transformar datos JSON:

-- PostgreSQL: extraer valores de JSON
SELECT 
    datos->>'nombre' AS nombre,
    (datos->>'edad')::INTEGER AS edad
FROM usuarios_json;

-- MySQL: extraer valores de JSON
SELECT 
    JSON_EXTRACT(datos, '$.nombre') AS nombre,
    JSON_EXTRACT(datos, '$.edad') AS edad
FROM usuarios_json;

Para transformar datos tabulares a formato JSON:

-- PostgreSQL: crear JSON a partir de columnas
SELECT 
    id,
    json_build_object(
        'nombre', nombre,
        'apellido', apellido,
        'edad', edad,
        'direccion', json_build_object(
            'calle', calle,
            'ciudad', ciudad,
            'codigo_postal', codigo_postal
        )
    ) AS datos_json
FROM clientes
JOIN direcciones ON clientes.id = direcciones.cliente_id;

Optimización de transformaciones de texto

Las operaciones de transformación de texto pueden ser costosas en términos de rendimiento, especialmente en conjuntos de datos grandes. Algunas recomendaciones:

• Realice transformaciones en el momento de la inserción/actualización cuando sea posible, no en cada consulta.
• Considere crear columnas derivadas para valores transformados frecuentemente utilizados.
• Utilice índices funcionales en PostgreSQL para consultas que filtren por valores transformados.

-- PostgreSQL: índice funcional para búsquedas insensibles a mayúsculas/minúsculas
CREATE INDEX idx_lower_nombre ON clientes (LOWER(nombre));

-- Consulta que aprovecha el índice
SELECT * FROM clientes WHERE LOWER(nombre) = 'juan';

Diferencias entre MySQL y PostgreSQL

Aunque ambos sistemas ofrecen funcionalidades similares, existen algunas particularidades:

• PostgreSQL tiene funciones más avanzadas para el procesamiento de texto, como INITCAP() nativa y mejor soporte para expresiones regulares.
• MySQL ofrece la función FORMAT() con soporte para configuraciones regionales.
• PostgreSQL tiene extensiones como pg_trgm para búsquedas de similitud de texto.
• Las funciones de transformación JSON tienen sintaxis diferentes en ambos sistemas.

La elección de las funciones adecuadas dependerá del sistema de gestión de bases de datos que estemos utilizando y de los requisitos específicos de nuestra aplicación.

Las transformaciones de texto son herramientas poderosas que nos permiten manipular y estandarizar datos textuales, mejorando la calidad y consistencia de la información almacenada en nuestras bases de datos.