Disgruntled with Soundex I went looking for a better phonetic matching algorithm.
Turns out there is a rather good one called Metaphone, which comes in two variants (Simple and Double)
I could find the source for this in C++, but I wanted to have it as a user function.
So here it is:
CREATE FUNCTION dbo.Metaphone(@str as varchar(70))
RETURNS varchar (25)
/*
Metaphone Algorithm
Created by Lawrence Philips.
Metaphone presented in article in "Computer Language" December 1990 issue.
Translated into tSQL by Keith Henry (keithh_AT_lbmsolutions.com)
*********** BEGIN METAPHONE RULES ***********
Lawrence Philips' RULES follow:
The 16 consonant sounds:
 ZERO represents "th"

B X S K J T F H L M N P R 0 W Y
Drop vowels
Exceptions:
Beginning of word: "ae", "gn", "kn", "pn", "wr" > drop first letter
Beginning of word: "x" > change to "s"
Beginning of word: "wh" > change to "w"
Beginning of word: vowel > Keep it
Transformations:
B > B unless at the end of word after "m", as in "dumb", "McComb"
C > X (sh) if "cia" or "ch"
S if "ci", "ce", or "cy"
SILENT if "sci", "sce", or "scy"
K otherwise, including in "sch"
D > J if in "dge", "dgy", or "dgi"
T otherwise
F > F
G > SILENT if in "gh" and not at end or before a vowel
in "gn" or "gned"
in "dge" etc., as in above rule
J if before "i", or "e", or "y" if not double "gg"
K otherwise
H > SILENT if after vowel and no vowel follows
or after "ch", "sh", "ph", "th", "gh"
H otherwise
J > J
K > SILENT if after "c"
K otherwise
L > L
M > M
N > N
P > F if before "h"
P otherwise
Q > K
R > R
S > X (sh) if before "h" or in "sio" or "sia"
S otherwise
T > X (sh) if "tia" or "tio"
0 (th) if before "h"
silent if in "tch"
T otherwise
V > F
W > SILENT if not followed by a vowel
W if followed by a vowel
X > KS
Y > SILENT if not followed by a vowel
Y if followed by a vowel
Z > S
*/
AS
BEGIN
Declare@Result varchar(25),
@str3char(3),
@str2 char(2),
@str1 char(1),
@strp char(1),
@strLen tinyint,
@cnt tinyint
set @strLen = len(@str)
set@cnt=1
set@Result=''
Process beginning exceptions
set @str2 = left(@str,2)
if @str2 in ('ae', 'gn', 'kn', 'pn', 'wr')
begin
set @str = right(@str , @strLen  1)
set @strLen = @strLen  1
end
if@str2 = 'wh'
begin
set @str = 'w' + right(@str , @strLen  2)
set @strLen = @strLen  1
end
set @str1 = left(@str,1)
if @str1= 'x'
begin
set @str = 's' + right(@str , @strLen  1)
end
if @str1in ('a','e','i','o','u')
begin
set @str = right(@str , @strLen  1)
set @strLen = @strLen  1
set@Result=@str1
end
while @cnt <= @strLen
begin
set @str1 = substring(@str,@cnt,1)
if @cnt <> 1
set@strp=substring(@str,(@cnt1),1)
elseset@strp=' '
if @strp<> @str1
begin
set @str2 = substring(@str,@cnt,2)
if @str1in('f','j','l','m','n','r')
set@Result=@Result + @str1
if @str1='q'set @Result=@Result + 'k'
if @str1='v'set @Result=@Result + 'f'
if @str1='x'set @Result=@Result + 'ks'
if @str1='z'set @Result=@Result + 's'
if @str1='b'
if @cnt = @strLen
if substring(@str,(@cnt  1),1) <> 'm'
set@Result=@Result + 'b'
else
set@Result=@Result + 'b'
if @str1='c'
if @str2 = 'ch' or substring(@str,@cnt,3) = 'cia'
set@Result=@Result + 'x'
else
if @str2in('ci','ce','cy')and@strp<>'s'
set@Result=@Result + 's'
elseset@Result=@Result + 'k'
if @str1='d'
if substring(@str,@cnt,3) in ('dge','dgy','dgi')
set@Result=@Result + 'j'
elseset@Result=@Result + 't'
if @str1='g'
if substring(@str,(@cnt  1),3) not in ('dge','dgy','dgi','dha','dhe','dhi','dho','dhu')
if @str2 in ('gi', 'ge','gy')
set@Result=@Result + 'j'
else
if(@str2<>'gn') or ((@str2<> 'gh') and ((@cnt + 1) <> @strLen))
set@Result=@Result + 'k'
if @str1='h'
if (@strp not in ('a','e','i','o','u')) and (@str2 not in ('ha','he','hi','ho','hu'))
if@strp not in ('c','s','p','t','g')
set@Result=@Result + 'h'
if @str1='k'
if @strp <> 'c'
set@Result=@Result + 'k'
if @str1='p'
if @str2 = 'ph'
set@Result=@Result + 'f'
else
set@Result=@Result + 'p'
if @str1='s'
if substring(@str,@cnt,3) in ('sia','sio') or @str2 = 'sh'
set@Result=@Result + 'x'
elseset@Result=@Result + 's'
if @str1='t'
if substring(@str,@cnt,3) in ('tia','tio')
set@Result=@Result + 'x'
else
if@str2='th'
set@Result=@Result + '0'
else
if substring(@str,@cnt,3) <> 'tch'
set@Result=@Result + 't'
if @str1='w'
if @str2 not in('wa','we','wi','wo','wu')
set@Result=@Result + 'w'
if @str1='y'
if @str2 not in('ya','ye','yi','yo','yu')
set@Result=@Result + 'y'
end
set @cnt=@cnt + 1
end
RETURN @Result
END
I've been reading a bit about fulltext searches, phonetic values and matchqueries and just don't know where to begin.
What I'm eventually going to do, is make procedures for matching names, finding records that are close matches and presenting them in a subform below the actual member that you look up.
E.g. if an employee looks up Sergej, he or she will also see Sergey, Sergei etc. below the membersheet.
BOL isn't very practical in examples, and its about 7 years since I took my SQLServer 7.0 MS courses, plus I've primarily worked as an administrator up until last fall, not a developer. So where to begin?
Can someone please explain how to do a query using the soundex function? If I have the last name of Smith and I want to get similar matches with soundex, what would be the syntax? SELECT * FROM NAME WHERE LNAME = 'SMITH' Thanks, Kellie
Has anyone used SOUNDEX() practically? Everytime i look through bol searching for a function i end up playing with soundex and diffference but have never been able (or wanted to take the time) to understand the values they generate..
We've installed this on our server: http://microsoft.apress.com/feature/72/sqlserversoundexing
(all the udf attempts were way too slow; this uda is nice and quick)
But we have not been able to reproduce the supposed improved functionality. No matter what misspelled name we send it, soundex and soundex_nara return the same amount of clients (in a db of over half a million). The return codes are sometimes different for the two functions but it doesnt ever result in an actual difference in query results. Has anyone actually experienced a difference in returned results?
EDIT: Have a look at this: http://en.wikipedia.org/wiki/Levenshtein_distance
I have two tables in Access database. One table has 166,000 rows and another 75,000 rows.
Both tables consist of the ids, names and addresses.
I need to run a program, which was written in VBA, to find similar names and cities in these two tables, and then I need to populate the third table with id only from both tables. I forgot to mention I use Soundex function in Access. I try to run this program for 48 hours, and it still running. Do you think this is a good idea to use Access for this task ? Or could you please help me to find another solution?
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
Hello, Does the SOUNDEX function only apply to english (surnames) or can it be used for other languages e.g. Dutch. Well, it can be used ofc. but does it produce meaningful output ?
Hi AllWe are using soundex (and later tried Nysiis) for fuzzy name searchsoftware. But we faced a lot of problems the search accuracy was not verygood also we saw a lot of misses of relevant names.There are many problems other than precision and accuracy, with soundex andNYSIIS.e.g.Look for Smith and it will comeup with around 250 very popular last names.That dones not help much when a user is searching for "John Smith". Also, itdoes not return Creighton for Kryton as the search string.I googled a little and saw soming called NamiX. Without contacting thecompany ( arizcon.com ) directly, I wanted to get feedback from newsgroupsor people who are experts at this. Has anyone used this software? If so, Isit as good as they claim?Thanks a million in advance.Steve Creighton(please remove .antispam from email address) or post back your answers tothis group
We have an application we are developing that will run worldwide. When we are searching data for peoples names I would like to use Soundex but I am not sure it works effectivly for any language other than English. Does anyone know if it is effective in Dutch, German, French etc?
I need to compare movie names from two systems. In both systems these names are entered manually by operators. I would like to compare them and give a rating on how close these names are equal.
Stripping special characters, and spaces is just not enough. It can happen that they key in sligthly different names. I've tried to use soundex but as we have over 15000 movie titles over the years i'm getting to many equal soundexes to use this as a comparison key.
Hi thereI have this query in a stored procedureSELECT * FROM Product WHERE FREETEXT (*, @SearchString )When I run the query with the @SearchString = 'hoover' it return values but if I spelt it wrong (eg: hover) I have no value returnMy question is, How can I use the SOUNDEX in a full text searchRegards
I am new to DM and I am not sure which algorithm would be best to use.
I am trying to build a custom comparitor application that companies can use to compare themselves against other companies based on certain pieces of information. I need to group a company with 11 other companies based on 6 attributes. I need the ability to apply weightings to each of the 6 attributes and have those taken into consideration when determining which 10 other companies each company is grouped with. Each group must contain 11 members, the company for the user logged in and 10 other companies that it will be compared against.
At first I thought that clustering would be a good fit for this but I can not see a way to mandate that each cluster contain exactly 11 members, I cannot see a way to weight the inputs, and I think each company can only be in one cluster at a time which do not meet my requirements.
Well, i have read in claude seidman book about data mining that some algorithm inside in microsoft decision tree are CART, CHAID and C45 algorithm. could anyone explain to me about the tree algorithm and please explain to me how the tree algorithm used together in one case?
Hello,Do you know if the algorithm for the BINARY_CHECKSUM function in documentedsomewhere?I would like to use it to avoid returning some string fields from theserver.By returning only the checksum I could lookup the string in a hashtable andI think this could make the code more efficient on slow connections.Thanks in advanced and kind regards,Orly Junior
What kind of algorithm does the MAX command uses? I have a table that I need to get the last value of the Transaction ID and increment it by 1, so I can use it as the next TransID everytime I insert a new record into the table. I use the MAX command to obtain the last TransID in the table in this process. However, someone suggested that there is a problem with this, since if there are multiple users trying to insert a record into the same table, and processing is slow, they might essentially come up with the same next TransID. He came up with the idea of having a separate table that contains only the TransID and using this table to determine the next TransID. Will this really make a difference as far as processing speed is concerned or using a MAX command on the same table to come up with the next TransID enough? Do you have a better suggestion?
I have few questions regarding Clustering algorithm.
If I process the clustering model with Ks (K is number of clusters) from 2 to n how to find a measure of variation and loss of information in each model (any kind of measure)? (Purpose would be decision which K to take.)
Which clustering method is better to use when segmenting data Kmeans or EM?
I want to predict which product can be sold together , Pl help me out which algorithm is best either association, cluster or decision and pl let me know how to use case table and nested table my table structure is
hi, i am using sqlserver2005 as back end for my project. actually we developing an stand alone web application for client, so we need to host this application in his server. he is not willing to install sql server 2005 edition in his sever so we r going by placing .mdf file in data directory of project.
but before i developed in server2005 i used aes_256 algorithm to encrypt n decrypt the pwd column by using symmetric keys.it is working fine.
but when i took the .mdf file of project n add into my project it is throwing error at creation of symmetric key that "Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows."
i'm making my master thesis about a new plugin algorithm, with the LVQ Algorithm. I make the tutorial with the pair_wise_linear_regression algorithm and i have some doubts. i was searching for the code of the algorithm in the files of the tutorial and i didn't saw it. I have my new algorithm programmed in C++ ready to attach him, but i don't know where to put him, in which file i have to put him to start to define the COM interfaces? And in which file is the code of the pair_wise_linear_regression algorithm in the SRC paste of the tutorial?
Obviosly for Person1 and 200501 I expect to see on MS Time Series Viewer $3000, correct? Instead I see REVENUE(actual)  200501 VALUE =XXX, Where XXX is absolutly different number.
Also there are negative numbers in forecast area which is not correct form business point Person1 who is tough guy tryed to shoot me. What I am doing wrong. Could you please give me an idea how to extract correct historical and predict information?
Currently I want to run a vanilla multivariate regression and get some statistics back about the regression that is built. For instance, besides the coefficients, I also want the twosided pvalues on the coefficients and the R2 of the model.
I've tried playing with the Microsoft_Linear_Regression algorithm and have run into two issues. I'm doing all this programmatically using DMX queries rather than through the BI studio.
(a) I can never get the coefficients from the regression to match with results I would get from running R or Excel. The results are close but still significantly off. I suspect this is because the Linear Regression is just a subset of the Decision/Regression Trees functionality, in which case some kind of Bayesian prior is being incorporated here. Is that the issue? And if so, is there some way to turn off the Bayesian scoring and get a vanilla multivariate regression? I don't see anything in the inputs to the linear regression that would let me do this, and even running Microsoft_Decision_Trees with a few different settings, I can't get the output I'm looking for. If there's no way to turn off the Bayesian scoring, can someone explain to me what the prior being used here is and how Bayesian learning is being applied to the regression?
(b) Using the Generic Tree Viewer, I see that there are a few "statistics" values in the Node_Distribution, but I'm not sure what they're referring to. One of them looks like it might be the MSE. I could play with this some more to find out, but I'm hoping someone here can save me that work and tell me what these numbers are. Hopefully they will constitute enough information for me to rebuild the pvalues and the R2.
I have a code for Nearest neighbour algorithm, I want to build a datamining algorithm using that code..
I have the following link that includes the source code for a sample plugin algorithm written in C#.
(managed plugin framework that's available for download here: )http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AAB4BD4705AA0AB477BA72A9CB&displaylang=en#DMAPI.
But i am confused on where to insert my algorithm logic?