Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 soundex function Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, January 29, 2009 11:07 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 16, 2015 6:46 AM Points: 5, Visits: 6
 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.
Post #646207
 Posted Thursday, January 29, 2009 11:08 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #646212
 Posted Thursday, January 29, 2009 11:41 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 16, 2015 6:46 AM Points: 5, Visits: 6
 Thanks. This is exactly what I am looking for
Post #646244
 Posted Thursday, August 23, 2012 12:17 PM
 Old Hand Group: General Forum Members Last Login: Friday, December 2, 2016 11:20 AM Points: 326, Visits: 1,766
 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 => 6Collapse 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;gocreate function Soundex5( @name varchar(30))returns varchar(5)asbegindeclare @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 soundsset @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);endgo`
Post #1349289

 Permissions