How and Why to Use the SQL REPLACE Function: A Step By Step Guide
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.
SQL REPLACE Function - Syntax:
The syntax for SQL REPLACE statement is
REPLACE (column_name, string_that_has_to_be_changed, replacement_string);
Breaking the Syntax in Parts:
column_name- Enter the name of the column in which the string has to be matched
string_that_has_to_be_changed - Enter the string that has to be replaced by another string
replacement_string - Enter the string that has to replace the matched string
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.
- 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
console.
CREATE DATABASE hub;
- In order to create a table in that database, first we have to use the database. To use the database enter the following code.
USE hub;
- 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 ;
- 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.
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.
SQL LIKE Operator: Explanation & Examples
SQL CASE Expressions: Explanation & Examples
