SQL Server Replace function – A Comprehensive Guide

Filed Under: SQL Server
SQL SERVER REPLACE FUNCTION

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;

Output:

Good Evening.

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.

Result:

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;

Output:

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;

Output:

SQL Replace With Update Function
SQL Replace With Update Function

Conclusion

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.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages