Google+ Followers

Pages

Thursday, August 16, 2012

Function to remove symbols from a string


CREATE OR REPLACE FUNCTION Remove_Symbols(p_text VARCHAR2)
      RETURN VARCHAR2 IS
      v_length NUMBER(10);
      v_clean   VARCHAR2 (2000);
   BEGIN
      SELECT LENGTH(p_text) INTO v_length FROM dual;
      v_clean := SUBSTR (
                    REPLACE (
                       TRANSLATE (
                          p_text
                         ,'~`!@#$%^&*()_-+={}|[]:";''<>?,./'
                         ,'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
                       )
                      ,'~'
                      ,NULL
                    )
                   ,1
                   ,NVL (v_length, LENGTH (p_text))
                 );
      RETURN (v_clean);
   END Remove_Symbols;
/



Example



SELECT Remove_Symbols('7$%$^%fhdfhdf<<<...hdgfgdf~()dfjdjfh') string FROM dual;

No comments:

Post a Comment