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 11.2.0.1.0 – 64bit Production


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

>>Istanbul

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;

>>NULL

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.