/fdata%2F2470622%2Favatar-blog-1074652166-tmpphpjYbJuw.jpg){kind=avatar}
SoundEx für den deutschsprachigen Raum
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
/idata%2F2470622%2FDESCRIBE_SC1.jpg)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
