Re: Substitute Characters in SQL

Try REPLACE:

SQL> select replace('EXAMPLE','E','xyz') from dual;  

REPLACE('EX

SQL> Sincerely,

Martin Farber
Independent Oracle Consultant "A Jack of all trades and a slave to one."

                        |  NYNEX Science & Technology
                        |  500 Westchester Ave, Rm 1B-23
                        |  White Plains, NY  10604
                        |     -----------------
                        |  email: farber_at_nynexst.com
                        |  Voice: 914/644-2656
                        |    FAX: 914/644-2216

In article nt6_at_nz12.rz.uni-karlsruhe.de, Stefan Scheck <scheck_at_ask.uni-karlsruhe.de> () writes:
>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 Sat Mar 25 1995 - 00:28:41 CET