Corrected Metaphone Implementation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Corrected Metaphone Implementation

Author

Topic

zing_ming
Starting Member

2 Posts

Posted - 2009-05-14 : 14:44:35

-- Corrected Metaphone Implementation

Create FUNCTION [dbo].[Metaphone](@str as varchar(100))
RETURNS varchar (25)
/*
Metaphone Algorithm

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),
@str3 char(3),
@str2 char(2),
@str1 char(1),
@strp char(1),
@strLen tinyint,
@cnt tinyint

set @str = Ltrim(rtrim(@str))
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
set @cnt=2
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 @str1 in ('a','e','i','o','u')
begin
--set @str = right(@str , @strLen - 1)
--set @strLen = @strLen - 1
set @Result=@str1
set @cnt=2
end

while @cnt <= @strLen
begin
set @str1 = substring(@str,@cnt,1)

if(@str1 = ' ')
begin
set @Result=@Result + ' '+ dbo.Metaphone(substring(@str,@cnt,@strLen))
Goto endFunction
end

if @cnt <> 1
set @strp=substring(@str,(@cnt-1),1)
else set @strp=' '

if @strp<> @str1
begin

set @str2 = substring(@str,@cnt,2)

if @str1 in('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
Begin
if substring(@str,(@cnt - 1),1) <> 'm'
set @Result=@Result + 'b'
end
else
set @Result=@Result + 'b'

if @str1='c'
if @str2 = 'ch' or substring(@str,@cnt,3) = 'cia'
set @Result=@Result + 'x'
else
if @str2 in('ci','ce','cy') and @strp<>'s'
set @Result=@Result + 's'
else set @Result=@Result + 'k'

if @str1='d'
if substring(@str,@cnt,3) in ('dge','dgy','dgi')
set @Result=@Result + 'j'
else set @Result=@Result + 't'

if @str1='g'
if not(@str2='gh' and substring(@str,@cnt+2,1)not in ('',' ','a','e','i','o','u') )
if(@str2 <> 'gn' and substring(@str,@cnt,4)<>'gned')
if substring(@str,(@cnt - 1),3) not in ('dge','dgy','dgi')
if (@str2 in ('gi', 'ge','gy') and @strp<>'g')
set @Result=@Result + 'j'
else
set @Result=@Result + 'k'

if @str1='h'
if not((@strp 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'
else set @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 in('wa','we','wi','wo','wu')
set @Result=@Result + 'w'

if @str1='y'
if @str2 in('ya','ye','yi','yo','yu')
set @Result=@Result + 'y'
end
set @cnt=@cnt + 1
end
endFunction:
RETURN @Result
END

edit: moved to script library

pootle_flump

1064 Posts

Posted - 2009-05-15 : 07:41:08

Mods - is this a script library thread? I think there is a Metaphone thread there already....

zing_ming
Starting Member

2 Posts

Posted - 2009-05-17 : 19:32:50

Hi there
this is corrected version of metaphone t-sql implementation, I have linked this page to wikipedia page for easy access.

http://en.wikipedia.org/wiki/Metaphone

Please keep this here on top. you can link other metaphone page to this page.

CodeNaked
Starting Member

1 Post

Posted - 2010-03-03 : 15:09:03

There is an inaccurate comment under the "Transformations" section. It explains that C is transformed to K when present in "-sch-", but it is not. A computationally cheap way to correct this is to include the following lines at the beginning of the function (before @strLen is set).


SET @str = REPLACE(@str, 'sch', 'sk')
SET @str = REPLACE(@str, 'chr', 'kr')

Thanks,

Darwin

liberty1
Starting Member

1 Post

Posted - 2011-08-20 : 13:15:33

Hello - I am impressed with this procedure - many years ago (30) I had to write a soundex algorithm in COBOL - this is much much better. But alas I am no longer a programmer so I find this SQL quite hard. Can you tell me house I use this 'function' in conjunction with my database please ie how do I give the function the actual data field to work on?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources