soundex

Database Type and Version

• Source database type and version: all Oracle versions
• Target database type and version: GaussDB of versions earlier than V2.0-2.7

Function replacement syntax:

CREATE OR REPLACE FUNCTION dsc_ora_ext.dsc_fn_soundex
/* This function is used to replicate the behaviour of Oracle SOUNDEX */
     ( i_str        IN     TEXT ) 
RETURN TEXT
IMMUTABLE 
AS 
    v_soundex              TEXT;
    v_str_to_be_proc       TEXT;
    v_symbol               TEXT;
    v_last_symbol          TEXT     := '';
    v_pos                  INTEGER  := 1;
BEGIN

    IF i_str IS NULL THEN
       RETURN NULL;
    END IF;

    /* The below statement works only if the "fuzzystrmatch" extension is created 
	   if not created, NULL is assigned into v_soundex in EXCEPTION */
    BEGIN
       v_soundex := SOUNDEX(i_str);
    EXCEPTION
       WHEN OTHERS THEN
          v_soundex := NULL;
    END;

    IF v_soundex IS NOT NULL THEN
       RETURN v_soundex;
	END IF;

    WHILE v_soundex IS NULL OR LENGTH(v_soundex) < 4 
	LOOP
        v_str_to_be_proc := UPPER(SUBSTR(i_str, v_pos, 1));
        v_pos            := v_pos + 1;

        CASE WHEN v_str_to_be_proc IS NULL THEN
               -- End of i_str string
               IF v_soundex IS NULL THEN
                   RETURN '';
               ELSE
                   RETURN RPAD(v_soundex, 4, '0');
               END IF;
            WHEN v_str_to_be_proc IN ('B', 'F', 'P', 'V') THEN
               v_symbol := '1';
            WHEN v_str_to_be_proc IN ('C', 'G', 'J', 'K', 'Q', 'S', 'X', 'Z') THEN
               v_symbol := '2';
            WHEN v_str_to_be_proc IN ('D', 'T') THEN
               v_symbol := '3';
            WHEN v_str_to_be_proc = 'L' THEN
               v_symbol := '4';
            WHEN v_str_to_be_proc IN ('M', 'N') THEN
               v_symbol := '5';
            WHEN v_str_to_be_proc = 'R' THEN
               v_symbol := '6';
            ELSE
            -- Not a consonant; no output, but next similar consonant will be re-recorded
               v_symbol := NULL;
        END CASE;

        IF v_soundex IS NULL THEN
           -- First character; only accept strictly English ASCII characters
           IF v_str_to_be_proc ~>=~ 'A' AND v_str_to_be_proc ~<=~ 'Z' THEN
              v_soundex     := v_str_to_be_proc;
              v_last_symbol := v_symbol;
           END IF;
        ELSIF (v_last_symbol IS NULL AND v_symbol IS NOT NULL) OR (v_last_symbol IS NOT NULL AND v_symbol IS NULL) OR (v_last_symbol != v_symbol) THEN
           v_soundex     := v_soundex || v_symbol;
           v_last_symbol := v_symbol;
        END IF;

  END LOOP;

  RETURN v_soundex;
END;