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