René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

translate [Oracle SQL]

translate (string, search-characters, replace-characters)
Translate searches for search-characters in string and replaces each character found with the character from replace-characters at the search-characters's respective position.
In the following example, translate is used to make sure that a table's column only contains vowels:
create table test_ (
  only_vowels varchar2(50) check (translate(upper(only_vowels),':AEIOU',':') = ':')
);

insert into test_ values ('abc');
insert into test_ values ('io');
insert into test_ values (null);
insert into test_ values ('u');

select * from test_;

drop table test_;
The abc record is not inserted as it contains a consonant.
ONLY_VOWELS
--------------------------------
io

u