rem --------------------------------------------------------------------------
rem Filename: M_SOUNDEX.SQL
rem Purpose:  Converts a string of text into seperate soundex values. Treating 
rem           it as space deliminated words. Useful when searching text strings for a sounds like.
rem            
rem Notes:    USEAGE "select M_SOUNDEX('the cat sat on the mat') from dual;"
rem M_SOUNDEX('THECATSATONTHEMAT')
rem -----------------------------------
rem  T000 C300 S300 O500 T000 M300
rem 
rem 
rem select M_SOUNDEX('the cat sat on the mat') from dual where
rem M_SOUNDEX('the cat sat on the mat') like ('%' || SOUNDEX('cot') || '%');
rem 
rem Date:     01-Mar-2005
rem Author:   Trevor Fairhurst, trevgf@yahoo.com
rem --------------------------------------------------------------------------

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;
/