更新时间:2025-06-04 GMT+08:00
soundex
数据库类型与版本
- 源库类型与版本:Oracle所有版本。
- 目标库类型与版本:GaussDB V2.0-2.7之前版本。
转换替代方案:
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;