REGEXP_INSTR

La función REGEXP_INSTR() busca un patrón de expresión regular dentro de una cadena y devuelve la posición donde se encuentra la coincidencia. Es el equivalente regex de la función INSTR(), pero en lugar de buscar una subcadena literal, busca un patrón. Esto la convierte en una herramienta ideal para localizar datos estructurados dentro de textos libres, determinar si un patrón aparece y en qué posición, o encontrar la enésima ocurrencia de un patrón.

Sintaxis

REGEXP_INSTR(expresion, patron [, pos [, ocurrencia [, opcion_retorno [, modo]]]])

Los parámetros son:

La función devuelve un entero con la posición (empezando en 1) o 0 si no hay coincidencia. Si algún argumento es NULL, devuelve NULL.

Comportamiento básico

En su forma más simple, REGEXP_INSTR() devuelve la posición donde comienza la primera coincidencia del patrón:

SELECT REGEXP_INSTR('El pedido PED-2025-001 fue procesado', '[0-9]+') AS posicion;

El primer grupo de dígitos (2025) comienza en la posición 12 de la cadena. Observa que la función solo indica dónde empieza la coincidencia, no qué texto coincidió. Para extraer el texto necesitarías REGEXP_SUBSTR(), que veremos más adelante.

Cuando el patrón no se encuentra, la función devuelve 0:

SELECT REGEXP_INSTR('Sin números aquí', '[0-9]+') AS posicion;

Esto permite usar REGEXP_INSTR() como condición booleana: un valor mayor que cero indica que el patrón existe.

-- Encontrar productos que contienen dígitos en su nombre
SELECT nombre, precio
FROM productos
WHERE REGEXP_INSTR(nombre, '[0-9]') > 0;

Parámetro pos: posición inicial

El parámetro pos indica desde qué posición empezar la búsqueda. Es útil cuando quieres ignorar coincidencias que ocurren antes de cierto punto en la cadena:

-- Buscar el primer dígito a partir de la posición 15
SELECT REGEXP_INSTR('El pedido PED-2025-001 fue procesado', '[0-9]+', 15) AS posicion;

Al empezar la búsqueda en la posición 15, se salta el 2025 (que comienza en la posición 12) y encuentra 001 en la posición 17. Recuerda que pos no desplaza el resultado: la posición devuelta siempre es relativa al inicio de la cadena completa.

Un caso práctico es buscar patrones solo en una parte específica de un campo. Por ejemplo, si tienes un campo de dirección con formato "Ciudad, Código Postal" y quieres encontrar el código postal (que siempre está después de la coma):

SELECT
    direccion,
    REGEXP_INSTR(direccion, ',') AS pos_coma,
    REGEXP_INSTR(direccion, '[0-9]{5}', REGEXP_INSTR(direccion, ',')) AS pos_cp
FROM clientes;

Al usar la posición de la coma como punto de inicio, nos aseguramos de encontrar el código postal y no otros grupos de cinco dígitos que podrían aparecer en el número de la calle.

Parámetro ocurrencia: enésima coincidencia

El parámetro ocurrencia permite buscar la segunda, tercera o enésima vez que aparece el patrón:

SELECT
    'REF: A100-B200-C300' AS texto,
    REGEXP_INSTR('REF: A100-B200-C300', '[A-Z][0-9]{3}', 1, 1) AS primera,
    REGEXP_INSTR('REF: A100-B200-C300', '[A-Z][0-9]{3}', 1, 2) AS segunda,
    REGEXP_INSTR('REF: A100-B200-C300', '[A-Z][0-9]{3}', 1, 3) AS tercera;

El patrón [A-Z][0-9]{3} (una letra seguida de tres dígitos) aparece tres veces en la cadena. Con el parámetro de ocurrencia podemos localizar cada una independientemente.

Este parámetro es muy útil para analizar campos con datos separados por delimitadores:

-- Encontrar la posición del tercer segmento separado por guiones
SELECT
    codigo,
    REGEXP_INSTR(codigo, '[^-]+', 1, 1) AS seg1_pos,
    REGEXP_INSTR(codigo, '[^-]+', 1, 2) AS seg2_pos,
    REGEXP_INSTR(codigo, '[^-]+', 1, 3) AS seg3_pos
FROM pedidos;

Parámetro opcion_retorno: inicio o fin de la coincidencia

El parámetro opcion_retorno controla qué posición devuelve la función. Con valor 0 (por defecto) devuelve la posición donde empieza la coincidencia. Con valor 1 devuelve la posición del carácter inmediatamente después del final de la coincidencia:

SELECT
    'Código: ABC-1234-XY' AS texto,
    REGEXP_INSTR('Código: ABC-1234-XY', '[0-9]+', 1, 1, 0) AS inicio,
    REGEXP_INSTR('Código: ABC-1234-XY', '[0-9]+', 1, 1, 1) AS despues_del_fin;

El grupo de dígitos 1234 comienza en la posición 13 y tiene 4 caracteres, por lo que la posición después del final es 17. La diferencia entre ambos valores te da la longitud de la coincidencia: 17 - 13 = 4.

Este parámetro es especialmente útil para calcular la longitud de una coincidencia o para extraer texto entre coincidencias:

SELECT
    descripcion,
    REGEXP_INSTR(descripcion, '[0-9]+', 1, 1, 0) AS num_inicio,
    REGEXP_INSTR(descripcion, '[0-9]+', 1, 1, 1) AS num_fin,
    REGEXP_INSTR(descripcion, '[0-9]+', 1, 1, 1)
        - REGEXP_INSTR(descripcion, '[0-9]+', 1, 1, 0) AS longitud_numero
FROM productos
WHERE REGEXP_INSTR(descripcion, '[0-9]+') > 0;

Caso práctico: encontrar patrones en direcciones

Supón que necesitas analizar las direcciones de tus clientes para localizar dónde aparece el número del portal:

SELECT
    direccion,
    REGEXP_INSTR(direccion, '[0-9]+') AS pos_numero,
    SUBSTRING(direccion, REGEXP_INSTR(direccion, '[0-9]+')) AS desde_numero
FROM clientes
WHERE REGEXP_INSTR(direccion, '[0-9]+') > 0;

Al combinar REGEXP_INSTR() con SUBSTRING(), puedes extraer todo el texto desde la posición donde aparece el primer número, lo cual captura tanto el número del portal como el resto de la dirección (piso, puerta, etc.).

Caso práctico: contar segmentos en un campo

Puedes usar REGEXP_INSTR() para determinar cuántos segmentos tiene un valor delimitado. La idea es buscar ocurrencias sucesivas hasta que devuelva 0:

SELECT
    etiquetas,
    CASE
        WHEN REGEXP_INSTR(etiquetas, '[^,]+', 1, 4) > 0 THEN '4+'
        WHEN REGEXP_INSTR(etiquetas, '[^,]+', 1, 3) > 0 THEN '3'
        WHEN REGEXP_INSTR(etiquetas, '[^,]+', 1, 2) > 0 THEN '2'
        WHEN REGEXP_INSTR(etiquetas, '[^,]+', 1, 1) > 0 THEN '1'
        ELSE '0'
    END AS num_etiquetas
FROM productos;

Esta técnica permite clasificar productos por la cantidad de etiquetas que tienen sin necesidad de dividir la cadena en filas separadas.

Caso práctico: localizar datos estructurados en texto libre

En campos de texto libre, REGEXP_INSTR() permite determinar si ciertos datos estructurados están presentes y dónde se encuentran:

SELECT
    id,
    comentario,
    REGEXP_INSTR(comentario, '#[0-9]{4,}') AS pos_ticket,
    REGEXP_INSTR(comentario, '[A-Z]{2,3}-[0-9]+') AS pos_referencia
FROM incidencias;

El valor 0 indica que el patrón no se encontró, lo que permite identificar rápidamente qué comentarios contienen tickets, referencias o ambos.

Manejo de NULL

Como todas las funciones regex en MySQL, REGEXP_INSTR() devuelve NULL cuando cualquiera de los argumentos obligatorios es NULL:

SELECT
    nombre,
    telefono,
    REGEXP_INSTR(telefono, '[0-9]+') AS pos_digitos,
    COALESCE(REGEXP_INSTR(telefono, '[0-9]+'), 0) AS pos_digitos_seguro
FROM clientes;

Usa COALESCE para convertir el NULL en 0 cuando necesites un valor numérico garantizado para cálculos o comparaciones.

Combinación con otras funciones

REGEXP_INSTR() se combina frecuentemente con SUBSTRING() para extraer texto basándose en posiciones encontradas:

-- Extraer el dominio de emails usando REGEXP_INSTR
SELECT
    email,
    SUBSTRING(
        email,
        REGEXP_INSTR(email, '@') + 1,
        REGEXP_INSTR(email, '@[^@]+$', 1, 1, 1) - REGEXP_INSTR(email, '@') - 1
    ) AS dominio
FROM clientes
WHERE email IS NOT NULL;

También puedes usarla con IF() para crear columnas calculadas que indiquen la presencia de patrones:

SELECT
    nombre,
    descripcion,
    IF(REGEXP_INSTR(descripcion, '\\b(envío|entrega)\\s+gratis', 1, 1, 0, 'i') > 0,
       'Sí', 'No') AS envio_gratis,
    IF(REGEXP_INSTR(descripcion, '\\b(garantía|warranty)\\b', 1, 1, 0, 'i') > 0,
       'Sí', 'No') AS menciona_garantia
FROM productos;

En el siguiente artículo veremos REGEXP_REPLACE para reemplazar texto usando patrones.