SQL Server TRANSLATE() function replaces several single-characters, one-to-one translation in one operation.
SQL Translate
SQL TRANSLATE() is a built-in function that replaces the sequence of characters with another set of characters one by one. The TRANSLATE() method returns a string from the 1st argument after the characters specified in the second argument are translated into the characters set in a third argument.
Syntax
SELECT TRANSLATE (String, String_to_replace, Replacement_String);
Parameters
- String: The Source string where changes have to be made.
- String_to_replace: The combination of characters must be replaced from the source string.
- Replacement_String: The Combination of characters that will replace 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.
NOTE:
- If the String_to_replace and Replacement_string lengths are different, it will return an error.
- If any argument is declared NULL, then the function will return NULL.
Example 1:
SELECT TRANSLATE ('AppDiviDenD', 'ADi', 'adI');
Output
'appdIvIdend'
Query 2
SELECT TRANSLATE ('222App', '2A', '3i');
Output
'333ipp'
Query 3
SELECT TRANSLATE ('[408] 555 6789', '[]', '()');
Output
(408) 555 6789
Let’s Apply this function in a Table.
Table: Employee
Emp_id | Emp_name | City | State | Salary |
101 | Rohit | Patna | Bihar | 30000 |
201 | Shivam | Jalandhar | Punjab | 20000 |
301 | Karan | Allahabad | Uttar Pradesh | 40000 |
401 | Suraj | Kolkata | West Bengal | 60000 |
501 | Akash | Vizag | Andhra Pradesh | 70000 |
Suppose we want to replace ‘abcdefghijk’ with ‘@#$%^&*()}]’ characters just for fun, then the following query has to be written.
Query
SELECT TRANSLATE (Emp_name,'abcdefghijk', '@#$%^&*()}]’)
FROM Employee;
Output
Emp_name |
Ro()t |
S()v@m |
K@r@n |
Sur@} |
A]@s( |
Here, you can see that all the strings having characters’ abcdefghijk’ had been 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 translations 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.
That’s it for this example.