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;