January 29, 2009 at 11:07 am
What exactly and on what basis does the soundex function returns the integer values.
I read through msdn but it only tells that it doesn't considers vowels in the strings.
But what I exactly want to know is what logic is used during comparison of two strings behind th scene.
January 29, 2009 at 11:08 am
There's an entry in Wikipedia about soundex. It has what you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 11:41 am
Thanks. This is exactly what I am looking for
August 23, 2012 at 12:17 pm
Here is my first attempt to make soundex more selective:
Algorithm sqlserver uses:
Replace consonants with digits as follows (but do not change the first letter):
b, f, p, v => 1
c, g, j, k, q, s, x, z => 2
d, t => 3
l => 4
m, n => 5
r => 6
Collapse adjacent identical digits into a single digit of that value.
Remove all non-digits after the first letter.
Return the starting letter and the first three remaining digits.
If needed, append zeroes to make it a letter and three digits.
Lets extend this to 5 in length to make it more selective.
On lastname data I see a selectivity increase from 1 in 3000 to 1 in 10000.
Function contains no error checking and is not optimized.
drop function dbo.soundex5;
go
create function Soundex5( @name varchar(30))
returns varchar(5)
as
begin
declare
@out varchar(20),
@i int,
@vc varchar(1);
set @name = @name + '00000';
set @out = left(@name,1);
set @i = 2;
while @i <= len(@name)
begin
set @vc = substring(@name, @i, 1)
set @out = @out +
case
when charindex(@vc, 'bfpv') > 0 then '1'
when charindex(@vc, 'cgjkqsxz') > 0 then '2'
when charindex(@vc, 'dt') > 0 then '3'
when charindex(@vc, 'l') > 0 then '4'
when charindex(@vc, 'mn') > 0 then '5'
when charindex(@vc, 'r') > 0 then '6'
when charindex(@vc, '0') > 0 then '0'
else ''
end;
set @i = @i + 1;
end
-- ignore similar consecutive sounds
set @out = REPLACE(@out,'11','1');
set @out = REPLACE(@out,'22','2');
set @out = REPLACE(@out,'33','3');
set @out = REPLACE(@out,'44','4');
set @out = REPLACE(@out,'55','5');
set @out = REPLACE(@out,'66','6');
return left(@out,5);
end
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply