[CodeProject daily recommendation] Double Metaphone speech matching algorithm [3, 4] VBScript calls COM; Stored Procedure Implementation and advanced topic

Implement Phonetic ("Sounds-like") Name Searches with Double Metaphone Part III: VBScript and ASP & Database Solutions By Adam Nelson

[3] It was taken over: Because VB is only a typed variable, and the script language is non-typed. therefore, COM must implement a separate version for the scripting language, and then it can be called in the script language vbbench, JScript, and vbbench in ASP.

Implement Phonetic ("Sounds-like") Name Searches with Double Metaphone Part IV: SQL Server and Advanced Database Topics By Adam Nelson

[4] It is quite interesting. As an author of database developers (author), I introduced the advantages of stored procedures and demonstrated the use of SQL Server.

[Use stored procedures]
SQL Server's extended storage process uses Win32 DLL. For installation, you only need to copy the DLL to the SQL Server installation directory.BinnAnd then run in the master database.Sp_addextendedproc, such
Use master
Exec sp_addextendedproc 'xp _ metaphone ', 'xpmetaphone. dll'
The stored procedure only uses the optimized version of unsigned short. If all the keys are calculated and saved in advance, you can use the select statement to query them.

[Create a Cluster Index]
A table can only create one cluster index column, so that the physical storage can be sorted by this column. It must be used when the data size is large. if the Voice key and other information of the application are stored in a table, the key usually does not matter to the cluster index.

[Reduce coupling with existing Program Data Tables]
It is good to store the Voice key in a separate table and at least have a cluster index column. This database creation method reduces coupling.

[Update key with trigger]
The algorithm may change. When a new word arrives, you also need to insert the key. Use the trigger during update and insertion to maintain the corresponding key of the word.

[Scoring matching in select]
(Translator: I usually ignore this case and forget it after a long time) select
Word,
(
Case
When key1 = @ primaryKey then
1 -- Strong match

When key2 = @ primaryKey then
2 -- Normal match

When key1 = @ alternateKey then
2 -- Normal match

When key2 = @ alternateKey then
3 -- Minimal match

Else
4 -- No match
End
) As matchScore
From Words
Where
Key1 = @ primaryKey
Or
Key2 = @ primaryKey
Or
Key1 = @ alternateKey
Or
Key2 = @ alternateKey
Order by word
Go

[Encapsulate the search logic in the stored procedure]
This is complete. The application simply needs to call a stored procedure and does not need to spell SQL statements or something. code reuse is another trick.