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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Corrected Metaphone Implementation
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zing_ming
Starting Member

2 Posts

Posted - 05/14/2009 :  14:44:35  Show Profile  Reply with Quote
-- 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
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 05/15/2009 :  07:41:08  Show Profile  Reply with Quote
Mods - is this a script library thread? I think there is a Metaphone thread there already....
Go to Top of Page

zing_ming
Starting Member

2 Posts

Posted - 05/17/2009 :  19:32:50  Show Profile  Reply with Quote
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.

Go to Top of Page

CodeNaked
Starting Member

USA
1 Posts

Posted - 03/03/2010 :  15:09:03  Show Profile  Reply with Quote
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
Go to Top of Page

liberty1
Starting Member

United Kingdom
1 Posts

Posted - 08/20/2011 :  13:15:33  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.18 seconds. Powered By: Snitz Forums 2000