Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Convert text to soundex Jump to:  
Category: >> Oracle PL/SQL >> Convert text to soundex Bookmark and Share

<< lastnext >>

Snippet Name: Convert text to soundex

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.

Also see:
» Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» UNISTR
» TRANSLATE
» TO_YMINTERVAL
» TO_TIMESTAMP_TZ
» TO_TIMESTAMP
» TO_SINGLE_BYTE
» TO_NUMBER
» TO_NCLOB
» TO_NCHAR
» TO_MULTI_BYTE
» TO_LOB
» TO_DSINTERVAL
» TO_DATE
» TO_CLOB
» TO_CHAR
» TO_BINARY_FLOAT
» TO_BINARY_DOUBLE
» TIMESTAMP_TO_SCN
» SCN_TO_TIMESTAMP
» ROWIDTONCHAR
» ROWIDTOCHAR
» REFTOHEX
» RAWTONHEX
» RAWTOHEX
» NUMTOYMINTERVAL
» NUMTODSINTERVAL
» HEXTORAW
» DECOMPOSE

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 03rd, 2009

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') || '%');
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 292 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?