Un nuovo post per approfondire la questione 'similar text' in una direzione diversa rispetto al precedente.

Soundex è un algoritmo standard, offerto nativamente anche da sql, in grado di generare, a partire da una stringa qualunque, un codice che ne descrive il valore fonetico. I codici ritornati da soundex sono simili per stringhe che si leggono im modo simile supponendo che a leggere sia un anglofono.

Quindi la stringa 'munpho' e 'manfo' c'è da aspettarsi che abbiano un valore di soundex molto simile essendo pronunciati da un anglofono quasi allo stesso modo, proviamolo subito infilandoci un 'manto' di test er osservare subito anche un codice con una dizione leggermente diversa:

select soundex('munpho'),soundex('manfo'),soundex('manto')

Otteniamo, come atteso, due valori uguali e uno leggermente diverso:

M510,M510,M530

Al fine di quantificare la differenza è possibile sfruttare la funzione difference la quale ritorna un valore tra 0 e 4 a seconda del grado di similarietà [4 per testi molto simili], nel nostro caso:

select difference('munpho','manfo'),difference('munpho','manto'),difference('munpho','altro')

Ritorna:

4,3,0

La coppia di funzioni soundex e difference permette quindi di fare ricerche su testi simili, almeno nel caso in cui con 'testi simili' si intendano testi pronunciati in modo analogo da un anglofono. Non ha molto senso, tuttavia, nel caso in cui le stringhe da ricercare non abbiano un chiaro valore fonetico [per esempio dei codici alfanumerici], ma l'idea di semplificare la stringa  a un codice può comunque tornare utile.

Il caso che prendiamo ora in esame è il medesimo del precedente post, dei maledettissimo codici di aspirapolvere. In questo scenario i più comuni errori di ricerca riguardano:

- Errori fonetici [Vampyrette viene spesso battuto come vampiret].
- Errori di lettura [Inversioni 1-I, 0-O].
- Omissioni di lettere mute come la H.
- Inversioni
- Battitura scorretta di caratteri ripetuti.

L'ultimo errore è molto frequente in tutti gli ambiti. Il numero di telefono del mio studio contiene un terribile 222 che viene spessissimo acquisito da un interlocutore come 22 [viene colto il fatto che sono più d'uno ma non la quantità esatta]. La ripetizione della medesima lettera, per il cervello umano, ottimizzato per l'analisi delle differenze più che delle similitudini, genera spesso errori. Tornando ai codici, si ha che un codice del tipo GTRFHY654 fatto esclusivamente di differenze risulta più facile da copiare di un GTRRFH6654. In questo ultimo caso si osserverà spesso la perdita di una R o di un 6.

Le inversioni invece sono comunissime e riguardano soprattutto la scrittura su tastiera. Scrivendo anche solo a due dita si invertono spessissimo le lettere di una paorla.

Una ricerca che scovi questi errori, come abbiamo visto può basarsi sulla distanza di Levenshtein, ma anche su algoritmi tipo soundex basati sulla semplificazione. La funzione che segue si occupa di fare una serie di sostiruzioni fonetiche [ph=f, i=y..] e grafiche [i=1, O=0...]. Inoltre riduce tutte le serie identiche ad un unica occorrenza [ttttttt=t, 000=0] e, per scovare le inversioni, ordina alfabeticamente il risultato. Quindi:

astrattissimo=0aiNrst

La funzione proposta non sfrutta il CLR, rinunciando alle prestazioni a favore di un linguaggio più naturale per SQL Server, come vedremo comunque non sarà richiesto un uso massiccio della funzione grazie a una colonna calcolata, ma andiamo per gradi, ecco la funzione:

create function fi_Simplify (@Codice varchar(255))
returns varchar(255)
as
begin
set @Codice=replace(replace(replace(replace(
    replace(replace(replace(replace(
    replace(replace(replace(replace(
    replace(replace(replace(replace(
    replace(replace(replace(@Codice
,'CT','T'),'PH','F'),'CQ','C'),'QU','CU'),'Q','0')
,'OO','U'),'O','0'),'0U','U'),'AI','I'),'Y','I')
,'J','I'),'1','I'),'W','U'),'X','S'),'CK','K')
,'CH','K'),'C','K'),'M','N'),'U','V')

declare @i int
set @i=len(@Codice)-1

set @Codice= dbo.fi_Order(@Codice)

while @i>0
    begin
    if substring(@Codice,@i,1)=substring(@Codice,@i+1,1)
        set @Codice=stuff(@Codice,@i,1,'')
    set @i=@i-1
    end

return @Codice
end

Dove fi_Order ha la forma:

create function [dbo].[fi_Order](@c varchar(255))
returns varchar(255)
as
begin
declare @t table(c char(1))
while len(@c)>0
    begin
    insert into @t values(substring(@c,1,1))
    set @c=substring(@c,2,255)
    end

set @c=''
select @c=@c+c from @t order by c

return @c
end

Se ora affianchimo alla nostra colonna di codici da ricercare una colonna calcolata

CodiceS as dbo.fi_Simplify(Codice)

e la indiciziamo, possimo procedere con ricerche sul testo simile con scritture tipo:

set @CodiceS=dbo.fi_Simplify(@Codice)
select * from tabella where CodiceS=@CodiceS

Come anticipato la funzione fi_Simplify non viene mai richiamata migliaia di volte, ma solo in fase di creazione/modifica del codice in tabella e un'unica volta in fase di estrazione quindi, potendo evitare il CLR, lo evitiamo volentieri.