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
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.



Is this content useful for you?

 


You might be using Adblocker. Since w3resource does not charge anything from users, all of our expenses (e.g. Hosting, Content Creation, Development etc.) are met by advertisement. Please whitelist w3resource.