Hello, readers! In this article, we will be understanding the working of SQL Server Replace function.
Let us get started!
Working of the SQL Server Replace function
The name ‘Replace’ itself defines its meaning. Yes! You have guessed it right.
SQL Server Replace function enables us to replace a string or a portion of string(substring) and all its occurrences with a new string.
In other programming languages, such as Java or C++, we do have defined functions to replace certain characters or sub-strings with other(new) string.
Let us understand the concept of Replace function with the help of an example.
Consider an Online ticket booking system. If we wish to place and open slots for customers to book tickets only for ‘Pune’, we will have to replace the current city status and all its occurrences to ‘Pune’. This is when the REPLACE function comes into the picture. Using the function, we can replace every occurrence of the current city with ‘Pune’.
Having understood the working, let us now understand the structure of Replace function.
Syntax of REPLACE function
SQL Server Replace function replaces every occurrence or presence of the mentioned string/substring with the new/set string.
REPLACE(string/substring, old_str, new_str)
- string/substring: The string within which the data would be replaced.
- old_str: The string to be replaced.
- new_str: The string value that replaces the old string.
Moreover, the Replace function is case-insensitive.
Let us now understand the implementation of Replace function with the help of some examples.
Implementing Replace function through examples
In the below example,we have replaced the word ‘Morning’ from the string ‘Good Morning’ with the new string ‘Evening’.
SELECT REPLACE( 'Good Morning.', 'Morning', 'Evening' ) output;
Further, now we have replaced multiple occurrences of the string ‘Morning’ with ‘Evening’.
SELECT REPLACE( 'Good Morning. This MORNING seems beautiful!!', 'Morning', 'Evening' ) output;
Moreover, it is clear from the above example that Replace function is totally case-insensitive.
Good Evening. This Evening seems beautiful!!
Now, we have created a table ‘Info’ and added values using the below command:
CREATE TABLE Info ( id INT PRIMARY KEY, City VARCHAR (255) NOT NULL ); INSERT INTO Info ( id, City ) VALUES ( 1, ' Pune ' ), ( 2, ' Satara ' ), ( 3, ' California ' ); Select * from Info;
id City 1 Pune 2 Satara 3 California
In the below example, we have replaced every occurrence of the character ‘a’ with ‘B’ in the column ‘City’, provided the data values of ‘City’ is not null.
UPDATE Info SET City = REPLACE(City,'a','B') WHERE City IS NOT NULL;
By this, we have come to the end of this topic. Feel free to comment below, in case you come across any question.
For more such posts related to SQL Server, please do visit SQL Server JournalDev.