Please start any new threads on our new site at 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!
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  

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

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

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

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')
-- set @str = right(@str , @strLen - 1)
-- set @strLen = @strLen - 1
set @cnt=2

if @str2 = 'wh'
set @str = 'w' + right(@str , @strLen - 2)
set @strLen = @strLen - 1

set @str1 = left(@str,1)
if @str1= 'x'
set @str = 's' + right(@str , @strLen - 1)

if @str1 in ('a','e','i','o','u')
--set @str = right(@str , @strLen - 1)
--set @strLen = @strLen - 1
set @Result=@str1
set @cnt=2

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

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

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

if @strp<> @str1

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

if @str1='c'
if @str2 = 'ch' or substring(@str,@cnt,3) = 'cia'
set @Result=@Result + 'x'
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'
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'
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'
if @str2='th'
set @Result=@Result + '0'
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'
set @cnt=@cnt + 1
RETURN @Result

edit: moved to script library

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

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.

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

Go to Top of Page

Starting Member

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')


Go to Top of Page

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.07 seconds. Powered By: Snitz Forums 2000