SQL TRANSLATE() function

TRANSLATE() function

The SQL TRANSLATE() function replaces a sequence of characters in a string with another sequence of characters. The function replaces a single character at a time.

Syntax:

TRANSLATE(char_value USING translation_name)

Parameters:

DB2 Syntax:

TRANSLATE(string-expression,to-string,from-string,pad)

PostgreSQL Syntax:

TRANSLATE(string text, from text, to text)

Oracle Syntax:

    TRANSLATE(char_value USING {CHAR_CS | NCHAR_CS})

Example:

To get the string where all occurrences of 'abcdefghijklmnopqrstuvwxyz' will be replaced with corresponding characters in the string 'defghijklmnopqrstuvwxyzabc', the following SQL statement can be used:

SQL Code:

-- This SQL query translates characters in a given string according to a specified mapping and returns the result with an alias.
-- SELECT statement begins
SELECT 
    TRANSLATE('this is my string', -- Original string to be translated
              'abcdefghijklmnopqrstuvwxyz', -- Mapping of characters to be replaced
              'defghijklmnopqrstuvwxyzabc') -- Mapping of replacement characters
    AS encode_string -- Alias the result of the translation as 'encode_string'
FROM 
    dual; -- Dual is a dummy table in Oracle, used here as a placeholder since we're not actually querying any table

Explanation:

• This SQL code is a SELECT statement that demonstrates the use of the TRANSLATE function to perform character translation on a string.


• The original string 'this is my string' is provided as the first argument to the TRANSLATE function.


• The second argument specifies the mapping of characters to be replaced, covering the entire lowercase English alphabet ('abcdefghijklmnopqrstuvwxyz').


• The third argument specifies the mapping of replacement characters. In this case, each character is shifted by three positions to the right, wrapping around if needed ('defghijklmnopqrstuvwxyzabc').


• The result of the character translation is assigned an alias 'encode_string'.


• The query operates on the 'dual' table, which is a system-generated table in Oracle commonly utilized as a placeholder for single-row queries or expressions.

SQL TRANSLATE() function with numeric values

To get a new string from the string +91 25-2469782464 where all occurrences of '0123456789-+' will be replaced with corresponding characters in the string '6789012345+-' , the following SQL statement can be used:

SQL Code:

-- This SQL query translates characters in a given string according to a specified mapping and returns the result with an alias.
-- SELECT statement begins
SELECT 
    TRANSLATE('+91 25-2469782464', -- Original string to be translated
              '0123456789-+', -- Mapping of characters to be replaced
              '6789012345+-') -- Mapping of replacement characters
    AS encode_number -- Alias the result of the translation as 'encode_number'
FROM 
    dual; -- Dual is a dummy table in Oracle, used here as a placeholder since we're not actually querying any table

Explanation:

• This SQL code is a SELECT statement that demonstrates the use of the TRANSLATE function to perform character translation on a string.
• The original string '+91 25-2469782464' is provided as the first argument to the TRANSLATE function.
• The second argument specifies the mapping of characters to be replaced ('0123456789-+').
• The third argument specifies the mapping of replacement characters ('6789012345+-').
• Each character is replaced based on its position in the mappings provided. For example, '0' is replaced with '6', '1' with '7', '2' with '8', and so on.
• The result of the character translation is assigned an alias 'encode_number'.
• The query operates on the 'dual' table, a system-generated table in Oracle often employed as a placeholder for single-row queries or expressions.

Here, "encode_number" is an alias which will come as a column heading to the output.

Output:

ENCODE_NUMBER
-----------------
-57 81+8025348020

SQL TRANSLATE() function with character values

 
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To get a new string from agent_name where all occurrences of 'abcdefghijk' will be replaced with corresponding characters in the string '@#$%^&*()}]' from the agent table, the following SQL statement can be used:

SQL Code:

-- This SQL query translates characters in a column of the 'agents' table according to a specified mapping and returns the result.
-- SELECT statement begins
SELECT 
    TRANSLATE(agent_name, -- Original column data to be translated
              'abcdefghijk', -- Mapping of characters to be replaced
              '@#$%^&*()}]') -- Mapping of replacement characters
FROM 
    agents; -- Query data from the 'agents' table

Explanation:

• This SQL code is a SELECT statement that demonstrates the use of the TRANSLATE function to perform character translation on a column of data from the 'agents' table.
• The TRANSLATE function is applied to the column 'agent_name' from the 'agents' table.
• The second argument specifies the mapping of characters to be replaced ('abcdefghijk').
• The third argument specifies the mapping of replacement characters ('@#$%^&*()}]').
• Each character in the 'agent_name' column is replaced based on its position in the mappings provided. For example, 'a' is replaced with '@', 'b' with '#', and so on.
• The result of the character translation is returned as part of the query result set.
• The query is executed against the 'agents' table, which presumably contains data about agents, and retrieves the translated 'agent_name' column.

Output:

TRANSLATE(AGENT_NAME,'ABCDEFGHIJK','@#$%^&*()}]')
--------------------------------------------------
Al^x
Su##@r@o
B^n}@m)n
R@m@sun%@r
Al&or%
R@v) Kum@r
S@nt@]um@r
Lu$)%@
An%^rson
Mu]^s(
M$D^n
Iv@n

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: TRIM
Next: Order By