written by: vishalseafarer•edited by: Amber Neely•updated: 9/18/2011
SQL Replace function is similar to the find and replace operation in many text editors that you have used. Learn from this step by step tutorial on how to use the REPLACE function in SQL.
slide 1 of 3
SQL REPLACE Function: Explanation and Syntax
There are occasions when a lot of data (that is of the same case) needs to be replaced with another data. In such cases the replace() function is used. The SQL REPLACE function uses three arguments to replace the data that is matched, with the data that is to be replaced.
Now you have a database with a table that is ready to be operated upon using the REPLACE function.
Use the following statements in your MySQL console and see the results:
SELECT REPLACE(last_name, 'Weasley', 'I am a Weasley') AS changed_last_name from friends;
SELECT REPLACE(last_name, 'Granger', 'I am a Granger') AS changed_last_name from friends;
SELECT REPLACE(age, 20, 21) AS changed_age from friends;
Note: Since age is of type int, the best practice is to enter the string that has to be replaced with the replacement string without quotes. Although no error will be returned in MySQL, it is good to avoid using single quotes.
Common Learner's Error:
The data that has to be matched is case-sensitive. In the above example, Weasley (with a W) is replaced with I am Weasley. If there is an entry weasley (with a w) it won't be replaced with I am Weasley. So weasley, Weasley, WEASLEY are not the same.
slide 3 of 3
Links to Other Articles Related to SQL:
Did you like the article that you just read? Read the following articles that illustrate how to use other SQL operators and statements.