DB GEEK

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:

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

SELECT 
translate('12hello34','1234','5678') as HELLO 
FROM dual;

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?

Example-2

SELECT 
translate('12hello34','98751234','5678') as HELLO 
FROM dual;

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.

SELECT
translate('12h7e58l6l0o934','/0123456789','/') AS char_only,
translate('12h7e58l6l0o934','/abcdefghijklmnopqrstuvwxyz','/') AS numeric_only 
FROM
dual;

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!