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


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