## soundex function

 Author Message vipulhimanshu Forum Newbie Group: General Forum Members Points: 7 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. GSquared SSC Guru Group: General Forum Members Points: 140555 Visits: 9731 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 vipulhimanshu Forum Newbie Group: General Forum Members Points: 7 Visits: 6 Thanks. This is exactly what I am looking for Bill Talada SSCertifiable Group: General Forum Members Points: 6776 Visits: 2296 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`