Cómo usar la función SQL REPLACE()

TL;DR

• REPLACE(string, old_substring, new_substring) sustituye todas las apariciones de una subcadena en una sola llamada

• Encadena llamadas para reemplazar varias subcadenas: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Pasa una cadena vacía para eliminar caracteres: REPLACE(phone, '-', '')

• Si algún argumento es NULL, la función devuelve NULL para esa fila

• La distinción entre mayúsculas y minúsculas depende de tu base de datos y la intercalación: SQL Server y MySQL suelen ser no sensibles a mayúsculas por defecto; PostgreSQL, sensible a mayúsculas por defecto

Entendiendo la función SQL REPLACE()

La función REPLACE() en SQL busca la subcadena o cadena indicada en una columna y la reemplaza por otra cadena dada. El ejemplo siguiente muestra el uso básico de REPLACE(). Los argumentos de la función son:

• string: La cadena original donde se hará el reemplazo.

• old_substring: La subcadena que quieres reemplazar.

• new_substring: La subcadena que sustituirá a la anterior.

REPLACE(string, old_substring, new_substring)

Al usar REPLACE() para modificar cadenas en una tabla, la consulta tendrá la siguiente sintaxis, donde:

• column_name: La columna en la que se buscan las apariciones de la subcadena.

• old_substring: La subcadena que quieres reemplazar.

• new_substring: La subcadena que sustituye a la anterior.

-- Selecciona la columna description de la tabla products,
-- sustituyendo 'old' por 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

Ejemplo de REPLACE()

La función REPLACE() en SQL sustituye todas las apariciones de una cadena por la subcadena o cadena indicada. En el ejemplo siguiente, REPLACE() ha reemplazado el valor old por new en la columna description. La consulta se ejecuta fila a fila para cambiar la cadena antigua por la nueva.

-- Selecciona la columna description de la tabla products,
-- sustituyendo 'old' por 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Nota: Las bases de datos MySQL, PostgreSQL, Oracle y SQL Server admiten la función REPLACE().

Casos de uso de REPLACE() en SQL

Existen muchos casos de uso relevantes de REPLACE() en la manipulación de texto y la transformación de datos. Algunos ejemplos:

Reemplazo básico

Puedes usar REPLACE() para realizar un reemplazo básico. Supongamos que tenemos la siguiente tabla en nuestra base de datos de SQL Server:

Ejemplo de tabla Products. Imagen del autor.

Con la siguiente consulta SQL, puedes usar REPLACE() para cambiar el estado old por new.

SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

La tabla resultante tendrá un aspecto similar al que ves a continuación.

Tabla actualizada con SQL REPLACE(). Imagen del autor.

Reemplazar varias subcadenas

Si necesitas reemplazar varias subcadenas, encadena múltiples llamadas a REPLACE(). Aquí actualizamos el valor old por outdated y new por fresh.

-- Encadena varias REPLACE() para cambiar 'old' por 'outdated' y 'new' por 'fresh'
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

La tabla resultante se muestra a continuación.

Tabla actualizada usando múltiples subcadenas. Imagen del autor.

Eliminar caracteres

Pasa una cadena vacía como tercer argumento para eliminar caracteres por completo. Es la forma más limpia de quitar signos de puntuación, espacios extra o caracteres de formato de una columna.

-- Quitar guiones de los números de teléfono
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

Por ejemplo, '555-867-5309' pasa a ser '5558675309'. La misma técnica sirve para eliminar caracteres especiales, artefactos de codificación o espacios en blanco no deseados en datos importados.

Gestionar la sensibilidad a mayúsculas

La sensibilidad a mayúsculas de REPLACE() depende de tu base de datos y su intercalación. PostgreSQL la trata como sensible a mayúsculas por defecto, por lo que REPLACE(col, 'Old', 'New') no coincidirá con 'old'. SQL Server y MySQL siguen la intercalación de la columna o la base de datos: en la mayoría de instalaciones por defecto no se distingue entre mayúsculas, así que REPLACE() hará coincidir independientemente del caso.

Si quieres reemplazar valores sin distinguir mayúsculas, usa UPPER() o LOWER() para convertir el texto antes del reemplazo. Esta técnica mantiene los valores consistentes.

-- Reemplazar 'OLD' por 'OUTDATED', gestionando mayúsculas convirtiendo a mayúsculas
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

Tabla actualizada con REPLACE() y UPPER(). Imagen del autor.

-- Reemplazar 'old' por 'outdated', gestionando mayúsculas convirtiendo a minúsculas
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

Tabla actualizada con REPLACE() y LOWER(). Imagen del autor.

Técnicas avanzadas

Estas técnicas combinan REPLACE() con otras funciones de SQL para gestionar sustituciones que van más allá del simple buscar y reemplazar.

Reemplazos con valores dinámicos

Puedes realizar reemplazos dinámicos combinando REPLACE() con otras funciones de SQL. Por ejemplo, puedes usar el valor de otra columna para sustituir la subcadena. En el ejemplo siguiente, REPLACE() reemplaza cada producto antiguo por el valor real de product_name.

-- Reemplazar 'old' por el valor de la columna product_name
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

Tabla actualizada con REPLACE() y valores dinámicos. Imagen del autor.

Usar REPLACE() en sentencias UPDATE

La función REPLACE() funciona dentro de sentencias UPDATE para modificar valores de columnas en una o varias filas. La consulta siguiente actualiza el valor old por outdated en la columna status.

-- Actualizar la columna status para reemplazar 'old' por 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

Para escenarios más complejos, como actualizar una tabla uniéndola con otra, echa un vistazo a nuestro tutorial sobre SQL UPDATE con JOIN.

Combinar REPLACE() con otras funciones de cadena

Una técnica avanzada consiste en combinar REPLACE() con otras funciones de cadenas en SQL, como CHARINDEX() o SUBSTRING(), al reemplazar valores de texto.

-- Reemplazar la parte del nombre del producto desde el primer espacio por 'Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Extrae la subcadena de product_name desde el primer espacio hasta el final
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

La tabla resultante de la consulta anterior se muestra a continuación.

Tabla actualizada con REPLACE() y otras funciones de cadenas. Imagen del autor.

Reemplazar NULL por 0

Puedes usar la función COALESCE() para sustituir el valor NULL por un valor de reserva antes de llamar a REPLACE(), como se muestra a continuación.

-- Reemplazar NULL por 'new' en la columna status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

Ojo: si cualquiera de los argumentos que pasas a REPLACE() es NULL, la función devuelve NULL para esa fila, aunque los demás argumentos sean válidos. Envuelve las columnas que puedan ser nulas con COALESCE() antes de llamar a REPLACE(), no después.

Aplicaciones prácticas de la función SQL REPLACE()

Estos son escenarios en los que REPLACE() se gana su lugar en consultas de producción.

• Limpieza de datos: REPLACE() es útil para limpiar datos eliminando o sustituyendo caracteres o espacios no deseados en cadenas dentro de un conjunto de datos.

• Transformación de datos: se usa para transformar datos cambiando valores a otros formatos y actualizando valores obsoletos por nuevos.

• Gestión de entradas de usuario: resulta clave para ajustar y convertir datos introducidos por personas a un estándar uniforme aceptado en una base de datos concreta. Así aseguras uniformidad.

• Estandarización de texto: estandariza valores en columnas de texto para mantener la coherencia, por ejemplo, aplicar abreviaturas en minúsculas o mayúsculas.

• Reemplazo condicionado: también facilita la gestión de bases de datos sustituyendo valores según reglas específicas.

Implementaciones específicas por base de datos de REPLACE()

MySQL, PostgreSQL, Oracle y SQL Server admiten la función REPLACE() en SQL, con una sintaxis muy similar entre ellas.

-- Reemplazar 'old' por 'new' en la columna status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Rendimiento y buenas prácticas con REPLACE()

Al usar REPLACE(), ten en cuenta estas consideraciones de rendimiento y buenas prácticas.

Impacto en el rendimiento de la consulta

REPLACE() puede afectar al rendimiento, sobre todo con conjuntos de datos grandes. Ocurre porque la función procesa la tabla fila a fila y la ejecución puede tardar.

Consideraciones de indexación

Indexa la columna sobre la que consultas antes de usar REPLACE() en una cláusula WHERE o en un UPDATE. Sin índice, la base de datos escanea todas las filas para encontrar coincidencias, algo especialmente costoso en tablas grandes.

Supervisión y resolución de cuellos de botella

Para mitigar problemas de rendimiento, aplica estas prácticas:

• Usa herramientas de perfilado de la base de datos para monitorizar y optimizar consultas con grandes volúmenes de datos.

• Cuando uses REPLACE() para modificar valores de texto, mantén los índices con regularidad para asegurar su alineación.

• Recurre al procesamiento por lotes para reducir el tiempo de ejecución con conjuntos de datos muy grandes.

Para aprender otras funciones avanzadas de SQL orientadas a la transformación de datos, te recomiendo el curso Learn SQL de DataCamp. El curso Reporting in SQL también cubre temas de optimización de bases de datos y te enseña técnicas para mejorar el rendimiento.

Conclusiones

La función REPLACE() cubre la mayoría de necesidades de sustitución de texto en bases de datos SQL. Ten presente el comportamiento con NULL (cualquier argumento NULL devuelve NULL) y revisa la intercalación de tu base de datos cuando importe la sensibilidad a mayúsculas.

Puedes profundizar en REPLACE() con los cursos Intermediate SQL y SQL Fundamentals de DataCamp. También te recomiendo el itinerario profesional Associate Data Analyst in SQL para aprender más sobre las aplicaciones de SQL en la transformación de datos y dar impulso a tu carrera profesional. Por último, anímate a conseguir la SQL Associate Certification de DataCamp para acreditar tu dominio de SQL en análisis de datos y destacar en las entrevistas.

Si te interesa aprender más sobre funciones de SQL usadas en análisis de datos, echa un vistazo a otros de mis tutoriales:

• Cómo usar el operador BETWEEN en SQL
• Cómo usar la cláusula LIMIT en SQL
• Cómo usar el operador IN en SQL
• Cómo usar la cláusula OFFSET en SQL