Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

SoundEx für den deutschsprachigen Raum

13. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Man kennt das Problem: Man sucht in einem Adressstamm nach einer Person und findet ihn auf die Schnelle nicht, weil man nicht genau weiß, ob sich „Meier“ nun Maier, Meyer, Mayer oder Mayr schreibt.

Für den englischsprachigen Raum gibt es im MS Sql Server die integrierte Funktion SOUNDEX, die eine Ähnlichkeitssuche (Unscharfe Suche) ermöglicht.

Beispiel:

 SELECT SOUNDEX('Tailer') AS S1,  
  SOUNDEX('Tayler') AS S2,  
  SOUNDEX('Tyler') AS S3 

Die Selektion liefert 3 identische Ergebnisse

 S1 S2 S3 
 ----- ----- ----- 
 T460 T460 T460 

also egal nach welchem Namen ich suche, ich finde immer alle drei ähnlich geschriebenen Namen.

Die Funktion hat aber auch seine kleinen Nachteile

-       Sie ist sehr auch englisch-sprachige Namen abgestimmt

-       Das Ergebnis ist mit 4 Zeichen ein doch sehr „zusammen geraffte“ Teilmenge des Namens

 

Für meine Ansprüche und eben deutschsprachige Namen habe ich mir selbst mal eine SoundExDE Funktion erstellt, zudem wird das Ergebnis nicht auf 4 Zeichen begrenzt. Dadurch ist die Ergebnismenge von Selektionen zwar mit unter geringer, aber dafür auch präziser; werden will schon hunderte von nur entfernt ähnliche Namen durchgehen.

So sieht sie aus:

 -- Alte Version löschen 
 IF NOT OBJECT_ID('dbo.fnSoundExDE', 'FN') IS NULL 
  DROP FUNCTION [dbo].[fnSoundExDE] 
 GO 
   
 -- Erzeugt aus dem übergebenen Text einen phonetischen SoundEx Text für 
 -- einen Ähnlichkeitsvergleich (unscharfer Vergleich)  
 -- Optimiert für deutschsprachige Texte 
 -- Parameter:  
 -- @sValue = der umzusetztende Text 
 -- @iIgnoreNumbers = 1 - Zahlenwerte werden ignoriert, 0 = Zahlen werden beachtet 
   
 CREATE FUNCTION [dbo].[fnSoundExDE](@sValue AS nvarchar(4000),  
  @iIgnoreNumbers AS tinyint) 
 RETURNS varchar(4000) 
 AS 
 BEGIN 
  DECLARE @sSoundEx AS nvarchar(4000) 
  DECLARE @sResult as varchar(4000) 
  DECLARE @iLoop AS smallint 
  DECLARE @iHit AS smallint 
   
  --Nur Großbuchstaben ohne Leerzeichen 
  SET @sSoundEx = UPPER(LTRIM(RTRIM(@sValue))) 
  SET @sSoundEx = REPLACE(@sSoundEx, ' ', '') 
   
  IF LEN(@sSoundEx) > 0 
  BEGIN 
  -- Umlaute substituieren 
  SET @sSoundEx = REPLACE(@sSoundEx, N'Ä', 'E') -- Wie ein E 
  SET @sSoundEx = REPLACE(@sSoundEx, N'Ö', 'OE') 
  SET @sSoundEx = REPLACE(@sSoundEx, N'Ü', 'UE') -- Wie Y 
  SET @sSoundEx = REPLACE(@sSoundEx, N'ß', 'S') 
   
  -- Von Unicode in ASCII casten 
  SET @sResult = CONVERT(varchar(4000), @sSoundEx) 
   
  -- Nun die erste Stufe der Ersetzung von Buchstabenkombinationen 
  SET @sSoundEx = REPLACE(@sSoundEx, 'EI', 'AY') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'AI', 'AY') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'EY', 'AY') 
   
  SET @sSoundEx = REPLACE(@sSoundEx, 'CZ', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'DS', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'SC', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'SZ', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'TZ', 'C') 
   
  SET @sSoundEx = REPLACE(@sSoundEx, 'PH', 'V') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'PF', 'V') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'QU', 'KV') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'UE', 'Y') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'EU', 'OY') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'AE', 'E') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'KS', 'X') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'IE', 'Y') 
   
  SET @sSoundEx = REPLACE(@sSoundEx, 'AH', 'A') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'EH', 'E') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'IH', 'I') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'UH', 'E') 
   
  -- Jetzt die zweite Stufe 
  SET @sSoundEx = REPLACE(@sSoundEx, 'K', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'G', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'Q', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'I', 'Y') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'W', 'V') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'F', 'V') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'T', 'D') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'P', 'B') 
   
  --Zum Schluß alle doppelten und Nicht-Buchstaben entfernen 
  SET @iLoop = 1 
  WHILE @iLoop <= LEN(@sResult) 
  BEGIN 
  SET @iHit = 1 
  IF (@iIgnoreNumbers <> 0 AND NOT SUBSTRING(@sResult, @iLoop, 1) LIKE '[A-Z]') 
  OR (@iIgnoreNumbers = 0 AND NOT (SUBSTRING(@sResult, @iLoop, 1) LIKE '[A-Z]' OR SUBSTRING(@sResult, @iLoop, 1) LIKE '[0-9]')) 
  BEGIN 
  SET @sResult = SUBSTRING(@sResult, 1, @iLoop - 1) + SUBSTRING(@sResult, @iLoop + 1, LEN(@sResult) - @iLoop) 
  SET @iHit = 0 
  END 
   
  IF @iLoop <= LEN(@sResult) AND @iLoop > 1 
  IF SUBSTRING(@sResult, @iLoop, 1) = SUBSTRING(@sResult, @iLoop - 1, 1) 
  BEGIN 
  SET @sResult = SUBSTRING(@sResult, 1, @iLoop - 1) + SUBSTRING(@sResult, @iLoop + 1, LEN(@sResult) - @iLoop) 
  SET @iHit = 0 
  END 
   
  SET @iLoop = @iLoop + @iHit 
  END 
  END 
   
  RETURN @sResult 
   
 END 

 

So, nun noch ein kleiner Test, ob es so funktioniert wie gewünscht:

 -- Unittest für 'dbo.fnSoundExDE' 
 CREATE TABLE #Namen 
  (Name nvarchar(20), 
   mySoundEx nvarchar(20)); 
   
 INSERT INTO #Namen ([Name]) VALUES ('Müller') 
 INSERT INTO #Namen ([Name]) VALUES ('Mueller') 
 INSERT INTO #Namen ([Name]) VALUES ('Müler') 
 INSERT INTO #Namen ([Name]) VALUES ('Weihermüller') 
 INSERT INTO #Namen ([Name]) VALUES ('Weiden-Müller') 
 INSERT INTO #Namen ([Name]) VALUES ('Mühler') -- Der ist nur fast so ähnlich 
 INSERT INTO #Namen ([Name]) VALUES ('Muehler') -- Der ist nur fast so ähnlich 
   
 UPDATE #Namen SET mySoundEx = dbo.fnSoundExDE([Name], 1) 
   
 SELECT *, SOUNDEX([Name]) AS Sx 
 FROM #Namen 
 WHERE mySoundEx LIKE '%' + dbo.fnSoundExDE('Müller', 1) + '%' 
   
 GO 
 DROP TABLE #Namen 

Ergebnis:

 Name mySoundEx Sx 
 -------------------- -------------------- ----- 
 Müller MUELER M460 
 Mueller MUELER M460 
 Müler MUELER M460 
 Weihermüller WEIHERMUELER W654 
 Weiden-Müller WEIDENMUELER W350 

Diesen Post teilen

Repost 0

Kommentiere diesen Post

Dennis 07/18/2012 11:45

Hallo,

ein sehr hilfreicher Post!

Allerdings sollte folgender Code:

"-- Von Unicode in ASCII casten
SET @sResult = CONVERT(varchar(4000), @sSoundEx)"

weiter nach unten vor diese Schleife verschoben werden:
"--Zum Schluß alle doppelten und Nicht-Buchstaben entfernen
SET @iLoop = 1"

...ansonsten sind alle Replace-aufrufe unwirksam, die nach dem ASCII-Convert aufgerufen werden. Die Variable @sSoundEx wird nämlich nicht mehr in @sResult geschrieben.

Gruß Dennis