[Home] [Help]
FUNCTION: SYS.ORA_STRING_DISTANCE
Source
1 function ora_string_distance
2 (s varchar2,
3 t varchar2)
4 return integer is
5 s_len integer := nvl (length(s), 0);
6 t_len integer := nvl (length(t), 0);
7 type arr_type is table of number index by binary_integer;
8 d_col arr_type ;
9 dist integer := 0;
10 begin
11 if s_len = 0 then
12 dist := t_len;
13 elsif t_len = 0 then
14 dist := s_len;
15 -- Bug 18237713 : If source or target length exceeds max DB password length
16 -- that is 128 bytes, then raise exception.
17 elsif t_len > 128 or s_len > 128 then
18 raise_application_error(-20027,'Password length more than 128 bytes');
19 elsif s = t then
20 return(0);
21 else
22 for j in 1 .. (t_len+1) * (s_len+1) - 1 loop
23 d_col(j) := 0 ;
24 end loop;
25 for i in 0 .. s_len loop
26 d_col(i) := i;
27 end loop;
28 for j IN 1 .. t_len loop
29 d_col(j * (s_len + 1)) := j;
30 end loop;
31
32 for i in 1.. s_len loop
33 for j IN 1 .. t_len loop
34 if substr(s, i, 1) = substr(t, j, 1)
35 then
36 d_col(j * (s_len + 1) + i) := d_col((j-1) * (s_len+1) + i-1) ;
37 else
38 d_col(j * (s_len + 1) + i) := LEAST (
39 d_col( j * (s_len+1) + (i-1)) + 1, -- Deletion
40 d_col((j-1) * (s_len+1) + i) + 1, -- Insertion
41 d_col((j-1) * (s_len+1) + i-1) + 1 ) ; -- Substitution
42 end if ;
43 end loop;
44 end loop;
45 dist := d_col(t_len * (s_len+1) + s_len);
46 end if;
47
48 return (dist);
49 end;