SQL Server REPLACE() Function

 The REPLACE() function in SQL Server is used to replace all occurrences of a specified substring within a string with another substring. It operates on text or string data types and returns a new string with the replacements.

Syntax:

REPLACE (string_expression, string_to_replace, string_to_replace_with)

• string_expression: The original string in which the replacement will be made.
• string_to_replace: The substring that you want to replace.
• string_to_replace_with: The substring that will replace string_to_replace.

Example:

1. Basic Usage:

   SELECT REPLACE('Hello World', 'World', 'SQL Server');
   

   Result:

   Hello SQL Server
   

2. Replacing multiple occurrences:

   SELECT REPLACE('apple orange apple banana', 'apple', 'fruit');
   

   Result:

   fruit orange fruit banana
   

3. Handling empty strings: If any of the parameters is an empty string, the result will be the original string with no change for replacements.

   SELECT REPLACE('Hello World', '', 'X');
   

   Result:

   Hello World
   

4. Replacing part of a string:

   SELECT REPLACE('123-456-7890', '-', '/');
   

   Result:

   123/456/7890
   

Notes:

• The REPLACE() function is case-sensitive, meaning it distinguishes between uppercase and lowercase characters.
• It does not modify the original string but returns a new string with the replacements.