SQL REPLACE() Function: A Step-By-Step Guide

Introduction

When you are working with databases, you want to search or replace an existing substring with the new one. For example, change the dead link to the new one or renaming the product name with a new string as given. SQL provides a useful function REPLACE () to update the content of a string. SQL REPLACE () allows you to replace all occurrences of a substring with a new substring. Keep in mind that search is always case-sensitive, and the REPLACE function compares based on the collation of input expressions.

Online SQL server training will help you understand the real-time working of SQL language and how it can be deployed in different industries and roles together with Data Analysis and various reporting environments. 

So, without further ado, let’s try to understand the REPLACE function in SQL.

SQL REPLACE Function

SQL REPLACE function is a built-in function that facilitates the user to replace all the occurrences of a substring inside a given string using a new substring. Therefore, at any time, when you wish to replace something such as a dead link or a name of the product, use the SQL REPLACE function. 

Syntax of SQL REPLACE Function

The general syntax for REPLACE in SQL server or Replace SQL Server or the SQL REPLACE function is given as: 

REPLACE(string, old_substring, new_substring);

• String: The original string you want to search and update. It can be of a character or binary data type.
• Old-substring: The string to be replaced. It should not be empty. It should not exceed the maximum length that fits on the page.
• New-substring: This is the new replaced string that can be of a character or binary data type.

Let’s look at the SQL Server Return Types- 

Return Types

• If the input value is of varchar datatype, the string function will return varchar; otherwise, it may return nvarchar.
• It returns NULL if any of the arguments is NULL.
• If the string expression is not of type varchar (max), REPLACE the return value at 8000 bytes. To return a value greater than 8,000 bytes, it should be cast explicitly to a large-value data type.

Learn online SQL by enrolling in an online SQL certification program to master basic to advanced SQL concepts like SQL Server Programming, SSIS package, SSRS, SSAS, Power BI, and SQL’s use within the custom applications.

Examples of REPLACE Function in SQL

The REPLACE function in Structured Query Language (SQL) is used to substitute all instances of specific characters in a string with a designated new character or substring. This function can modify a column's value by replacing it with the desired new value. 

Syntax of REPLACE String Function

Syntax 1: This syntax uses REPLACE function with the column name of the SQL table:

SELECT REPLACE(Column_Name, Character/string_to_replace, new_String/character ) AS Alias_Name FROM Table_Name;  

In the syntax below, one first has to specify the name of that particular column whose values they wish to replace.

Syntax 2: This syntax uses REPLACE function with string:

SELECT REPLACE(Original_String, String_to_Replace, New_String) AS Alias_Name;  

Syntax 3: In this, we will demonstrate the utility of the REPLACE function on individual characters:

SELECT REPLACE(Original_String, character_to_Replace, New_Character) AS Alias_Name;  

Examples of REPLACE String function

Example 1: The following SELECT query replaces the character 'T' with 'N' in the original string:

SELECT REPLACE(  'JATBASK,' 'T', 'N' ) AS Website_Name;  

Output:

Example 2: The following SELECT query replaces all the occurrences of character 'O' with the new character 'T' in the original string:

SELECT REPLACE( 'JONBOSK', 'O', 'A') AS Website_Name;  

Output:

Example 3: The following SELECT query replaces the substring 'Training' with the new word 'Courses' in the given original string:

SELECT REPLACE(  'JanBask provides Java certification training.', 'training', 'courses') AS JanBask_Sentence;  

Output:

Example 4: This example uses the REPLACE function with the table in Structured Query Language.

In this particular example, our objective is to generate a fresh SQL table to perform the REPLACE() function on its columns. The syntax used for creating the new table in the SQL database is provided below.:

CREATE TABLE table_name  

(  First_Column_of_table Data Type (character_size of 1st Column),    

Second_Column_of_table Data Type (character_size of the 2nd column ),    

Third_Column_of_table Data Type (character_size of the 3rd column),    

Last_Column_of_table Data Type (character_size of the Nth column)  

);    

The following CREATE statement creates the Student_Marks table:

CREATE TABLE Student_Marks  

(  Student_ID INT NOT NULL PRIMARY KEY,    

Student_First_Name VARCHAR (100),    

Student_Middle_Name VARCHAR (100),    

Student_Last_Name VARCHAR (100),   

Student_Class INT NOT NULL,  

Student_City Varchar(120),  

Student_State Varchar (80),  

Student_Marks INT   

);  

The below INSERT queries insert the records of college Faculties in the Student_Marks table:

INSERT INTO Student_Marks 

(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks) 

VALUES (4001, Joey, Alison, Smith, 4, Birmingham, Alabama, 88);  

 INSERT INTO Student_Marks   

(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   

VALUES ( 4002, Charles, Welsh, Brown, 4, Anchorage, Alaska,, 95 );  

INSERT INTO Student_Marks   

(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   

VALUES (4007, Alex, Darwin, Anderson, 8, Pheonix, Arizona, 91);  

INSERT INTO Student_Marks   

(Student_ID, Student_First_Name, Student_Middle_Name Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)    

VALUES (4004, James, Evans, Thomas, 6, Little Rock, Arkansas, 85);  

INSERT INTO Student_Marks   

(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   

VALUES (4011, Martin, Ezra, Adams, 8, Atlanta, Georgia, 94);  

INSERT INTO Student_Marks   

(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   

VALUES (4006, Taylor, Mason, Bing, 5, Boise, Idaho, 83);  

INSERT INTO Student_Marks   

(Student_ID, Student_First_Name, Student_Middle_Name, Student_Last_Name, Student_Class, Student_City, Student_State, Student_Marks)   

VALUES (4010, Patrick, Wilson, Aaron, 9, Chicago, Chicago, 89);  

The following SELECT statement displays the inserted records of the above Student_Marks table:

SELECT * FROM Student_Marks;

SQL REPLACE Used Cases

Generally, when you migrate a database, the dependent objects also need to be migrated. Let us discuss in a little more depth about the process of handling such changes dynamically using SQL REPLACE or Replace SQL functions with T-SQL constructs. You can delve deep into REPLACE function by enrolling in an online sql training program.

For one database migration project, there are multiple linked server configurations. The linked servers are mentioned or referred to in multiple stored procedures. It is a matter of fact to find or update the stored procedure scripts, but the intent is to automate the complete process and make sure that there is no need for manual updates.

• In this example, the search pattern is “employee,” and also use the custom escape characters to escape the special characters in the search pattern. Here 13 objects are listed to satisfy the search condition employee.
• Now use the loop to go through the listed objects, generate the script, and store the result in the temp table.
• Perform a simple update to change the keyword from CREATE table in the SQL server to ALTER. This way, the script is ready to execute on the target database. In a few cases, there is a need to retain the script.
• This is the right time to copy and paste the new query window and make sure that everything looks fine to run the SQL. You may also automate the process using stored procedures in the SQL.

Translate vs Replace Functions In SQL Server

In SQL Server versions and editions like SQL Server 2017 or higher (say it SQL Server 2019), you can use the TRANSLATE function to translate one or more characters into a complete set of characters. At this glance, REPLACE and TRANSLATE functions do exactly the same thing, but there are significant differences too.

• REPLACE () function replaces all occurrences of a specified string value with another string value.
• For the TRANSLATE () function, it returns the string provided as the first argument after some characters provided in the second argument are translated to the destination set of characters.

The major difference is how both functions deal with multiple characters. REPLACE function replaces one string with another string, so the string should always be in the same order to replace. At the same time, the TRANSLATE function works on characters and replaces each character one by one, regardless of their order. Let us understand the concept in depth with the help of the example below. In the first example, both functions will return the same output, and the query looks like this. 

SELECT REPLACE('123','123','456') AS Replace, TRANSLATE('123','123','456') AS Translate;

The output for this query will be. 

Replace     Translate ------- --------- 456 456

In this case, the REPLACE function returns 456 because the value in the second argument matches the first argument. The TRANSLATE function also returns the same value i.e. 456, because each character in the second argument is available in the same order as the original one. Let us write one more query where both functions return different values. It will help you to understand how both functions are different.

SELECT REPLACE('123','321','456') AS Replace, TRANSLATE('123','321','456') AS Translate;

The output for this query will be:

Replace     Translate -------          --------- 123 654

For the REPLACE function, the second argument does not match the value in the first argument in the same order, so it will not replace the string but return the original string without performing any operation on it.

For the TRANSLATE function, it will work on characters instead of the whole string. As all characters in the second argument are available in the original string, the final output is processed as 654 in this case.

Moving ahead, let us see how these functions work for the non-contiguous strings. Similar to the previous example, you will get a different result, but the string is non-contiguous here.

SELECT REPLACE('1car23','123','456') AS Replace, TRANSLATE('1car23','123','456') AS Translate; 

The output for this query will be:

Replace     Translate -------          --------- 1car23     4car56

Here, The REPLACE function scans the input string for every instance of the old substring and substitutes it with the new string. In the given scenario, when the statement is executed, all instances of "bar" are replaced with "foo," resulting in the string "foo foo foo." Returns the original string without performing any update. At the same time, the TRANSLATE function works on characters and replaces values as given in the output. With these examples, you must know how SQL's REPLACE and TRANSLATE functions work. You can quickly decide which fits your requirements the most. There is one more example where arguments are of different lengths, or we can say there is a discrepancy in the number of characters for various arguments. Here, the first argument contains fewer characters when compared to the second argument.

SELECT REPLACE('123','1234','4567') AS Replace, TRANSLATE('123','1234','4567') AS Translate;

The output for this query will be: 

Replace     Translate ------- --------- 123         456

In the first case, REPLACE function returns the original string while the TRANSLATE function will replace the string based on the characters value instead of looking at the length of parameters. For the TRANSLATE function, it does not matter whether the second argument contains more characters or not. The most important thing is that characters should be the same as the original string. There are some cases when REPLACE function works more suitably, and the TRANSLATE function throws an error. In this example, the second argument has a smaller number of characters than the original string, so REPLACE function works suitably here.

SELECT REPLACE('1234','123','4567') AS Replace;

The output for this query will be:

Replace ------- 45674

Here, REPLACE function works in the same way as it is expected, but the TRANSLATE function will throw an error.

SELECT TRANSLATE('1234','123','4567') AS Translate; 

The output for this query will be:

Error: The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters. 

You should use the REPLACE function when you want to update all the occurrences of a specified string exactly in the same order as it is written. And the TRANSLATE function is useful if you want to replace the occurrence of each character specified, regardless of their order within the specified string.

Conclusion

Being a SQL server developer, you can apply data intelligence in SQL Server using SQL replace or Replace SQL or the other query statements. To know more about different SQL substring functions and how they are used within a query, you may join an online SQL server training course at JanBask Training and practice multiple problems to gain hands-on expertise on multiple SQL case statements.

FAQs

Q1. What is the purpose of the SQL REPLACE function?

Ans:- The purpose of the SQL REPLACE function is to search for a specific substring within a string and replace it with a new substring. Learn more about the SQL REPLACE function by taking up a professional SQL Server certification course.

Q2. How does the SQL REPLACE function work?

Ans:- The SQL REPLACE function works by scanning the input string and replacing all occurrences of the old substring with the new substring.

Q3. Can the SQL REPLACE function be used to replace multiple occurrences of a substring in a string?

Ans:- Yes, the SQL REPLACE function replaces all occurrences of the old substring with the new substring within the input string.

Q4. Is the SQL REPLACE function case-sensitive?

Ans:- Yes, the SQL REPLACE function performs a case-sensitive search, meaning it will only replace occurrences of the old substring that match the case exactly.

Q5. What happens if the SQL REPLACE function cannot find the substring to be replaced?

Ans:- If the SQL REPLACE function cannot find the substring to be replaced, it will do nothing and leave the string unchanged.

Q6. Can the SQL REPLACE function be used to replace values in specific columns of a table?

Ans:- Yes, the SQL REPLACE function can be used in the UPDATE statement to replace values in specific columns of a table based on specified conditions.

Q7. Are there any limitations to using the SQL REPLACE function?

Ans:- One limitation of the SQL REPLACE function is that it replaces all occurrences of the old substring, which may not always be the desired behavior.

Q8. Can the SQL REPLACE function be used with numeric or non-string data types?

Ans:- No, the SQL REPLACE function is specifically designed for string manipulation and cannot be used with numeric or non-string data types.

Q9. Is there a performance impact when using the SQL REPLACE function on large datasets?

Ans:- When using the SQL REPLACE function on large datasets, there may be a performance impact, especially if the operation needs to process a significant number of rows.

Q10. Are there any alternative approaches to achieving string replacements in SQL apart from using the REPLACE function?

Ans:- Yes, there are alternative approaches to achieving string replacements in SQL, such as using string manipulation functions like SUBSTRING and CONCATENATE or utilizing regular expressions in databases that support them.