REPLACE function in SQL

Introduction

The REPLACE function helps you change parts of text inside your data. It swaps old words or letters with new ones.

You want to fix a typo in many records at once.

You need to update a product code by changing some characters.

You want to remove unwanted characters from a text field.

You want to change a domain name in email addresses.

You want to replace spaces with underscores in usernames.

Syntax

SQL

REPLACE(original_string, string_to_find, string_to_replace_with)

The function looks for all occurrences of string_to_find inside original_string and replaces them.

If string_to_find is not found, the original_string is returned unchanged.

Examples

This changes 'world' to 'SQL' resulting in 'hello SQL'.

SQL

SELECT REPLACE('hello world', 'world', 'SQL');

This replaces dashes with slashes in a date string.

SQL

SELECT REPLACE('2023-06-01', '-', '/');

This replaces every 'a' with 'o', resulting in 'bonono'.

SQL

SELECT REPLACE('banana', 'a', 'o');

Sample Program

This query replaces the word 'apples' with 'oranges' in the sentence.

SQL

SELECT REPLACE('I love apples', 'apples', 'oranges') AS fruit_change;

Important Notes

REPLACE is case-sensitive in most SQL databases.

If you want to replace only the first occurrence, you may need other functions or methods.

Summary

REPLACE swaps parts of text inside strings.

It changes all occurrences of the target text.

Useful for fixing or updating text data quickly.