Como usar a função SQL REPLACE()

Resumo

• REPLACE(string, old_substring, new_substring) troca todas as ocorrências de uma substring em uma única chamada

• Encadeie chamadas para substituir várias substrings: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')

• Passe uma string vazia para remover caracteres: REPLACE(phone, '-', '')

• Se algum argumento for NULL, a função retorna NULL para aquela linha

• A sensibilidade a maiúsculas/minúsculas depende do seu banco e da collation—SQL Server e MySQL costumam ser insensíveis por padrão; PostgreSQL é sensível por padrão

Entendendo a função SQL REPLACE()

A função REPLACE() em SQL procura a substring ou string especificada em uma coluna e a substitui por outra string informada. O exemplo abaixo mostra o uso básico da função REPLACE(). Os argumentos da função incluem:

• string: A string original onde você fará a substituição.

• old_substring: A substring que será substituída.

• new_substring: A substring que substituirá a anterior.

REPLACE(string, old_substring, new_substring)

Ao usar a função REPLACE() para alterar strings em uma tabela, a consulta terá a seguinte sintaxe, em que:

• column_name: A coluna onde serão procuradas as ocorrências da substring.

• old_substring: A substring a ser substituída.

• new_substring: A substring que entra no lugar da anterior.

-- Seleciona a coluna description da tabela products,
-- substituindo ocorrências de 'old' por 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

Exemplo de REPLACE()

A função SQL REPLACE() altera ou substitui todas as ocorrências de texto por uma substring ou string especificada. No exemplo abaixo, a função REPLACE() substituiu o valor old por new na coluna description. A consulta executa linha a linha para trocar a string antiga pela nova.

-- Seleciona a coluna description da tabela products,
-- substituindo ocorrências de 'old' por 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Observação: Bancos MySQL, PostgreSQL, Oracle e SQL Server oferecem suporte à função REPLACE().

Casos de uso de REPLACE() em SQL

Há vários casos de uso importantes da função REPLACE() na manipulação de texto e na transformação de dados. Veja alguns:

Substituição básica

Você pode usar a função REPLACE() para fazer uma substituição simples. Vamos supor que temos a tabela abaixo em um banco SQL Server:

Exemplo de tabela Products. Imagem do autor.

Com a consulta abaixo, você usa REPLACE() para trocar o status old por new.

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

A tabela resultante ficará como mostrado a seguir.

Tabela atualizada com SQL REPLACE(). Imagem do autor.

Substituindo múltiplas substrings

Se quiser substituir várias substrings, encadeie múltiplas funções REPLACE(). Aqui, atualizamos o valor old para outdated e new para fresh.

-- Encadeia múltiplas REPLACE() para trocar 'old' por 'outdated' e 'new' por 'fresh'
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

A tabela resultante é mostrada abaixo.

Tabela atualizada com múltiplas substituições. Imagem do autor.

Removendo caracteres

Passe uma string vazia como terceiro argumento para remover caracteres por completo. Esta é a forma mais simples de eliminar pontuação, espaços extras ou caracteres de formatação de uma coluna.

-- Remove os traços dos números de telefone
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

Por exemplo, '555-867-5309' vira '5558675309'. A mesma técnica remove caracteres especiais, artefatos de codificação ou espaços indesejados em dados importados.

Tratando sensibilidade a maiúsculas/minúsculas

A sensibilidade a maiúsculas/minúsculas em REPLACE() depende do banco e da collation. O PostgreSQL trata como sensível por padrão, então REPLACE(col, 'Old', 'New') não corresponderá a 'old'. SQL Server e MySQL seguem a collation da coluna ou do banco—na maioria das instalações padrão, a collation é insensível, então REPLACE() faz a correspondência independentemente do caso.

Quando quiser substituir valores sem considerar maiúsculas/minúsculas, use as funções UPPER() ou LOWER() para converter o texto antes da substituição. Essa técnica mantém os valores consistentes.

-- Substitui 'OLD' por 'OUTDATED', tratando o caso ao converter para maiúsculas
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

Tabela atualizada usando as funções REPLACE() e UPPER(). Imagem do autor.

-- Substitui 'old' por 'outdated', tratando o caso ao converter para minúsculas
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

Tabela atualizada usando as funções SQL REPLACE() e LOWER(). Imagem do autor.

Técnicas avançadas

Estas técnicas combinam REPLACE() com outras funções SQL para lidar com substituições que vão além do simples buscar-e-trocar.

Substituição com valores dinâmicos

A função REPLACE() pode realizar substituições dinâmicas em combinação com outras funções SQL. Por exemplo, você pode usar o valor de outra coluna para substituir a substring. No exemplo abaixo, REPLACE() troca cada produto "old" pelo valor real de product_name.

-- Substitui 'old' pelo valor da coluna product_name
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

Tabela atualizada usando REPLACE() com valores dinâmicos. Imagem do autor.

Usando REPLACE() em instruções UPDATE

A função REPLACE() funciona dentro de instruções UPDATE para modificar valores de coluna em uma ou mais linhas. A consulta abaixo substitui o valor old por outdated na coluna status.

-- Atualiza a coluna status para trocar 'old' por 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

Para cenários mais complexos, como atualizar uma tabela fazendo join com outra, veja nosso tutorial sobre SQL UPDATE com JOIN.

Combinando REPLACE() com outras funções de string

Uma técnica avançada é combinar REPLACE() com outras funções de string em SQL, como CHARINDEX() ou SUBSTRING(), ao substituir valores de texto.

-- Substitui a parte do nome do produto a partir do primeiro espaço por 'Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Extrai a substring de product_name a partir do primeiro espaço até o final
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

A tabela resultante da consulta acima é mostrada a seguir.

Tabela atualizada usando REPLACE() e outras funções de string. Imagem do autor.

Substituindo NULL por 0

Você pode usar a função COALESCE() para trocar o valor NULL por um fallback antes de chamar REPLACE(), como mostrado abaixo.

-- Substitui NULL por 'new' na coluna status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

Armadilha: Se qualquer argumento passado a REPLACE() for NULL, a função retorna NULL para aquela linha—mesmo que os outros argumentos sejam válidos. Envolva colunas que podem ser nulas com COALESCE() antes de chamar REPLACE(), não depois.

Aplicações práticas da função SQL REPLACE()

Veja situações em que REPLACE() faz a diferença em consultas de produção.

• Limpeza de dados: A função REPLACE() é útil para limpar dados, removendo e substituindo caracteres ou espaços indesejados em strings em um dataset.

• Transformação de dados: A função REPLACE() é usada para transformar dados, mudando valores de formato e substituindo valores desatualizados por novos.

• Tratamento de inputs de usuários: A função REPLACE() ajuda a ajustar e converter dados inseridos pelo usuário para um padrão uniforme aceito no banco. Isso garante consistência no banco de dados.

• Padronização de texto: A função REPLACE() padroniza valores em colunas de texto para garantir uniformidade, como abreviações em minúsculas ou maiúsculas.

• Substituição condicional: A função REPLACE() também é útil na administração do banco para trocar valores seguindo regras específicas.

Implementações específicas por banco de dados de REPLACE()

MySQL, PostgreSQL, Oracle e SQL Server suportam a função REPLACE() em SQL, com sintaxe semelhante entre esses bancos.

-- Substitui 'old' por 'new' na coluna status
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Desempenho e boas práticas com REPLACE()

Ao usar REPLACE(), é importante considerar os pontos de desempenho e as boas práticas a seguir.

Impacto no desempenho da consulta

A função REPLACE() pode impactar o desempenho da consulta, especialmente em datasets grandes. Isso ocorre porque a função processa a tabela linha a linha e a execução pode demorar.

Considerações de indexação

Indexe a coluna usada no filtro antes de executar REPLACE() em um WHERE ou UPDATE. Sem índice, o banco precisa escanear todas as linhas para encontrar correspondências—o que é caro em tabelas grandes.

Monitoramento e solução de gargalos

Para mitigar problemas de performance, considere estas boas práticas:

• Use ferramentas de profiling do banco para monitorar consultas e otimizar o processamento de grandes volumes.

• Ao usar REPLACE() para modificar strings, mantenha os índices regularmente para garantir que continuem eficazes.

• Faça processamento em lotes (batch) para reduzir o tempo de execução ao lidar com grandes datasets.

Para aprender outras funções SQL avançadas para transformação de dados, recomendo o curso Learn SQL da DataCamp. O curso Reporting in SQL também aborda tópicos de otimização de banco de dados, ajudando você a conhecer diferentes técnicas de performance.

Considerações finais

A função REPLACE() cobre a maior parte das necessidades de substituição de texto em bancos SQL. Fique atento ao comportamento com NULL—qualquer argumento NULL resulta em NULL—e verifique a collation do seu banco quando a sensibilidade a maiúsculas/minúsculas for relevante.

Você pode saber mais sobre a função REPLACE() em SQL com os cursos Intermediate SQL e SQL Fundamentals da DataCamp. Também recomendo a trilha de carreira Associate Data Analyst in SQL para aprofundar o uso de SQL na transformação de dados e impulsionar sua carreira. Por fim, vale fazer a SQL Associate Certification para comprovar sua conquista profissional no uso de SQL para análise de dados e se destacar em entrevistas.

Se você quer aprender mais sobre diversas funções SQL usadas em análise de dados, confira outros tutoriais meus, incluindo:

• How to Use the SQL BETWEEN Operator
• How to Use the SQL LIMIT Clause
• How to Use the SQL IN Operator
• How to Use the SQL OFFSET Clause