SQL Replace Function

Introduction

In this article, we will learn about SQL Replace function in SQL. SQL REPLACE is a string function that replaces all occurrences of a substring within a string with another substring. It returns the modified string. SQL REPLACE can be used in SELECT, INSERT, UPDATE, or DELETE statements.

SQL REPLACE Function

The SQL REPLACE function is used in SQL to replace the existing value of a specified column or expression with another value. The syntax for the SQL REPLACE function is as follows.

REPLACE(column_name, old_value, new_value)

where column_name is a valid column name in which you want to replace the existing value with a new one, and old_value is an existing row or expression that needs to be replaced with a new one. Can you also use wildcards? and * as replacement values if required by your query

Once we have executed our query, we get desired results, but sometimes it may happen that some rows may not match with old_value, so any other value will not replace them. Rather, they will remain unchanged. In those cases, we need an additional parameter called 'ALL,' which indicates whether all matching rows must be replaced or not

The SQL REPLACE function replaces all occurrences of a substring within a string with a new substring.

The SQL REPLACE function replaces all occurrences of a substring within a string with a new substring.

When you use the SQL REPLACE function, it searches through the string and replaces any instances of a substring with another string.

SQL REPLACE function is available in SQL Server, Oracle, and MySQL

The syntax of the SQL REPLACE() function

SELECT REPLACE("String," 'Substring,' 'Replacement')

SQL REPLACE ( string_expression , string_pattern , string_replacement )

SQL REPLACE ( string_expression , string_pattern , string_replacement )

The REPLACE function replaces all occurrences of a specified substring in a string. The following example replaces all occurrences of the characters "123" with "ABC":

REPLACE('1234 12345', '23', 'ABC') returns '1234 ABCDEF'

Parameters or Arguments

The SQL REPLACE function accepts three arguments. The first is the string expression from which to remove characters, the second is the pattern of characters to remove, and the third is a replacement string that replaces those removed characters.

You must understand what each of these arguments means so that you can effectively use them in your own queries.

In the following example, the SQL REPLACE statement removes the last character of salesperson 5's name (the number 1) and replaces it with the number 9.

The SQL REPLACE function replaces all occurrences of a substring within a string with a new substring.

The syntax for the SQL REPLACE function is

REPLACE(stringToSearchFor, stringToBeReplacedWith, stringToSearchIn)

Here's an example

SELECT * FROM SALESPERSON WHERE LASTNAME = 'CONNORS' AND FIRSTNAME = 'ROBERT';

Note 

The SQL Replace function performs a replacement that is not case-sensitive. 

The SQL Replace function also removes extra records that match query values, inserts, update and delete privileges required for the target table. 

Syntax 

REPLACE ( string_expression , string_pattern , string_replacement )   

So all occurrences of string_expression and string_pattern will be replaced with string_to_replacement regardless of the case of string_expression and string_pattern or string_replacement in the replace statement.  

Syntax

SELECT REPLACE('DEFULTSFFG','HIJ','KLM');        
GO     

This example selects and replaces all the data.

Example

SQL Replace Statement

The following example selects and replaces all data.

The following example uses the Collection function in SQL Replace function. 

Syntax

SELECT REPLACE('This is a Sample'  COLLATE Latin1_General_BIN,        
'Sample', 'desk' );        
GO      

Example

SQL Replace Statement

The above query will select and replace COLLATE Latin1_General BIN file on the desk.

We can use SQL Replace to insert or replace rows with or without the column list.

Syntax

CREATE TABLE Students          
(Id int PRIMARY KEY identity(1,1),        
StudentId int)        
REPLACE INTO Students (Id,StudentId) VALUES (1);         

The above query creates a table with the name "Students" and column name id as studentid and then replaces the data in the Students table where the value is one.

How to replace multiple patterns in a given string statement?

The following example uses the SQL replace function to replace multiple patterns of the expression 3*[4+5]/{6-8}.

Syntax  

SELECT REPLACE(REPLACE(REPLACE(REPLACE('2*[5+5]/{7-8}', '[', '('), ']', ')'), '{', '('), '}', ')');       

Example 

SQL Replace Statement

We can see that the SQL Replace function is nested and called multiple times to replace the corresponding string as per the defined positional values within the SQL Replace function. 

Use Case statement

In this case, when you migrate a database, the dependent objects must be relocated; for example, a linked server or specific column values of detailed tables that reference a renamed table.

Let's get in-depth about the process of handling such changes dynamically using the SQL Replace function with SQL. The server has multiple Linked Server configurations in one complex database migration project.

These linked servers were referred to and mentioned in multiple stored procedures. Finding and replacing the stored procedure scripts is a matter of fact, but the intent is to automate the entire process to ensure that we will not do any manual updates.

Let me take you through the simple steps.

Step 1

In this case, the search pattern is an employee. Also, you see that the custom escape character is used to escape special characters '['and ']' in the search pattern

Syntax

DECLARE @searchPattern VARCHAR(100)= 'EmployeeDetails';      
SELECT DISTINCT       
       'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '      
FROM syscomments      
WHERE TEXT LIKE '%'+REPLACE(REPLACE(@searchPattern, ']', '\]'), '[', '\[')+'%' ESCAPE '\'      
ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' ';      

Example

SQL Replace Statement

The above query declares searchPattern column name from the EmployeeDetails table and selects DISTINCT sp_helptext for OBJECT_SCHEMA_NAME column name In syscomments table and where TEXT contains the searchPattern column for % and '\' statement, which is further arranged according to sp_helptext and the OBJECT_SCHEMA_NAME(id) and the OBJECT_NAME(id) function.

How do you perform a simple update to change the keyword create to the Alter table?

This way, the script is ready to execute on the target database. In some cases, you need to retain the script. That is still fine but don't run this step.

Syntax 

UPDATE @sp_EmployeeDetails      
  SET       
     storedProcedureText = REPLACE(REPLACE(storedProcedureText, 'CREATE PROCEDURE', 'ALTER PROCEDURE'), @searchpattern, @replacestring);      
SELECT storedProcedureText      
FROM @sp_EmployeeDetails;  

This above query updates the sp_EmployeeDetails from EmployeeDetails table Name hence setting the storeProcedureText to SQL. Replace the storeProcedureText and select the storedProceduretext from the EmployeeDetails table. 

Summary

In this article, we've covered the SQL REPLACE function and how it can be used with SQL. We've also looked at some different ways you can use this function, such as replacing all occurrences within a string or just replacing one occurrence. We hope this article has helped you understand how to use the SQL REPLACE function in your own projects!

Recommended Ebook

SQL Queries For Beginners

Download Now!
Similar Articles