Oracle TRANSLATE function

Oracle TRANSLATE(expression, from_string, to_string) function can replace all characters from from_string to to_string by its corresponding character. Also, the important thing is replacement is done by the order of characters. For example, let’s say that you want to translate all Turkish characters to English.

select * from v$version;

Oracle Database 11g Enterprise Edition Release – 64bit Production

select translate('İstanbul', 'ğşüıöçĞŞÜİÖÇ','gsuiocGSUIOC') from dual;


If the number of characters of the from_string do not match with the to_string then the non-equivalent characters convert to NULL.

select translate('Boğaz Köprüsü', 'ğşüıöçĞŞÜİÖÇ','g') from dual;

>>Bogaz Kprs
TRANSLATE and REPLACE functions are very similar in their appearance but can produce very different results.

However, as apposed to REPLACE function, if you write nothing (null) for to_string parameter then you will get NULL for the result.

select translate('Boğaz Köprüsü', 'ğ','') from dual;


select replace('Boğaz Köprüsü', 'ğ','') from dual;

>>Boaz Köprüsü
Posted in SQL
Leave a Reply

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