SQL Translate Function Example | SQL Server Translate
SQL TRANSLATE function is used for replacing the sequence of characters with another set of characters one by one. The TRANSLATE() function returns a string from the 1st argument after the characters specified in the 2nd argument are translated into the characters specified in a third argument.
SQL Translate Function
SQL Server TRANSLATE() function is used to replace several single-characters, one-to-one translation in one operation.
SELECT TRANSLATE (String, String_to_replace, Replacement_String);
- String: The Source string where changes have to be made.
- String_to_replace: The combination of characters that have to be replaced from the source string.
- Replacement_String: The Combination of characters which will be used for replacing characters from String_to_replace.
The Function will replace the 1st character in the string_to_replace with the first character in the Replacement_string.
Then it will replace the second character in the string_to_replace with the second character in the Replacement_string, and this goes on till the end of the string.
- If the lengths of the String_to_replace and Replacement_string are different, then it will return an error.
- If any argument is declared NULL, then the function will return NULL.
SELECT TRANSLATE ('AppDiviDenD', 'ADi', 'adI');
SELECT TRANSLATE ('222App', '2A', '3i');
SELECT TRANSLATE (' 555 6789', '', '()');
(408) 555 6789
Let’s Apply this function in a Table.
Suppose, we want to replace ‘abcdefghijk’ with ‘@#$%^&*()}]’ characters just for fun than the following query has to be written.
SELECT TRANSLATE (Emp_name,'abcdefghijk', '@#$%^&*()}]’) FROM Employee;
Here, you can see the all the string having characters ‘abcdefghijk’ had replaced by ‘@#$%^&*()}]’.
SQL TRANSLATE() vs. SQL REPLACE()
The behavior of the TRANSLATE() function is similar to calling the multiple REPLACE() functions.
However, the TRANSLATE() function does not replace all the occurrences of the character with a new one.
It is the difference between the TRANSLATE() function and calling multiple REPLACE() functions, and each REPLACE() function call would replace all the relevant characters.
In this tutorial, you have learned how to use the SQL Server TRANSLATE() function to replace the several single-character, one-to-one translation in one operation.
Oracle / PLSQL: TRANSLATE Function
The Oracle/PLSQL TRANSLATE function replaces the sequence of characters in the string with another set of characters.
However, it replaces a single character at a time.
For example, it will replace the first character in the string_to_replace with the first character in the replacement_string. Then it will replace the second character in the string_to_replace with the second character in the replacement_string, and so on.
Finally, SQL Translate Function Example | SQL Server Translate Example is over.