single quote in instr Oracle PL/SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

crazyideas - 23 May 2005 13:33 GMT

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.

Reply to this Message

Frank van Bortel - 23 May 2005 15:34 GMT

> 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?

Yes - a quote <g>
As documented, by the way.
Entry point for documentation: http://otn.oracle.com/docs

Signature

Regards,
Frank van Bortel

Reply to this Message

Holger Baer - 23 May 2005 16:01 GMT

> hi all,
> i'm working on a PL/SQL function (Oracle 9i) which should remove
[quoted text clipped - 15 lines]
> 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
--------
1234abcd

SQL> select translate ('1234''abcd', '1''', '') demo from dual;

D
-

HTH
Holger

Reply to this Message

Holger Baer - 23 May 2005 16:03 GMT

> Stop working and read the SQL Reference: what you need is already there.
> It's that often overlooked function translate. However, for the single
[quoted text clipped - 20 lines]
> 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

Signature

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@science-computing.de

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

Reply to this Message

Barbara Boehmer - 23 May 2005 21:59 GMT

You can TRANSLATE the special characters to just one special character,
then REPLACE that one special character with an empty string. You can
use that method directly in SQL or create a user-defined function, as
demonstrated below

scott@ORA92> SELECT REPLACE (TRANSLATE ('1234''~!@abcd',
2                  '~`!@#$%^&*()_+={}[]|\:;''",.<>?',
3                  '~'),
4          '~',
5          '')
6 FROM DUAL
7 /

REPLACE(
--------
1234abcd

scott@ORA92> CREATE OR REPLACE FUNCTION rsc -- remove special
characters
2 (p_string IN VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 BEGIN
6 RETURN REPLACE (TRANSLATE (p_string,
7                  '~`!@#$%^&*()_+={}[]|\:;''",.<>?',
8                  '~'),
9          '~',
10          '');
11 END rsc;
12 /

Function created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SELECT rsc ('1234''~!@abcd') FROM DUAL
2 /

RSC('1234''~!@ABCD')
----------------------------------------------------------------------------------------------------
1234abcd

Reply to this Message

Holger Baer - 24 May 2005 11:13 GMT

> You can TRANSLATE the special characters to just one special character,
> then REPLACE that one special character with an empty string. You can
> use that method directly in SQL or create a user-defined function, as
> demonstrated below

But it's totally unnecessary. We can safely assume that there will be
at least one character that's not special (else what would be so special
about special characters?) . Just make sure that one 'keep' character
appears in the to_string so that the last parameter is not null, and
be done with it.

Cheers
Holger

Reply to this Message

Barbara Boehmer - 30 May 2005 22:23 GMT

Yes, I see your point now and agree that just using translate, instead
of combining translate and replace, should be more efficient. So, the
revised query would be something like:

scott@ORA92> SELECT TRANSLATE ('1234''~!@abcd',
2 'a~`!@#$%^&*()_+={}[]|\:;''",.<>?',
3 'a')
4 FROM DUAL
5 /

TRANSLAT
--------
1234abcd

1 row selected.

Reply to this Message