SQL REPLACE Function | Replace Substring in a String

SQL Replace Function is an inbuilt Function in SQL Server which replaces all occurrences of a sub-string by a new substring in a string. In this article, we will discuss the SQL Replace Function. Also, we will discuss a few examples to show its usage.

Note: This function is case-insensitive. Therefore, it treats lowercase and uppercase characters as same.

Syntax

REPLACE(string, old_string, new_string)

Parameters

The SQL Replace Function has three parameters. The description of the parameters is as follows:

  • string: The first parameter is the string in which you want to replace a sub-string. Also, it can be the name of the column of the table.
  • old_string: The second parameter is a valid string which the function will search in the string. It will be replaced by the string in the third parameter.
  • new_string: The third parameter is the value of the string that you want to replace in place of the old string.

Return Value

The Replace Function in SQL Server returns the original string with new_string replacing the old_string in it. However, this function does not support regular expressions so you cannot replace a string based on a pattern.

Support

This function is supported in SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse.

Examples

Let’s discuss a few examples to demonstrate the usage of Replace Function in SQL Server.

Example 1: SELECT Query

For instance, consider a basic example to use the SQL Replace in a SELECT Query.

SELECT REPLACE ('I love Dogs. Dogs are cute.', 'Dogs', 'Cats');
+---------------------------------------------------------+
| REPLACE ('I love Dogs. Dogs are cute.', 'Dogs', 'Cats') |
+---------------------------------------------------------+
| I love Cats. Cats are cute.                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)

In the above example, we replace the sub-string ‘Dogs’ with ‘Cats’ in a string. You can observe function replaces all occurrences of the old_string with the new value.

Sample Table

For the following examples, consider the given sample table for reference.

select * from user_description;
+-----------+---------------------------------------------------+
| user_name | user_description                                  |
+-----------+---------------------------------------------------+
| Chandler  | Chandler Muriel Bing is a fictional character.    |
| Monica    | Monica E. Geller is a fictional character. 	|
| Ross      | Ross Eustace Geller, Ph.D., is a fictional charact|
+-----------+---------------------------------------------------+

Example 2: SELECT Query With Column

In this example, we will apply the Replace Function in SQL Server on a column of the sample table.

select user_name, REPLACE(user_description, 'fictional', 'real') from user_description;
+-----------+---------------------------------------------------+
| user_name | user_description                                  |
+-----------+---------------------------------------------------+
| Chandler  | Chandler Muriel Bing is a real character.         |
| Monica    | Monica E. Geller is a real character. 	        |
| Ross      | Ross Eustace Geller, Ph.D., is a real charact.    |
+-----------+---------------------------------------------------+

We replace the sub-string fictional with a new sub-string real while selecting the user_description column. You can observe that the function replaces the occurrences of the old string in all the rows.

Example 3: UPDATE Query

Similarly, we can also the function in an update query to replace all occurrences of a string.

UPDATE user_description SET user_description = REPLACE(user_description, 'fictional', 'real');
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0

SELECT * from user_description;
+-----------+---------------------------------------------------+
| user_name | user_description                                  |
+-----------+---------------------------------------------------+
| Chandler  | Chandler Muriel Bing is a real character.         |
| Monica    | Monica E. Geller is a real character. 	        |
| Ross      | Ross Eustace Geller, Ph.D., is a real charact.    |
+-----------+---------------------------------------------------+

As you can observe in the above example, the function updates all the rows of the table replacing occurrences of fictional with real in all the rows.

Conclusion

In conclusion, we discussed the SQL Server Replace Function in this article. It is very helpful in replacing a sub-string with another in a string. You can learn more about it on the Official Microsoft Documentation.

Also, you can learn more about SQL on Concatly!

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *