SQL REPLACE Function - Real World Examples:
To understand the REPLACE clause, first let us create a database and a table in it; populate the table (fill the table with data) and then operate on it.
1) Create a database with any database name. In this example, the database is named hub. Write the following code in any SQL console. I am using a MySQL
CREATE DATABASE hub;
2) In order to create a table in that database, first we have to use the database. To use the database enter the following code.
3) Now create a table called 'friends' with four fields namely id, first_name, last_name and age. Enter the following code to create the table.
CREATE TABLE `hub`.`friends` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 30 ) NOT NULL ,
`last_name` VARCHAR( 30 ) NOT NULL ,
`age` INT( 3 ) NOT NULL
) ENGINE = MYISAM ;
4) Populate the table using the INSERT INTO statement.
INSERT INTO `hub`.`friends` (`id`, `first_name`, `last_name`, `age`) VALUES (NULL, 'harry', 'potter', '20'), (NULL, 'ron', 'weasley', '20'), (NULL, 'hermione', 'granger', '20'), (NULL, 'Severus', 'Snape', '40'), (NULL, 'Minerva', 'McGonaggal', '60'), (NULL, 'albus', 'dumbledore', '70'), (NULL, 'Fred', 'Weasley', '24'); (NULL, 'George', 'Weasley', '24');
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.