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


Parentheses in SQL

Hey time travellers, Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about […]

Logical Conditions

Oracle – Logical Conditions

Hey time travellers, In this article, we will be discussing Logical Conditions. It is a very important aspect of SQL. […]


Oracle – IN Condition

Hey people, In this Oracle tutorial, we will learn how to use IN condition in SQL. IN condition is used […]

Leave a Reply

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