Introduction
In this chapter, we will learn about the REPLACE Statement in SQL and how to use the REPLACE Statement in SQL to replace the data in the database.
SQL REPLACE statement
The Replace statement is used to replace all occurrences of a specified string value with another string value. The Replace statement inserts or replaces values in a table. Use the Replace statement to insert new rows in a table and/or replace existing rows in a table.
Replace statement will search a table for a match based on any one of the unique keys defined for that table.
A unique key is defined as either a Primary key constraint or a Unique constraint.
- If this statement does not find a match, it will insert a new row into the table.
- If this statement finds a match, it will replace that row with the column values provided.
- If the Replace statement finds a match of more than one row, it will replace the first row and it will delete all other matching rows in this table.
The Values clause must return a value for every column in the table column_name list in that same order.
Replace statements can be used to merge two tables based on common column values.
The table referenced as Replace[INTO] is the primary table and must have at least one column defined as a unique key to be used for matching. If columns referenced by the "values" clause are not of the same data type as the columns defined in the Replace table, automatic data type conversion is attempted.
Note
The Replace statement performs a replacement that is not case-sensitive.
The Replace statement also removes extra records that match query values, insert, update, and delete privileges that are required for the target table.
Syntax
- REPLACE ( string_expression , string_pattern , string_replacement )
So all occurrences of string_expression and/or string_pattern will be replaced with string_to_replacement regardless of the case of string_expression and/or 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
The following example Selects and Replaces all the data.
The following example uses the Collection function in Replace statement.
Syntax
- SELECT REPLACE('This is a Sample' COLLATE Latin1_General_BIN,
- 'Sample', 'desk' );
- GO
The above query will select and replace the COLLATE Latin1_General BIN file to desk.
We can use 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
We can see that the Replace function is nested and it is called multiple times to replace the corresponding string as per the defined positional values within the SQL Replace function.
In this case, when you migrate a database, the dependent objects also need to be migrated; for example, a linked server or specific column values of specific 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. In one complex database migration project, the server has multiple Linked Server configurations.
These linked servers were referred to and mentioned in multiple stored procedures. It is a matter of fact to find and replace the stored procedure scripts but the intent is to automate the entire process to make sure that we are not going to 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
The above query declares searchPattern column name from EmployeeDetails table and select 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 to 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 just 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 Replace the storeProcedureText and selecting the storedProceduretext from EmployeeDetails table.
Summary
In the next chapter, we will learn how to work with the SQL DROP statement.