CoderZone.org

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)

Author: CoderZone
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') || '%');
 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 7