w3resource logo


>Sql translate

SQL TRANSLATE() function

Secondary Nav

Description

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

Name Description
char_value A string.
translation_name A character set.

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 :

SELECT TRANSLATE ( 'this is my string', 
'abcdefghijklmnopqrstuvwxyz', 
'defghijklmnopqrstuvwxyzabc' ) 
encode_string 
FROM dual ;

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

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 :

SELECT TRANSLATE( '+91 25-2469782464', 
'0123456789-+','6789012345+-' ) 
encode_number 
FROM dual;

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

Sample table : agents

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 :

SELECT TRANSLATE(agent_name,'abcdefghijk', 
'@#$%^&*()}]' )
FROM agents;

Output

TRANSLATE(AGENT_NAME,'ABCDEFGHIJK','@#$%^&*()}]')
--------------------------------------------------
Al^x
[email protected]@o
[email protected])n
[email protected]@[email protected]
Al&or%
[email protected]) [email protected]
[email protected]@][email protected]
Lu$)%@
An%^rson
Mu]^s(
M$D^n
[email protected]

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.



Join our Question Answer community to learn and share your programming knowledge.

Help the community:

Python: Fizzbuzz

C++: Decimal to binary conversion

JavaScript: Need Help in JavaScript

Python: Help me with this program