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:
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:
SQL Code:
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:
SQL Code:
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:
SQL Code:
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
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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join