| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: single quote in instr Oracle PL/SQL
crazyideas wrote:
> hi all,
> i'm working on a PL/SQL function (Oracle 9i) which should remove
> special characters from a variable before entering it. the function is
> supposed to remove all special characters except for - (hyphen) and
> /(forward slash). It is possible to search the variable using IF and
> OR. for example,
> If( (instr(variable_name,'~') > 0) OR (instr(variable_name,'`') >
> 0)..... Then
> But the list is huge (32 to be exact)
>
> I have 2 questions:
> 1. is there a short cut to this on 9i? i've heard that 10g supports
> something like regexp_instr.
>
> 2. if the answer to 1 is no, how do we catch a single quote in the
> instr method? is there an escape character for it?
>
> thanks,
> (gone)crazy.
>
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
SQL> select translate ('1234''abcd', '1''', '') demo from dual;
D
-
HTH
Holger
Received on Mon May 23 2005 - 10:01:42 CDT
 |
 |