REGEXP_SUBSTR

La función REGEXP_SUBSTR() extrae de una cadena la porción de texto que coincide con un patrón de expresión regular. Mientras que REGEXP_INSTR() te dice dónde está una coincidencia y REGEXP_LIKE() te dice si existe, REGEXP_SUBSTR() te devuelve el texto que coincidió. Esta función es esencial cuando necesitas extraer datos estructurados que están incrustados en campos de texto libre: números de referencia, dominios de email, códigos postales, importes o cualquier fragmento que siga un patrón reconocible.

Sintaxis

REGEXP_SUBSTR(expresion, patron [, pos [, ocurrencia [, modo]]])

Los parámetros son:

La función devuelve la subcadena que coincide con el patrón o NULL si no hay coincidencia o si algún argumento es NULL.

Comportamiento básico

En su forma más simple, REGEXP_SUBSTR() extrae la primera coincidencia del patrón:

SELECT REGEXP_SUBSTR('Pedido PED-2025-00142 procesado', '[A-Z]+-[0-9]+-[0-9]+') AS referencia;

La función ha encontrado el fragmento que coincide con el patrón de letras-dígitos-dígitos y lo ha devuelto completo. Esto es mucho más cómodo que calcular posiciones con REGEXP_INSTR() y luego usar SUBSTRING() para extraer el texto.

Cuando no hay coincidencia, la función devuelve NULL:

SELECT REGEXP_SUBSTR('Sin referencias aquí', '[A-Z]+-[0-9]+') AS referencia;

Un ejemplo práctico con datos de una tabla. Para extraer el primer número que aparece en la descripción de un producto:

SELECT
    nombre,
    descripcion,
    REGEXP_SUBSTR(descripcion, '[0-9]+') AS primer_numero
FROM productos;

Para los auriculares no se extrae ningún número porque la descripción no contiene dígitos.

Extraer la enésima ocurrencia

El parámetro ocurrencia permite extraer una coincidencia específica cuando el patrón aparece múltiples veces:

SELECT
    'Dimensiones: 120x45x30 cm' AS texto,
    REGEXP_SUBSTR('Dimensiones: 120x45x30 cm', '[0-9]+', 1, 1) AS largo,
    REGEXP_SUBSTR('Dimensiones: 120x45x30 cm', '[0-9]+', 1, 2) AS ancho,
    REGEXP_SUBSTR('Dimensiones: 120x45x30 cm', '[0-9]+', 1, 3) AS alto;

Al cambiar el parámetro de ocurrencia de 1 a 3, extraemos cada dimensión por separado. El patrón [0-9]+ coincide con cada grupo de dígitos y el parámetro de ocurrencia selecciona cuál de ellos devolver.

Este enfoque es muy útil para descomponer valores compuestos almacenados en una sola columna:

SELECT
    codigo_pedido,
    REGEXP_SUBSTR(codigo_pedido, '[^-]+', 1, 1) AS tipo,
    REGEXP_SUBSTR(codigo_pedido, '[^-]+', 1, 2) AS anio,
    REGEXP_SUBSTR(codigo_pedido, '[^-]+', 1, 3) AS numero
FROM pedidos;

El patrón [^-]+ captura cualquier secuencia de caracteres que no sea un guion, lo que efectivamente divide la cadena por el delimitador -.

Parámetro pos: posición inicial

El parámetro pos permite empezar la búsqueda desde una posición específica, lo cual es útil cuando sabes que la información relevante está después de cierto punto:

-- Extraer solo el código postal (dígitos después de la coma)
SELECT
    direccion,
    REGEXP_SUBSTR(direccion, '[0-9]{5}', REGEXP_INSTR(direccion, ',') + 1) AS codigo_postal
FROM clientes;

Al empezar la búsqueda después de la coma, evitamos extraer accidentalmente un número de la calle que pudiera tener cinco dígitos (como en el tercer ejemplo, donde 12345 en la calle se ignora porque la búsqueda empieza después de la coma).

Caso práctico: extraer dominios de email

Una tarea frecuente es extraer el dominio de las direcciones de email para analizarlos:

SELECT
    email,
    REGEXP_SUBSTR(email, '@(.+)$') AS con_arroba,
    REGEXP_SUBSTR(email, '[^@]+$') AS dominio
FROM clientes
WHERE email IS NOT NULL;

El patrón [^@]+$ captura todos los caracteres que no son @ al final de la cadena, lo que da el dominio completo. Puedes usar esto para generar un informe de distribución de dominios:

SELECT
    REGEXP_SUBSTR(email, '[^@]+$') AS dominio,
    COUNT(*) AS cantidad
FROM clientes
WHERE email IS NOT NULL
GROUP BY dominio
ORDER BY cantidad DESC
LIMIT 10;

Caso práctico: extraer importes de texto libre

En campos de notas o descripciones, a menudo aparecen importes que necesitas extraer para cálculos:

SELECT
    notas,
    REGEXP_SUBSTR(notas, '[0-9]+[.,][0-9]{2}') AS importe_texto,
    CAST(REGEXP_REPLACE(
        REGEXP_SUBSTR(notas, '[0-9]+[.,][0-9]{2}'),
        ',', '.'
    ) AS DECIMAL(10,2)) AS importe_numerico
FROM pedidos
WHERE REGEXP_SUBSTR(notas, '[0-9]+[.,][0-9]{2}') IS NOT NULL;

La combinación de REGEXP_SUBSTR() para extraer, REGEXP_REPLACE() para normalizar la coma decimal y CAST() para convertir a número permite procesar importes incrustados en texto de forma automática.

Caso práctico: extraer datos de campos semiestructurados

Cuando trabajas con campos que contienen pares clave-valor o datos semiestructurados, REGEXP_SUBSTR() permite extraer valores específicos:

SELECT
    especificaciones,
    REGEXP_SUBSTR(especificaciones, 'Color: ([a-zA-ZáéíóúÁÉÍÓÚñÑ]+)') AS color_completo,
    REGEXP_SUBSTR(especificaciones, 'Peso: ([0-9.]+\\s*(kg|g))') AS peso_completo
FROM productos;

El tercer producto no tiene color en sus especificaciones, por lo que REGEXP_SUBSTR() devuelve NULL para ese campo. Esto permite construir consultas que extraen información estructurada de campos de texto de forma robusta, sin fallar cuando la información esperada no está presente.

Extraer múltiples coincidencias en columnas separadas

Aunque REGEXP_SUBSTR() solo devuelve una coincidencia por llamada, puedes usar el parámetro de ocurrencia para extraer múltiples valores en columnas separadas:

SELECT
    etiquetas,
    REGEXP_SUBSTR(etiquetas, '[^,]+', 1, 1) AS etiqueta_1,
    REGEXP_SUBSTR(etiquetas, '[^,]+', 1, 2) AS etiqueta_2,
    REGEXP_SUBSTR(etiquetas, '[^,]+', 1, 3) AS etiqueta_3
FROM productos;

Este enfoque es una alternativa ligera a dividir la cadena en filas cuando solo necesitas un número fijo de valores. Para el producto con cuatro etiquetas, la cuarta (outdoor) no se extrae porque solo pedimos tres columnas, pero podríamos añadir una cuarta llamada con ocurrencia 4.

Manejo de NULL

REGEXP_SUBSTR() devuelve NULL en tres situaciones: cuando la expresión es NULL, cuando el patrón es NULL o cuando no hay coincidencia. Es importante distinguir entre "campo nulo" y "patrón no encontrado":

SELECT
    nombre,
    descripcion,
    REGEXP_SUBSTR(descripcion, '[0-9]+\\s*(GB|TB)') AS almacenamiento,
    CASE
        WHEN descripcion IS NULL THEN 'Sin descripción'
        WHEN REGEXP_SUBSTR(descripcion, '[0-9]+\\s*(GB|TB)') IS NULL THEN 'No especificado'
        ELSE REGEXP_SUBSTR(descripcion, '[0-9]+\\s*(GB|TB)')
    END AS almacenamiento_detallado
FROM productos;

El CASE permite dar un mensaje diferente según si el campo era nulo o si simplemente no contenía el patrón buscado.

Combinación con otras funciones

REGEXP_SUBSTR() se integra perfectamente con funciones numéricas y de cadena para extraer y transformar datos en un solo paso:

-- Extraer y sumar todos los importes mencionados en las notas
SELECT
    id,
    notas,
    CAST(REGEXP_SUBSTR(notas, '[0-9]+\\.?[0-9]*', 1, 1) AS DECIMAL(10,2)) AS importe_1,
    CAST(REGEXP_SUBSTR(notas, '[0-9]+\\.?[0-9]*', 1, 2) AS DECIMAL(10,2)) AS importe_2,
    COALESCE(CAST(REGEXP_SUBSTR(notas, '[0-9]+\\.?[0-9]*', 1, 1) AS DECIMAL(10,2)), 0)
    + COALESCE(CAST(REGEXP_SUBSTR(notas, '[0-9]+\\.?[0-9]*', 1, 2) AS DECIMAL(10,2)), 0)
    AS suma
FROM pedidos
WHERE REGEXP_LIKE(notas, '[0-9]+\\.?[0-9]*');

También puedes combinar REGEXP_SUBSTR() con CONCAT() para construir cadenas nuevas a partir de las partes extraídas:

SELECT
    telefono,
    CONCAT(
        '(',
        REGEXP_SUBSTR(REGEXP_REPLACE(telefono, '[^0-9]', ''), '[0-9]{3}', 1, 1),
        ') ',
        REGEXP_SUBSTR(REGEXP_REPLACE(telefono, '[^0-9]', ''), '[0-9]{3}', 1, 2),
        '-',
        REGEXP_SUBSTR(REGEXP_REPLACE(telefono, '[^0-9]', ''), '[0-9]{3}', 1, 3)
    ) AS telefono_formateado
FROM clientes
WHERE telefono IS NOT NULL;

Con esto completamos la sección de expresiones regulares. En la siguiente sección veremos las funciones JSON de MySQL.