Latest Code Tutorials

SQL Replace Function | Search and Replace String in Database

SQL replace is an inbuilt function that is used for replacing a sequence of characters in a string with another set of characters. For example, it is used for replacing all the occurrences of a substring within a present string taken from another new string.

SQL replace function performs case sensitive replacement in MySQL but in SQL Server it is case-insensitive. It returns NULL if any argument is NULL.

SQL replace function

If you want to search and replace a substring with a new one in a column then SQL replace() function is your solution. For example, change a dead link to a new one, rename an obsolete product to the new name, etc.

SQL provides a very helpful string function called REPLACE that allows you to replace all the occurrences of the substring in the string with a new substring.

The replace function replaces all the occurrences of the substring within the string, with a new substring.

See the following syntax.

REPLACE(string, old_string, new_string)
  1. string: The string will be used for replacing the set of characters by another set of characters.
  2. old_string: The substring which will be searched in the string.
  3. new_string: All occurrences of old_string will be replaced with new_string in the string.

The function returns the new string.


SELECT REPLACE(“I Love SQL”, ’I’, ’Everyone’);

See the below output.

Everyone Love SQL


In this example, it is very well clear that I was replaced with Everyone who replaced I love SQL to the desired output as shown above.


Let’s see another example.

SELECT REPLACE(“”, ‘i’, ‘3’);

See the output.


Here, in the above example, i was replaced with 3. As mentioned above, It performs a case sensitive replacement.

This will happen only in MySQL; in SQL server it is not case sensitive. We have also used the SELECT statement in the above query.

So, in that case, the output would have been


See the following query.


See the output.



Here there was no replacement because “a” was not present in the first string.


See the following query.

SELECT REPLACE('App', ' ', '_');

See the output.


Here, whitespace or blank space is replaced with an underscore.


See the following query.

SELECT REPLACE(‘’, ‘i’, ‘123’);

See the output.


Here, in the above example, i was replaced with 123.

Now, from the examples mentioned above, you might have got a clear picture of what SQL replace function does.

Let’s look at a proper example where it is used.

Suppose we have a table named STUDENT.

Roll Name City Phone
1 Rohit Patna 585.145.1475
2 Shouvik Jalandhar 225.856.4475
3 Shubh Kolkata 123.456.7890
4 Karan Allahabad 258.258.1452
5 Shivam Palampur 225.142.2589
6 Karan Dhulian 145.585.7854


Now, if we want to replace the (.) character with (-) character in the phone column. Then we have to use REPLACE function along with the UPDATE statement.


UPDATE STUDENT SET Phone = REPLACE(Phone, '.', '-');

See the output.

Roll Name City Phone
1 Rohit Patna 585-145-1475
2 Shouvik Jalandhar 225-856-4475
3 Shubh Kolkata 123-456-7890
4 Karan Allahabad 258-258-1452
5 Shivam Palampur 225-142-2589
6 Karan Dhulian 145-585-7854


So, here you can see that (.) character is replaced with (-) character.

Finally, SQL Replace Function Example Tutorial is over.

Recommended Posts

SQL replicate function

SQL lower function

SQL left function

SQL difference function

SQL char function

SQL charindex function

SQL ascii function

SQL trim function

SQL substring function

SQL concat function

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.