Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

soundex function Expand / Collapse
Author
Message
Posted Thursday, January 29, 2009 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 11:15 AM
Points: 5, Visits: 4
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #646212
Posted Thursday, January 29, 2009 11:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 11:15 AM
Points: 5, Visits: 4
Thanks. This is exactly what I am looking for








Post #646244
Posted Thursday, August 23, 2012 12:17 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:32 AM
Points: 152, Visits: 961
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


Post #1349289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse