SQL Server Replace Function

I recently worked on SQL Query, where I needed to change employee roles and salaries in the EmployeeInfo table. Here, in SQL, we can easily use the Replace Function to make the changes.

Table of Contents

The replace () function in SQL is a built-in function that allows us to replace all the substrings within a specified string with a new substring. This feature is useful for databases and updating information in bulk.

Below is the syntax for the replace function in SQL Server.

REPLACE (column_name, old_string, new_string)

column_name – The column’s name contains the text you want to search for.
old_string – The string you want to replace.
new_string – The string to replace the old string.

To use replace function in SQL server, Consider the points below.

First backup – Always back up your data before performing bulk string replacements to prevent unintentional data loss.
Transactions – When updating data, use transactions to ensure that changes can be reversed if something goes wrong.
Enhance performance – For large datasets, consider restricting the scope of your REPLACE SQL operations to rows containing the old string.

Before using the replace() function in a column in a table, let me show how to use the replace() function in a string.

Here, I have a string like the one below:

This is a good platform

In this string, I want to replace Platform with Technology.

You can use the below query.

SELECT 
    REPLACE(
        'This is a good platform', 
        'platform', 
        'Technology'
    ) result;

Keynote – Now, the platform has been changed to Technology. Sometimes, the replace() function search is case-sensitive. It will not consider ‘Platform’ the same as ‘platform.’ So, it will simply return the exact string without making any changes.

Replace Column Value using Replace() Function

Now, we will see one more example with the SQL table called EmployeeInfo.

Here, HR is one value under the EmployeeRole column; I want to replace that with Human Resource in the table EmpDetails.

Use the query below to change HR to Human Resource under EmployeeRole in EmployeeInfo. The output will be as follows. Here, wherever you have the HR value, everywhere it will change to Human Resource.

select Replace (Role, 'HR', 'Human Resource') from EmployeeInfo

Here, HR is changed to Human Resource using the replace function.

Now, we will try to change the salary for the same table EmpDetails. Sometimes, the employee’s salary changes. Here, I want to change the salary for a person. Use the below query to change.

Note – Wherever 55000 is shown, it will change to 57000. If you want to change for a particular person, you have to use the where condition.

select Replace (salary, '55000', '57000') from EmployeeInfo

This is how we have to use replace in SQL Server.

Now, if you want to change the salary for a particular person, use the below query. Here, you can use the UPDATE statement with where condition to reflect in the table.

UPDATE EmployeeInfo
SET
Employeesalary = REPLACE(Employeesalary, '55000', '58000')
where EmployeeId = 1;

This is how we can use the Replace() function in SQL Server.

Use Replace in SQL With the UPDATE Statement

By using the Replace function, the string will be changed from one to another. But if we want to reflect in our table, we must use replace and update statements together.

Below are the table details. Here, I want to change the name of the Employee Peter to David by using update and replace statements.

how to use replace function in sql server

Update EmployeeInfo
set Employeename = REPLACE(Employeename,'Peter', 'David')
where EmployeeId = 1

Now, the name will be changed from Peter to David.

Conclusion

In this article, you have learned everything about replacing SQL. The SQL replace function makes changing any particular data from large databases and tables easy.

Using replace in SQL is a fundamental concept essential to managing databases efficiently. Apply it from your end and see the changes.