SQL Replace Function - All You Need to Know

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 quite useful function REPLACE () to update the content of a string. It 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 performs the comparison based on collation of input expressions.

The general syntax for REPLACE () function in SQL server or Replace SQL Server or the Replace function in SQL 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.

Read: How To Become Expert In Sql Server Developer?

Example: SELECT REPLACE('SQL Tutorial', 'SQL', 'HTML');In this example, we want to replace the SQL with HTML in the original string “SQL Tutorial”. The final output will be “HTML Tutorial” when you run this SQL query. If the function does not find the string, it does nothing. It will return the original string in that case if it does not find any occurrences of the substring.

HTML Tutorial SELECT REPLACE('ABC ABC ABC', 'a', 'c'); In this example, we want to replace the “A” with “C” in the original string “ABC ABC ABC”. The SELECT statement finds the rows or columns first that will be affected by the replacement. When you run this SQL query, the final output will be: cBC cBC cBC

Let’s get started with SQL Server training demo

Return Types

• If the input value is of varchar datatype then 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 the value greater than 8,000 bytes, it should be casted explicitly to a large-value data type.

SQL REPLACE Use Cases

Generally, when you migrate a database, the dependent objects also need to be migrated. Let us discuss in little more depth about the process of handling such changes dynamically using SQL REPLACE or Replace SQL function with T-SQL constructs.

Read: Step By Step SSAS Tutorial For Beginners

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 and generate the script and store the result in the temp table.
• Perform a simple update to change the keyword from CREATE to ALTER. In this way, the script is ready to execute on the target database. In a few cases, there is a need for retaining 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 2019

In 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 exactly do 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.

Remember everything with SQL Server online training

The major difference is how both functions deal with multiple characters. REPLACE function replaces one string with another string, so string should always be in the same order to replace. At the same time, 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 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;

Read: How To Quickly Get An Entry Level SQL Jobs?

The output for this query will be the same as given below. Replace     Translate ------- --------- 456 456

In this case, REPLACE function returns 456 because the value in the second argument matches exactly with 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 of the original one. Let us write one more query where both functions return the 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 different as given below.

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.

Read: How to Increase the Speed of SQL Query Execution

For the TRANSLATE function, it will work on character instead of the whole string. As all characters in the second argument are available in the original string too, so 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 the different result but the string is non-contiguous here.

Read: SAS Tutorial Guide for Beginners

SELECT REPLACE('1car23','123','456') AS Replace, TRANSLATE('1car23','123','456') AS Translate; The output for this query is given below.

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

Here, the REPLACE function will return the original string without performing any update while TRANSLATE function works on characters and replaces values as given in the output. With these examples, you must be sure now how REPLACE and TRANSLATE functions work in SQL. You can quickly decide which fits your requirements the most. There is one more example where arguments are of different length 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 characters value instead of looking at the length of parameters. For the TRANSLATE function, it does not matter either second argument contains more characters or not. The most important thing is that characters should be the same as of the original string. There are some cases when REPLACE function works more suitably and 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:

Read: What is Foreign Key in SQL? How to Set, Add, Create & Use of Foreign Key

Replace ------- 45674

Here, REPLACE function works in the same way as it is expected but 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. Final Words:

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 database coder, you can apply data intelligence in SQL Server using SQL replace or Replace SQL or the other query statements.To know more on different SQL string functions and how they are used within a query, you may join SQL certification course at JanBask training and practice multiple problems to gain hands-on expertise on multiple SQL statements.

Read: How To Quickly Get Entry Level SQL Jobs?

FaceBook Twitter Google+ LinkedIn Pinterest Email