SQL> CREATE OR REPLACE FUNCTION number_stripper(p_string VARCHAR2) RETURN VARCHAR2
2 IS
3 v_current_element NUMBER;
4 v_char_length NUMBER;
5 v_ret_val VARCHAR2 (2000);
6 v_current_char VARCHAR2 (1);
7 BEGIN
8 v_char_length := LENGTH (p_string);
9 v_current_element := 1;
10
11 LOOP
12 EXIT WHEN v_current_element > v_char_length;
13 v_current_char := SUBSTR (p_string, v_current_element, 1);
14
15 IF TRANSLATE (v_current_char, '1234567890', '**********') != '*'
16 OR v_current_char = '~'
17 THEN
18 v_ret_val := v_ret_val || v_current_char;
19 END IF;
20
21 v_current_element := v_current_element + 1;
22 END LOOP;
23
24 RETURN (v_ret_val);
25 END;
26 /
Function created.
SQL> select number_stripper('asdf123') from dual;
NUMBER_STRIPPER('ASDF123')
--------------------------------------------------------------------------------
asdf
SQL>