SQL Server REPLACE() Function

SQL Server is a strong relational database management system (RDBMS) developed to manage large data efficiently. In SQL Server, the REPLACE() function is used to modify or replace a substring within a given string. Taking about the real word uses, the REPLACE() function is vastly used in data processing tasks such as data cleaning. It is also used for data masking purposes.

In this article, we are going to deep dive into the uses and implementation of the REPLACE() function. We will explore various examples along with their respective explanations.

REPLACE() Function

In SQL Server, the REPLACE() function is used to modify a string value. It is used to replace all the occurrences of a substring with a given string. We generally use the REPLACE() function along with the SELECT statement or UPDATE statement. It is also a case insensitive. This means it replaces all the occurrences of a given substring within a string regardless of its case.

Syntax:

REPLACE ( Input String, S1 , S2)

• S1: Substring to be replaced within the given string.
• S2: Replacement Substring.

Demo SQL Server Database

In this tutorial on REPLACE() function, we will use the following table for examples.

Table geeksforgeeks

To create this table in your system, you have to run the below query:

Query:

--to create the table

CREATE TABLE [geeksforgeeks]
(
    [id] [int] UNIQUE,
    [name] [varchar](20),
    [course] [varchar] (50)
) ON [PRIMARY]

--to insert values to the table

INSERT INTO geeksforgeeks ([id],[name],[course])
VALUES (1, 'Vishu', 'Python Self Paced');
INSERT INTO geeksforgeeks ([id],[name],[course])
VALUES (2, 'Neeraj', 'Java Self Paced'); 
INSERT INTO geeksforgeeks ([id],[name],[course])
VALUES (3, 'Aayush', 'SQL Self Paced');
INSERT INTO geeksforgeeks ([id],[name],[course])
VALUES (4, 'Sumit', 'Java Self Paced');
INSERT INTO geeksforgeeks ([id],[name],[course])
VALUES (5, 'Vivek', 'Java Self Paced');

REPLACE() Function Examples

In this, we will various examples along with their respective explanations.

Example 1: REPLACE() Function with SELECT Statement

In this example, we are going to implement the REPLACE() function with SELECT Statement.

Query:

SELECT  REPLACE('Java is used in ML', 'Java', 'Python') as Result;

Output:

replace()with select

Explanation: In the query, we have passed a string ‘Java is used in ML’, here ‘Java’ is the substring we want to replace, and ‘Python’ is the replacement substring. We have passed all the required information to the REPLACE() function. As shown in image, ‘Java’ has been replaced with ‘Python’. Now the new string is ‘Python is used in ML’.

Example 2: REPLACE() function with update statement.

In this example, we will see how we can use REPLACE() function in UPDATE statement. We will update column values with the help of REPLACE() function.

Query:

UPDATE geeksforgeeks
SET course = REPLACE(course, 'Java', 'C++')
WHERE course LIKE '%Java%';

Output:

Replace () with an update statement

Explanation: In the above image, we can observe that id’s 2,4, and 5 have Java in their course column previously. We have specified in the query that, replace all the occurrences of Java with C++ from the courses where Java Word is present.

REPLACE() Function as CASE-Sensitive

As stated earlier, REPLACE function is case insensitive. In some of the cases, we want only some particular case data to be replaced say it to be upper case or lower case. We can solve this problem very easily. We will use binary collation. Doing this, REPLACE() function will treat characters differently based on their binary values.

Query:

SELECT REPLACE('JAVA is good' COLLATE Latin1_General_BIN, 'java', 'Python') as RESULT

Output:

Replace as case-sensitive

Explanation: In the above image, you can see that the input string remains unchanged. We have specified ‘java‘ in lower case but in upper case, JAVA is present in the string. To replace the word, we need to pass the exact word keeping the case unchanged.

Query:

 SELECT REPLACE ( 'JAVA is good'   COLLATE   Latin1_General_BIN,   'JAVA' ,  'Python') as RESULT

Output:

Replaced String

Explanation: We can see that the string has been updated now. In this case, we have specified the same case as in the given string string. Therefore the substring string has been matched and get replaced by our REPLACE() function.

Conclusion

Overall, the REPLACE() Function is used to replace a substring within the given string. It is a case-in-sensitive function. It is used in various real-life applications. One of its real-life applications is data cleaning. REPLACE() is not a standalone function. Therefore, we generally use the REPLACE() function with UPDATE or SELECT Statement. We have covered examples related to replace functions along with how we can use the REPLACE() function as case-sensitive. Now you have a good understanding of the REPLACE() function, you can build queries related to it and get the desired result.