Pin Me

SQL Replace Function: Explanation & Examples

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.

    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

  • slide 2 of 3

    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



    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.

    USE hub;

    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` (


    `first_name` VARCHAR( 30 ) NOT NULL ,

    `last_name` VARCHAR( 30 ) NOT NULL ,

    `age` INT( 3 ) NOT NULL


    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.

    SQL REPLACE Function 

    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.

    SQL LIKE Operator: Explanation & Examples

    SQL CASE Expressions: Explanation & Examples

    SQL Delete Statement: Syntax & Examples

    SQL Select Statement: Syntax & Examples

© Copyright 2017