Search This Blog

Monday, February 18, 2013

Remove Junk Characters Function


CREATE OR REPLACE FUNCTION xxx_ascii_only_vipul (p_txt IN VARCHAR2)
       RETURN VARCHAR2
    IS
       v_tmp     VARCHAR2 (32767);
       v_clean   VARCHAR2 (32767);
       v_char    VARCHAR2 (3 BYTE);
    BEGIN
       FOR i IN 1 .. LENGTH (p_txt) LOOP
          v_char := SUBSTR (p_txt, i, 1);

         IF    (ASCII (v_char) BETWEEN 32 AND 127)
            OR (ASCII (v_char) IN (9, 10, 13)) THEN
            v_clean := v_clean || v_char;
         END IF;
      END LOOP;

      IF LENGTH (v_clean) != LENGTH (p_txt) THEN
         DBMS_OUTPUT.put_line ('removed '||TO_CHAR(LENGTH(p_txt) - LENGTH(v_clean))||' characters');
      END IF;

      RETURN v_clean;
   END;
   /

No comments:

Post a Comment