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