Re: single quote in instr Oracle PL/SQL

Holger Baer wrote:
>
> Stop working and read the SQL Reference: what you need is already there.
> It's that often overlooked function translate. However, for the single
> quote you'll need to quote it, as Frank explained.
>
> One funny thing you might note: Although you only want to remove
> characters,
> you have to supply at least *one* characters in the to_string that will be
> kept, else every character from the source string will be replaced.
>
> Examples:
>
> SQL> select translate ('1234''abcd', '1''', '1') demo from dual;
>
> DEMO
> --------
> 1234abcd
>
> SQL> select translate ('1234''abcd', '1''', '') demo from dual;
>
> D
> -
>
> HTH
> Holger

Darn, just after pressing submit I noticed what the documentation has to say: <quote>
You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle interprets the empty string as null, and if this function has a null argument, then it returns null.
</quote>

Cheers,
Holger

-- 
Mit freundlichen Grüßen

Holger Bär

_________________________creating IT solutions
IT-Services
phone   +49(0)7071 9457-451
fax     +49(0)7071 9457-411
h.baer_at_science-computing.de

science + computing ag
Hagellocher Weg 71-75
D-72070 Tuebingen, Germany
www.science-computing.de