Oracle – Translate Function

Translate

admin 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_ONLY NUMERIC_ONLY
hello 1275860934

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

merge

SQL – MERGE Statement

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

update2

SQL – UPDATE Statement part-2

Hi Geeks, In previous blog on UPDATE statement, we learnt, how can we update a column value with a string […]

update

SQL – UPDATE Statement part-1

Hi Geeks, We already have a separate blog on INSERT INTO statement. So today, we will talk about UPDATE statement […]

Leave a Reply

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