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!