SQL - möglichst gleiche Einträge finden

Question

Hallo,

ich mache sehr viel mit SQL. Ich habe jetzt aber ein neues Problem wo ich nicht genau weiß was der beste Weg ist das Problem zu lösen. Ich möchte mal um Kreative mit-denk-hilfe bitten. Hier mal die Problemstellung:

Aus einer Artikeltabelle sollen immer die 10 weiteren Datensätze angezeigt werden, die der Aktuellen Bezeichnung am Ähnlichsten sind.

Beispiel:
Der Aktuelle Artikel ist "Telefonregister schwarz 15,4x12,3cm Arlac"

Mögliche Artikel sind z.B:
Telefonregister schwarz 15,4x12,3cm S Arlac
Telefonregister royalblau 15,4x12,3 S Arlac
Telefonregister schwarz 23x19cm Index Arlac
Telefonregister beige 23x19cm Index Arlac
Telefonregister grau 23x19cm Index Arlac
Telefonregister bordeaux 23x19cm Index Arlac
Telefonregister weiß 23x19cm Index Arlac
Telefonregister royalblau 23x19cm Index Arlac
Telefonregister glasklar 23x19cm Index Arlac
Telefonregister petrol 23x19cm Index Arlac

Ich kann die Abfrage nicht einfach mit einem Like machen, da ich nicht weiß welche Länge verglichen werden muss. Ich suche also nach einer Funktion, die den gesamten Text vergleicht und z.B. die Anzahl der gleichen Wörter zurück gibt oder die Länge bis wohin der Artikel der gesuchten Bezeichnung gleicht oder so was.

Es müsste also als Ergebnis eine Zahl entstehen, nach der ich das absteigend sortieren kann um die 10 besten Treffer zu ermitteln ;-)

Wer hat eine idee?

Joachim

Answer 1

Hallo Joachim,

an SoundEx hatte ich zunächst auch gedacht, aber die Funktion ist zu sehr auf engliche Namen / die englische Sprache zugeschnitten, das wird Dir bei Deinen Bezeichnungen nicht wirklich weiter helfen.

Entweder erstellst Du eine eigene SoundEx Funktion speziell für Deine Bedürfnisse oder Du solltest Dir mal die Vollstext-Suche ansehen, ob Du es damit lösen kannst.

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

SOUNDEX und DIFFERENCE helfen nicht weiter, da alle Vergleichswerte 4 = große Ähnlichkeit ausgeben.

Schau dir das mal an:
http://www.simple-talk.com/sql/t-sql-programming/quantifying-text-differences-in-tsql/

 

dbo.ufnDifferencesInText('Telefonregister schwarz 15,4x12,3cm Arlac', Bezeichnung)

Telefonregister schwarz 23x19cm Index Arlac       1
Telefonregister schwarz 15,4x12,3cm S Arlac        1
Telefonregister beige 23x19cm Index Arlac            3
Telefonregister grau 23x19cm Index Arlac             3
Telefonregister bordeaux 23x19cm Index Arlac     3
Telefonregister weiß 23x19cm Index Arlac             3
Telefonregister royalblau 23x19cm Index Arlac     3
Telefonregister glasklar 23x19cm Index Arlac       3
Telefonregister petrol 23x19cm Index Arlac           3
Telefonregister royalblau 15,4x12,3 S Arlac           4

Answer 3

Hey Olaf,

and Soundex hatte ich auch gedacht. Ich hatte es nicht ausprobiert weil ich die Probleme erwartet hatte. Mit Deiner Idee habe ich es aber mal ausprobiert. Leider versagt die Soundex Funktion komplett wenn es um zahlen geht.

select

Top 100 Bezeichnung from artikel where soundex(Bezeichnung) like SOUNDEX('Telefonregister schwarz 15,4x12,3cm S Arlac')

Telefonregister schwarz 23x19cm Index Arlac
...
Telefonringbuch A5 sort. Metallico Packung 4 Stück
Telefonarm lichtgrau Gelenkarm und Klemmfuß TSA 5020
Telefon-Reinigungstücher desinfiz. DataFlash
Telefonnotiz A5 50Blatt
Telefonarm lichtgrau Gelenkarm und Klemmfuß Talkmaster
Telefonringbuch A6 schwarz Spiralgeb PP-Folie, 72Blatt, 12tlg.
Telefon-Telefax-Notiz A5 50Blatt
Telefonnotiz A5 50Blatt

Wie beführtet ergibt Soundex kein gutes Ergebnis. Es wird viel zu viel Mist gefunden. Das Problem ist einfach die Bewertung. Ich müsste das Ergebnis so sortieren, dass nur die Besten genommen werden...

Der Idealfall wäre eine Zahl mit der Anzahl der gleichen Buchstaben/Zahlen die in der richtigen Reihenfolge vorkommen ...

Joachim

Answer 4

Hallo Joachim,

was Du da machen willst, wird mit Bordmitteln nicht gehen. Ich kenne solche Suchoptionen, die aber teilweise sehr ins Geld gehen.

Am einfachsten wäre es wohl, wenn Du dich mit Fuzzy-Suche und/oder unscharfer Dublettensuche beschäftigst. Da kommt wahrscheinlich mehr bei rum als wenn Du in SQL selbst nach so etwas suchst. Ob es dann etwas gibt, was Du als SQL heraus nutzen kannst, musst Du dann schauen.

Und ja, ich weiß, dass das nicht genau deine Fragestellung trifft. Es dürfte aber dem, was Du schlußendlich brauchst, näher kommen als Soundex, Difference, ...

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 5

Hey Praktikant,

sehr coole Funktion. Sehr interessant zu lesen. Leider funktioniert die Funkktion nicht für meine Anforderung.
Wir haben 30.000 Artikel in der Datenbank. Die abfrage dauert ewig. Durch die vielen Artikel kommt es zu sehr merkwürdigen Ergebnissen:

select Top 100 Bezeichnung, dbo.ufiDifferencesInText(Bezeichnung,'Telefonregister royalblau 15,4x12,3 S Arlac') from Artikel order by dbo.ufiDifferencesInText(Bezeichnung,'Telefonregister royalblau 15,4x12,3 S Arlac')

Telefonregister royalblau 15,4x12,3 S Arlac 0
Telefonregister royalblau 18,5x15,5 Luxadex-S Arlac 2
Heftklammer 24/10 s 1000Stück 2
Federschale silber Pen-Pool Arlac 3
Multiköcher petrol Uni-Butler Arlac 3
Köcher weinrot Date-Box Arlac 3
Stehsammler royalblau College Arlac 3
Telefonregister royalblau 23x19cm Index Arlac 3
Klebefilmabroller schwarz Arlac 3
Ersatzregister für Ardex Arlac 3

aber cooler Ansatz ;-)

Danke

Joachim

 

Answer 6

Hallo Stefan,

Gute Idee ... die hat mich zu dieser Seite gebracht:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51540

Die Function funktioniert recht gut:

CREATE FUNCTION dbo.edit_distance(@s1 nvarchar(3999), @s2 nvarchar(3999))
RETURNS int
AS
BEGIN
  DECLARE @s1_len int, @s2_len int, @i int, @j int, @s1_char nchar, @c int, @c_temp int,
    @cv0 varbinary(8000), @cv1 varbinary(8000)
  SELECT @s1_len = LEN(@s1), @s2_len = LEN(@s2), @cv1 = 0x0000, @j = 1, @i = 1, @c = 0
  WHILE @j <= @s2_len
    SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1
  WHILE @i <= @s1_len
  BEGIN
    SELECT @s1_char = SUBSTRING(@s1, @i, 1), @c = @i, @cv0 = CAST(@i AS binary(2)), @j = 1
    WHILE @j <= @s2_len
    BEGIN
      SET @c = @c + 1
      SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
        CASE WHEN @s1_char = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END
      IF @c > @c_temp SET @c = @c_temp
      SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
      IF @c > @c_temp SET @c = @c_temp
      SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
    END
    SELECT @cv1 = @cv0, @i = @i + 1
  END
  RETURN @c
END

Aufruf mit ...
select Top 100 Bezeichnung, dbo.edit_distance(Bezeichnung,'Telefonregister schwarz 15,4x12,3cm S Arlac') from artikel order by dbo.edit_distance(Bezeichnung,'Telefonregister schwarz 15,4x12,3cm S Arlac')

Das Ergebnis ist das beste was ich bishe gesehen habe ;-)

Telefonregister schwarz 15,4x12,3cm S Arlac 0
Telefonregister schwarz 24,5x20cm Regis Arlac 10
Telefonregister royalblau 15,4x12,3 S Arlac 11
Telefonregister schwarz 23x19cm Index Arlac 12
Telefonregister schwarz 22,5x26cm Maxi-Confon 16
Telefonregister schwarz 16x22cm Schuppenform 17
Telefonregister weiß 23x19cm Index Arlac 18
Telefonregister grau 23x19cm Index Arlac 18
Telefonregister grau 18,5x15,5cm Luxadex-S Arlac 18
Telefonregister weiß 18,5x15,5cm Luxadex-S Arlac 18
Telefonregister weiß 18x12cm Fonadress Arlac 19
...

Ich denke das ist die Lösung! Ich werde das jetzt mal für die 30.000 Artikel durchlaufen lassen. Mal sehen was dabei für Probleme auftauchen ;-)

Danke an alle Kreativ-Mitdenker !!!

Joachim