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 is given as:
REPLACE(string, old_substring, new_substring);
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.
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
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 function with T-SQL constructs. 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 SQL Server 2017, you can use 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.
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.
REPLACE('123','123','456') AS Replace,
TRANSLATE('123','123','456') AS Translate;
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.
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.
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.
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.
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:
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.
To know more on different SQL string functions and how are they used within a query, you may join SQL certification course at JanBask training and practice multiple problems to gain hands-on expertize on multiple SQL statements.
JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.
Course for testing
Receive Latest Materials and Offers on SQL Server Course