REPLACE Function - SQL

Overview

The REPLACE function in SQL replaces all occurrences of a specified substring within a string with another substring. It is useful for modifying or sanitizing string data.

Example:

SELECT REPLACE('Lets go run', 'run', 'hike')

Syntax:

SELECT REPLACE(string, search_string, replace_string)

string is the string in which you want to perform the replacement.

search_string is the substring you want to find and replace.

replacement_string is the substring that will replace the search_string.

Sample Data:

    
            department_id
            first_name
        
            3
            Frank
        
            2
            Jane
        
            3
            Ashley
        
            NULL
            Glenn
        
            2
            Kelly
        
            1
            Richard
        
            1
            George
        
            5
            Kyle
        
            2
            James
        
            1
            Gustavo

department_id	first_name
3	Frank
2	Jane
3	Ashley
NULL	Glenn
2	Kelly
1	Richard
1	George
5	Kyle
2	James
1	Gustavo

Example: Hard-coded value

In this example, we are replacing the word ‘run’ in the string ‘Lets go run’ with the word ‘hike’ for a final output of ‘Lets go hike.’

Example: Query without the WHERE Statement

In this example, we are replacing the letter ‘a’ in all of the rows of the first_name column with the letter ‘i.’ The first name on the list is ‘Frank’ and you can see that the first output is now ‘Frink.’

Example: Query with the WHERE Statement

In this example, we are replacing the letter ‘a’ in all of the rows of the first_name column with the letter ‘i’ where the department_id equals 1. The first name on the list with a department_id of 1 is ‘Richard’ and the first output is ‘Richird.’