Re: single quote in instr Oracle PL/SQL

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 23 May 2005 14:01:13 -0700
Message-ID: <1116882073.639472.145400@z14g2000cwz.googlegroups.com>

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_at_ORA92> SELECT REPLACE (TRANSLATE ('1234''~!@abcd',

  2  				'~`!@#$%^&*()_+={}[]|\:;''",.<>?',
  3  				'~'),
  4  		     '~',
  5  		     '')

  6 FROM DUAL
  7 /

REPLACE(

---

1234abcd

scott_at_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_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> SELECT rsc ('1234''~!@abcd') FROM DUAL   2 /

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

---

1234abcd Received on Mon May 23 2005 - 16:01:13 CDT