Como usar a função SQL REPLACE()

A função SQL REPLACE() é útil para a manipulação de texto. A função REPLACE() altera uma parte de uma string com outra substring especificada. Na maioria dos casos, a função SQL REPLACE() é usada para modificar todas as ocorrências de substring em uma string em um determinado conjunto de dados.

A manipulação de texto é importante no SQL, pois ajuda a limpar os dados para uma análise eficiente. A técnica também ajuda na transformação e otimização de dados para melhorar o desempenho.

Se você é um aspirante a analista ou cientista de dados, recomendo que faça o curso Introdução ao SQL da DataCamp para entender os conceitos básicos das funções SQL na transformação de dados. Você também deve conferir a Folha de consulta básica do SQL para revisar alguns conceitos básicos à medida que exploramos o tópico mais a fundo.

A resposta rápida: Como usar o SQL REPLACE()

A função SQL REPLACE() altera ou substitui todas as ocorrências de string por uma substring ou string especificada. No exemplo abaixo, a função REPLACE() no SQL substituiu o valor old por new na coluna de descrição. A consulta executará cada linha para substituir a string antiga por uma nova.

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

Observação: Os bancos de dados MySQL, PostgreSQL, Oracle e SQL Server são compatíveis com a função REPLACE().

Entendendo a função SQL REPLACE()

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

• string: A cadeia de caracteres original onde você deve fazer a substituição.
• old_substring: A substring a ser substituída.
• new_substring: A substring que substituirá a antiga.

REPLACE(string, old_substring, new_substring)

Ao usar a função REPLACE() para alterar as cadeias de caracteres em uma tabela, a consulta terá a seguinte sintaxe: onde:

• nome_da_coluna: A coluna usada para pesquisar as ocorrências de substring.
• old_substring: A substring a ser substituída.
• new_substring: A substring que substituirá a antiga.

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

Exemplos detalhados e casos de uso

Há muitos casos de uso importantes da função SQL REPLACE() na manipulação de texto e transformação de dados. Eles incluem o seguinte:

Substituição básica

Você pode usar a função SQL REPLACE() para realizar uma substituição básica. Vamos supor que você tenha a seguinte tabela em nosso banco de dados do SQL Server:

Exemplo de tabela de produtos. Imagem do autor.

Com a seguinte consulta SQL, você pode usar a função REPLACE() para alterar o status de old para new.

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

A tabela resultante será parecida com a mostrada abaixo.

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

Substituição de várias substrings

Se você quiser substituir várias substrings, use várias funções REPLACE() encadeadas. Aqui, estamos atualizando o valor old com outdated, e new com fresh.

-- Chain multiple REPLACE() functions to replace 'old' with 'outdated' and 'new' with '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 usando várias substrings. Imagem do autor.

Manipulação da sensibilidade de maiúsculas e minúsculas

Como a função REPLACE() transforma tipos de dados de cadeia de caracteres, ela diferencia maiúsculas de minúsculas e só modificará as subcadeias que correspondem ao caso exato de old_substring. Portanto, é importante que você anote os valores originais do caso antes de usar a função REPLACE().

Quando quiser substituir valores que não diferenciam maiúsculas de minúsculas, você deve usar as funções UPPER() ou LOWER() para converter o texto antes da substituição. Essa técnica garante a consistência. 

-- Replace 'OLD' with 'OUTDATED', handling case sensitivity by converting to upper case
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.

-- Replace 'old' with 'outdated', handling case sensitivity by converting to lowercase
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

Vamos agora dar uma olhada em algumas técnicas ainda mais avançadas envolvendo REPLACE().

Substituição por valores dinâmicos

A função REPLACE() pode ser usada para realizar substituições dinâmicas por meio de uma combinação de outras funções SQL. Por exemplo, usando a função REPLACE(), você pode usar o valor de outra coluna para substituir a substring. No exemplo abaixo, a função REPLACE() substitui cada produto antigo pelo valor real de product_name.

-- Replace 'old' with the value from product_name column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

Tabela atualizada usando a função REPLACE() e valores dinâmicos. Imagem do autor.

Usando REPLACE() em instruções UPDATE

A função SQL REPLACE() é usada na função UPDATE para modificar os valores em uma tabela. A função UPDATE altera principalmente as colunas ou os valores de uma tabela. Por exemplo, a consulta abaixo atualizará o valor old com outdated na coluna status.

-- Update the status column to replace 'old' with 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

Combinando REPLACE() com outras funções de cadeia de caracteres

Uma técnica avançada para usar a função REPLACE() é combinar outras funções de cadeia de caracteres, como CHARINDEX() ou SUBSTRING(), ao substituir valores de cadeia de caracteres.

Você pode aprimorar a função REPLACE() combinando-a com outras funções de cadeia de caracteres, como CHARINDEX() ou SUBSTRING(), para uma manipulação de cadeia de caracteres mais eficaz.

-- Replace part of the product name starting from the first space with 'Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Extract the substring of product_name starting from the first space to the end
    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 abaixo.

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

Substituição de NULL por 0

Você pode usar a função COALESCE() para substituir o valor NULL por 0, como você pode ver na consulta abaixo.

-- Replace NULL with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

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

Agora que já exploramos como usar REPLACE(), vamos considerar algumas das aplicações práticas.

• Limpeza de dados: A função REPLACE() é útil para limpeza de dados, remoção e substituição de caracteres ou espaços indesejados em cadeias de caracteres em um conjunto de dados.
• Transformação de dados: A função REPLACE() é usada na transformação de dados para alterar valores em formatos diferentes e substituir valores desatualizados por novos valores.
• Manipulação de entradas do usuário: A função REPLACE() é importante para ajustar e converter os dados de entrada do usuário em um padrão uniforme aceitável em um banco de dados específico. Essa técnica ajuda a garantir a uniformidade do banco de dados.
• Padronização de textos: A função REPLACE() padroniza valores em colunas de strings para garantir a uniformidade, como abreviações em formatos de letras minúsculas ou maiúsculas.
• Substituição condicional: A função REPLACE() também é útil para práticas fáceis de gerenciamento de banco de dados para substituir valores de acordo com regras específicas.

Implementações e considerações específicas do banco de dados

Todos os bancos de dados MySQL, PostgreSQL, Oracle e SQL Server suportam a função REPLACE() no SQL, que tem uma sintaxe semelhante nesses bancos de dados.

-- Replace 'old' with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

Considerações sobre desempenho e práticas recomendadas

Ao usar a função REPLACE(), é importante que você observe as seguintes considerações de desempenho e práticas recomendadas.

Impacto no desempenho da consulta

A função REPLACE() pode afetar o desempenho da consulta, especialmente ao lidar com grandes conjuntos de dados. Esse problema ocorre porque a função atualiza a tabela por cada linha e pode demorar um pouco para que a consulta seja executada.

Considerações sobre indexação

É importante garantir a indexação adequada das colunas para que você tenha um desempenho ideal.

Monitoramento e resolução de gargalos de desempenho

Para resolver os problemas envolvidos no desempenho da consulta, é importante que você considere as práticas recomendadas a seguir:

• Use ferramentas de criação de perfil de banco de dados para monitorar o desempenho das consultas e otimizá-las ao lidar com grandes conjuntos de dados.
• Ao usar o site REPLACE() para modificar os valores das cadeias de caracteres, atualize os índices regularmente para garantir que eles correspondam.
• Inicie o processamento em lote para reduzir o tempo de execução da consulta ao lidar com grandes conjuntos de dados.

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

Conclusão e aprendizado adicional

Como vimos, a funçãoREPLACE() no SQL altera uma parte de uma cadeia de caracteres por outra. A aplicação da função REPLACE() é importante para diferentes práticas de limpeza e transformação de dados. É importante continuar praticando o uso e os diferentes cenários em que você pode aplicar a função REPLACE() para manipular e atualizar tipos de dados de cadeia de caracteres.

Você pode saber mais sobre a função REPLACE() no SQL fazendo os cursos SQL Intermediário e SQL Fundamentals da DataCamp. Também recomendo que você faça o programa de carreira Associate Data Analyst in SQL para saber mais sobre as aplicações do SQL na transformação de dados e iniciar sua carreira. Por fim, incentivo você a fazer a Certificação SQL Associate da DataCamp para ajudá-lo a mostrar suas conquistas profissionais no uso do SQL para análise de dados e se destacar nas entrevistas de emprego.

Por fim, se você estiver interessado em saber mais sobre várias funções SQL usadas na análise de dados, recomendo que consulte alguns dos meus outros tutoriais, incluindo:

• Como usar o operador BETWEEN do SQL
• Como usar a cláusula LIMIT do SQL
• Como usar o operador IN do SQL
• Como usar a cláusula SQL OFFSET