Oracle – Translate Function


admin Functions, Oracle , , , , ,

Hi Geeks,
Today, we will talk about an Oracle’s inbuilt function TRANSLATE.

TRANSLATE works same as REPLACE function does but with some additional benefits and tricks.
Fortunately, we already have a separate and dedicated blog on REPLACE function.
Let’s have a look at the syntax which will help us to understand the working scenarios of this functions.


TRANSLATE(expr, from_string, to_string)

It can be a column name from a table or a string. If it is a char string, it must be enclosed in single quoration marks.

User provides an argument as a string which needs to be replaced.

User provides an argument as a string which should replace from_string argument.

This function checks each charactor from from_string in expr and replace them with the corresponding values in to_string.

Let’s understand the concept with some examples.


When we run above SQL statement, Oracle will check 1, 2, 3 and 4 in the first argument and will replace each found character with 5, 6, 7 and 8 respectively.


What if user puts more character in the from_string than the to_string?


Concept will be the same. Oracle will check each character in the expr and replace with to_string but it will replace only those characters which has substitute characters in to_string and rest will be replaced by NULL.So, it does not matter if to_string has more characters than from_string.


You can see in the above results. Very first Oracle searches ‘98751234’ in ’12hello34′ and it finds 1, 2, 3 and 4. Then it it goes to to_string values and checks for the substitute values but there are no values for 1, 2, 3 and 4 but 9, 8, 7 and 5. So, Oracle returns only ‘hello’ as output.

This function is very useful if user wants to remove whole alphabetic characters or numeric characters and keep only rest characters.


I hope you enjoyed this blog.
If you have any questions/suggestions, please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..


Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]


SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]


Comment in SQL and PL-SQL

Hi DBgeek Army, Welcome back to the world of Oracle! Comments are very important aspects of our code. No matter […]

Leave a Reply

Your email address will not be published. Required fields are marked *