Records with close values

Question

I need a light, I have to make an appointment to bring me registration with values close. I tried using SOUNDEX and even the phonetics function, but I did not succeed. Example: Let’s say the user is looking for a drug called Dorflex, but he doesn’t remember the exact name and ends up writing Corflex. When he makes the appointment I have to suggest to him the correct name (Dorflex) and all medications that have in the name the word Dorflex.

Answer 1

I wrote a function that I use on Oracle. Comparing DORFLEX with CORFLEX the function tells me that they are 86% similar. You can adapt it to your system.

create or replace function grausimilar(str1 in varchar, 
                               str2 in varchar ) 

return number
is
c1 number;
c2 number;
lcs number;
temp number;
i number;
maximo number;
minimo number;
s1 varchar(1024);
s2 varchar(1024);
begin       
  s1 := trim( str1 );
  s2 := trim( str2 ); 

if ( s1 = '' ) then
 begin
   if ( s2 = '' ) then
      return 0;
   else
      return length( s2 );
  end if;
 end;
end if; 

if ( s2 = '' ) then
     return length( s1 );
end if;     


maximo := length( s1 );
if ( length( s2 ) >  length( s1 )) then
   maximo := length( s2 );
end if;


c1 := 1;
c2 := 1;
lcs := 0;
temp := 0;

while ((c1 <= length( s1 )) and (c2 <= length(s2))) 
loop
    if ( substr(s1, c1, 1 ) = substr( s2, c2, 1 )) then
        lcs := lcs + 1;
    else 
     begin
        if (c1 < c2) then
             c2 := c1;
        else 
            c1 := c2;
        end if;    
        i := 0;
        while ( i < maximo )
        loop    
            if ( (c1 + i < length(s1)) and ( substr( s1, c1 + i, 1 ) =   substr( s2, c2, 1 ))) then 
               begin
                c1 := c1 + i;
                exit;
              end;
            end if;  
            if ((c2 + i < length(s2)) and (substr( s1, c1, 1 ) = substr( s2, c2 + i, 1 ))) then
              begin
                c2 := c2 + i;
                exit;
              end;  
            end if;
            i := i + 1;
        end loop;
      end;
    end if;  
    c1 := c1 + 1;
    c2 := c2 + 1;
end loop;

return round( ((( lcs * 2 ) / (length(s1) + length(s2))) * 100 ));
end;