Substitute Characters in SQL

I have a special problem concerning a SELECT-Statement.

I need a SQL-Funktion (just like Translate) which replaces the occurance of all characters c1 in a String by a corresponding string s1.
Unfortunately Translate is a funktion for Character-Substitution, not for String-Substitution.

Taht's what I want:

Example: SELECT F('EXAMPLE') FROM DUAL;

Result : xyzXAMPLxyz

The Funktion F should be able to substitute all 'E's with 'xyz'.

I cannot use PL/SQL, because I want to create a View with such a SQL-Statement.

If someone has a solution for this problem, please let me know.

Stefan Scheck
E-Mail: scheck_at_ask.uni-karlsruhe.de Received on Tue Mar 21 1995 - 16:44:42 CET