AppDividend
Latest Code Tutorials

SQL Translate() Function: SQL Server Translate

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

  1. String: The Source string where changes have to be made.
  2. String_to_replace: The combination of characters must be replaced from the source string.
  3. 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:

  1. If the String_to_replace and Replacement_string lengths are different, it will return an error.
  2. 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.

Recommended Posts

SQL Replicate Function

SQL LTRIM Function

SQL UPPER Function

SQL UNICODE Function

SQL Format Function

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.