phonetically search in oracle function

Description: Converts a string of text into separate soundex values.
Treating it as space delimited words. Useful when searching text
strings for a "sounds like" value.


CREATE OR REPLACE FUNCTION "M_SOUNDEX" (v_text IN VARCHAR2) RETURN VARCHAR2 IS
v_number NUMBER(10);
v_textin VARCHAR2(4000);
v_textout VARCHAR2(4000);
BEGIN
SELECT UPPER (TRIM( v_text )) INTO v_textin FROM dual;
DBMS_OUTPUT.put_line( 'Entered text :' || v_textin);
SELECT '' INTO v_textout FROM dual;
LOOP
SELECT INSTR( v_textin , ' ' , 1 , 1 ) INTO v_number FROM dual;
IF v_number = 0 THEN
SELECT v_textout || ' ' || SOUNDEX(v_textin) INTO v_textout FROM dual ;
EXIT;
ELSE
SELECT v_textout || ' ' || SOUNDEX(SUBSTR (v_textin , 0 , v_number - 1 )) INTO v_textout FROM dual;
SELECT SUBSTR (v_textin , v_number + 1 ) INTO v_textin FROM dual;
END IF;
END LOOP;
RETURN( v_textout );
END M_SOUNDEX;



-- select M_SOUNDEX('the cat sat on the mat') from dual where
-- M_SOUNDEX('the cat sat on the mat') like ('%' || SOUNDEX('cot') || '%');