1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| CREATE OR REPLACE FUNCTION soundex2(text)
RETURNS text AS
$BODY$DECLARE
sx TEXT;
i INTEGER;
rep1 TEXT[] := '{{"GUI","KI"}, {"GUE","KE"}, {"GA","KA"}, {"GO","KO"}, {"GU","K"}, {"CA","KA"}, {"CO","KO"}, {"CU","KU"}, {"Q","K"}, {"CC","K"}, {"CK","K"}}';
rep2 TEXT[] := '{{"MAC","MCC"}, {"ASA","AZA"}, {"KN","NN"}, {"PF","FF"}, {"SCH","SSS"}, {"PH","FF"}}';
BEGIN
sx := regexp_replace($1, '[- ]', '', 'g');
IF sx = '' THEN
RETURN '';
END IF;
sx := upper(translate(sx, 'ÀÄÂâäàÇçÉÈÊËéèêëÏÎïîÖÔöôÜÛÙüûù', 'aaaaaacceeeeeeeeiiiioooouuuuuu'));
FOR i IN array_lower(rep1, 1) .. array_upper(rep1, 1)
LOOP
sx := replace(sx, rep1[i][1], rep1[i][2]);
END LOOP;
sx := replace(sx, 'J', 'G'); -- Ajout GrandFather
sx := substring(sx from 1 for 1) || translate(substring(sx from 2), 'EIOU', 'AAAA');
FOR i IN array_lower(rep2, 1) .. array_upper(rep2, 1)
LOOP
sx := replace(sx, rep2[i][1], rep2[i][2]);
END LOOP;
sx := regexp_replace(sx, '([^CS]|^)H', '\\1', 'g');
sx := regexp_replace(sx, '([^A]|^)Y', '\\1', 'g');
sx := regexp_replace(sx, '[ATDS]$', '', 'g');
sx := substring(sx from 1 for 1) || replace(substring(sx from 2), 'A', '');
sx := regexp_replace(sx, '(.)\\1+', '\\1', 'g');
sx := substring(sx from 1 for 4);
RETURN sx;
END;$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT;
ALTER FUNCTION soundex2(text) OWNER TO postgres;
COMMENT ON FUNCTION soundex2(text) IS 'Variante francisée de SOUNDEX - Adapté en plpgsql d''après http://sqlpro.developpez.com/cours/soundex/'; |
