SQL Server String REPLACE() Function

SQL Server REPLACE() Function

The SQL Server REPLACE() function is used to replace all occurrences of a specified substring within a string with another substring. This function is useful for modifying string data by replacing unwanted or incorrect characters or words.

Syntax

SELECT REPLACE(string, old_substring, new_substring);

The REPLACE() function takes three arguments:

  • string: The string to be searched.
  • old_substring: The substring to be replaced.
  • new_substring: The substring to replace all occurrences of old_substring.

Example SQL Server REPLACE() Function Queries

Let's look at some examples of SQL Server REPLACE() function queries:

1. Basic REPLACE() Example

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

This query replaces all occurrences of 'World' with 'SQL Server' in the string 'Hello World'. The result will be:

result
--------------
Hello SQL Server

2. REPLACE() with a Column

SELECT first_name, REPLACE(first_name, 'a', 'A') AS modified_first_name
FROM employees;

This query replaces all occurrences of the letter 'a' with 'A' in the first_name column for each employee. The result will show the first_name and the modified version as modified_first_name.

3. REPLACE() with a Variable

DECLARE @myString VARCHAR(50);
SET @myString = 'SQL Server 2019';
SELECT REPLACE(@myString, '2019', '2022') AS result;

This query uses a variable to store a string and then replaces '2019' with '2022'. The result will be:

result
--------------
SQL Server 2022

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the REPLACE() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    description VARCHAR(50)
);

In this example, we create a table named example_table with columns for id and description.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, description) VALUES (1, 'Apple Pie');
INSERT INTO example_table (id, description) VALUES (2, 'Banana Bread');
INSERT INTO example_table (id, description) VALUES (3, 'Cherry Tart');

Here, we insert data into the example_table.

Step 3: Using the REPLACE() Function

This step involves using the REPLACE() function to replace substrings in the description column.

SELECT id, description, REPLACE(description, 'Pie', 'Cake') AS modified_description
FROM example_table;

This query retrieves the id, description, and the modified version of the description column for each row in the example_table. The result will be:

id  description    modified_description
--- -------------- --------------------
1   Apple Pie      Apple Cake
2   Banana Bread   Banana Bread
3   Cherry Tart    Cherry Tart

Conclusion

The SQL Server REPLACE() function is a powerful tool for replacing all occurrences of a specified substring within a string with another substring. Understanding how to use the REPLACE() function and its syntax is essential for effective string manipulation and data processing in SQL Server.