Oracle – Translate Function

Translate

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.

Syntax:

TRANSLATE(expr, from_string, to_string)

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

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

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

Example-1

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.

HELLO
56hello78

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

Example-2

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.

HELLO
hello

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.

CHAR_ONLYNUMERIC_ONLY
hello1275860934

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

WHERE

SQL – WHERE clause

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

comments

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 […]

merge

SQL – MERGE Statement

Hi Devs, As we have already learned INSERT INTO and UPDATE statement, our next very important statement is MERGE. Merge […]

Leave a Reply

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