In einer APEX-Anwendung ist die Suche in einem Bericht etwas völlig normales; interaktive und klassische Berichte werden von APEX typischerweise direkt mit Suchfunktion bereitgestellt. Bereits in der Vergangenheit sind in der APEX-Community Tipps zu den Themen Case- und Umlaut-Insensitive Suche sowie Ähnlichkeitssuche (unscharfe Suche) erschienen. Heute geht es um einen, für den deutschsprachigen Raum relevanten, Spezialfall der Ähnlichkeitssuche: Dieser Tipp zeigt, wie man die Kölner Phonetik in der Datenbank implementieren und in einer APEX-Anwendung nutzen kann.

Nach dem Wikipedia-Eintrag ist die Kölner Phonetik ein "phonetischer Algorithmus, der Wörtern nach ihrem Sprachklang eine Zeichenfolge zuordnet, den phonetischen Code. Ziel dieses Verfahrens ist es, gleich klingenden Wörtern denselben Code zuzuordnen, um bei Suchfunktionen eine Ähnlichkeitssuche zu implementieren. [...]".

Der Algorithmus der Kölner Phonetik ordnet den Buchstaben eines Wortes Zahlenwerte zu, und zwar abhängig davon, wo die Buchstaben im Wort stehen und welche Buchstaben jeweils vorher und nachher zu finden sind. Die Tabelle mit den Zuordnungsregeln ist ebenfalls im Wikipedia-Artikel zu finden. Diese lässt sich nun recht einfach in eine PL/SQL Funktion übersetzen - wie folgt:

create or replace function FUNC_PHONETIK (
  p_word in varchar2
) return varchar2 deterministic is
  l_word      varchar2(32767);
  l_code      varchar2(32767) := '';
  l_codechar  varchar2(2) := '';

  l_nextchar  char(1 CHAR);
  l_prevchar  char(1 CHAR);
  l_currchar  char(1 CHAR);

  l_lastpos   pls_integer;
begin
  l_word := upper(p_word);
  l_lastpos := length(l_word);
  for i in 1..l_lastpos loop
    l_currchar := substr(l_word, i, 1);
    if i < l_lastpos then
      l_nextchar := substr(l_word, i + 1, 1);
    else
      l_nextchar := '_';
    end if;
    if i > 1 then
      l_prevchar := substr(l_word, i - 1, 1);
    else
      l_prevchar := '_';
    end if;
    -- Umsetzung Tabelle startet hier
    if l_currchar in ('A','E','I','J','O','U','Y','-','_','+') then
      l_codechar := '0';
    elsif l_currchar = 'B' then
      l_codechar := '1';
    elsif l_currchar = 'P' and not l_nextchar = 'H' then
      l_codechar := '1';
    elsif l_currchar in ('D','T') then
      if not l_nextchar in ('C','S','¯','Z') then
        l_codechar := '2';
      else
        l_codechar := '8';
      end if;
    elsif l_currchar in ('F','V','W') or (l_currchar = 'P' and l_nextchar = 'H') then
      l_codechar := '3';
    elsif l_currchar in ('G','K','Q') then
      l_codechar := '4';
    elsif l_currchar ='C' then
      if i = 1 then
        if l_nextchar in ('A','H', 'K','L', 'O', 'Q', 'R', 'U', 'X') then
          l_codechar := '4';
        else
          l_codechar := '8';
        end if;
      else
        if l_nextchar in ('A','H','K','O','Q','U','X') and not l_prevchar in ('ß','S','Z') then
          l_codechar := '4';
        else
          l_codechar := '8';
        end if;
      end if;
    elsif l_currchar = 'X' then
      if l_prevchar in ('C','K','Q') then
        l_codechar := '8';
      else
        l_codechar := '48';
      end if;
    elsif l_currchar = 'L' then
      l_codechar := '5';
    elsif l_currchar in ('M','N') then
      l_codechar := '6';
    elsif l_currchar = 'R' then
      l_codechar := '7';
    elsif l_currchar in ('S','Z','ß') then
      l_codechar := '8';
    end if;
    if l_code is null then
      l_code := l_code || l_codechar;
    else
      if not l_codechar = 0 and not substr(l_code, length(l_code), 1) = l_codechar then
        l_code := l_code || l_codechar;
      end if;
    end if;
  end loop;
  return l_code;
end func_phonetik;

Auf Github ist eine weitere Implementierung von Andy Theiler zu finden. Im Gegensatz zu der hier vorliegenden setzt diese anstelle von INSTR und SUBSTR auf die REGEXP-Funktionen der Datenbank. Inhaltlich arbeiten beide gleich; die hier vorliegende dürfte, da sie auf die REGEXP-Funktionen verzichtet, jedoch etwas schneller in der Ausführung sein.

Die Funktion FUNC_PHONETIK kann nun getestet werden ...

SQL> select func_phonetik('Czarski') from dual;

FUNC_PHONETIK('CZARSKI')
--------------------------------------------------------------
8784

SQL> select func_phonetik('tsarsky') from dual;

FUNC_PHONETIK('TSARSKY')
----------------------------------------------------
8784

Der Algorithmus liefert, wie gewünscht, für (in der deutschen Sprache) gleich klingende Worte, gleiche Zahlenwerte zurück. Hat man nun eine Tabelle mit Kundennamen, so lässt sich die Ähnlichkeitssuche darauf wie folgt umsetzen:

SQL> select * from kunden
  2  where func_phonetik(name) = func_phonetik('Maier');

NAME
---------------------------------------------------------------
Meier
Maier
Mayer
Meyer

So kann eine Ähnlichkeitssuche funktional bereits umgesetzt werden. Bei großen Datenmengen braucht es aber noch eine Indexunterstützung, denn ein jedesmal stattfindender Full Table Scan würde die Anwendung sichtlich verlangsamen. Wie schon im Tipp zum Thema Case- und Umlaut-Insensitive Suche werden wir also als nächstes einen Funktionsbasierten Index erstellen.

SQL> create index fidx_name_phonetik on kunden (func_phonetik(name));

Index wurde erstellt.

Der Ausführungsplan zeigt nun, dass auch die "phonetische Suche" nun indexunterstützt abläuft - auch bei großen Datenmengen wird die Suche nun performant sein.

SQL> select * from kunden
  2  where func_phonetik(name) = func_phonetik('Maier');

NAME
----------------
Meier
Maier
:

Ausführungsplan
------------------------------------------------------------------------------------------------
| Id | Operation                   | Name               | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                    |    1 |  2104 |     2   (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| KUNDEN             |    1 |  2104 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | FIDX_NAME_PHONETIK |    1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TESTIT"."FUNC_PHONETIK"("NAME")="FUNC_PHONETIK"('Maier'))

Mit diesen Vorbereitungen geht es nun an die Nutzung in einer APEX-Anwendung. Legen Sie also eine Anwendungsseite mit einem klassischen Bericht an. Die Seite sollte etwa wie Abbildung 1 aussehen.

Navigieren Sie dann zur Berichtsquelle und ändern Sie die SQL-Abfrage wie folgt um:

select 
 "FIRST_NAME",
 "LAST_NAME",
 "PHONE_NUMBER",
 "HIRE_DATE",
from #OWNER#."EMPLOYEES"
where (
  func_phonetik("FIRST_NAME") = func_phonetik(:P2_REPORT_SEARCH) or
  func_phonetik("LAST_NAME") = func_phonetik(:P2_REPORT_SEARCH) or
  :P2_REPORT_SEARCH is null
)

Starten Sie die Seite danach neu. Nun funktioniert die Berichtssuche als Ähnlichkeitssuche.

Leider funktioniert das automatische Hervorheben des Suchbegriffs in den Daten nur für exakte Treffer. Schön wäre es ja, wenn man auch sehen würde, welche Daten per Ähnlichkeitssuche zu einem Treffer geführt haben. Dazu muss die SQL-Abfrage nochmals angepasst werden.

select 
 "EMPLOYEE_ID",
 case 
   when lower("FIRST_NAME") = lower(:P2_REPORT_SEARCH) 
     then '<span style="color: red">' || sys.htf.escape_sc("FIRST_NAME") || '</span>'
   when func_phonetik("FIRST_NAME") = func_phonetik(:P2_REPORT_SEARCH) 
     then '<span style="color: green">' || sys.htf.escape_sc("FIRST_NAME") || '</span>'
   else sys.htf.escape_sc("FIRST_NAME")
 end as FIRST_NAME,
 case 
   when lower("LAST_NAME") = lower(:P2_REPORT_SEARCH) 
     then '<span style="color: red">' || sys.htf.escape_sc("LAST_NAME") || '</span>'
   when func_phonetik("LAST_NAME") = func_phonetik(:P2_REPORT_SEARCH) 
     then '<span style="color: green">' || sys.htf.escape_sc("LAST_NAME") || '</span>'
   else sys.htf.escape_sc("LAST_NAME")
  end as LAST_NAME,
 "PHONE_NUMBER",
 "HIRE_DATE"
from #OWNER#."EMPLOYEES"
where 
  func_phonetik("FIRST_NAME") = func_phonetik(:P2_REPORT_SEARCH) or
  func_phonetik("LAST_NAME") = func_phonetik(:P2_REPORT_SEARCH) or
  :P2_REPORT_SEARCH is null

Stellen Sie dann die beiden Berichtsspalten FIRST_NAME und LAST_NAME in den Spaltenattributen auf Standardberichtsspalte um, damit die HTML-Tags vom Browser auch interpretiert werden. Um damit keine Sicherheitslöcher für Cross-Site-Scripting Attacken zu öffnen, werden die Inhalte der beiden Spalten in der SQL-Abfrage mit SYS.HTF.ESCAPE_SC maskiert. Das Umstellen der Spalten auf Standardberichtspalte ist damit ungefährlich.

Danach kann der Bericht wie folgt aussehen. Rot bedeutet nun, dass ein exakter Treffer vorliegt, Grün bedeutet, dass der Treffer dem Suchbegriff nach der Kölner Phonetik ähnlich ist. Wenn Sie für die Ausgabe noch ein "Maß" für die Ähnlichkeit benötigen, so können Sie ab Oracle11g Release 2 das PL/SQL-Paket UTL_MATCH verwenden. Die darin befindlichen Funktionen EDIT_DISTANCE bzw. JARO_WINKLER geben Ihnen eine Kennzahl für die Ähnlichkeit zurück, nach welcher dann absteigend sortiert werden kann ...

Dass man anstelle der hard kodierten HTML-Tags im SQL natürlich auch mit CSS-Klassen oder Templates arbeiten kann, versteht sich von selbst. Beachten Sie jedoch, dass die Kölner Phonetik tatsächlich nur im deutschsprachigen Raum sinnvoll ist. In anderen Sprachen sind die Treffer, die man erhalten wird, nur wenig sinnvoll. Eine sinnvolle Erweiterung dürfte auch sein, dass der Endanwender die Ähnlichkeitssuche per Checkbox aktiviert - möchte jemand explizit "scharf" suchen, ist das dann immer noch möglich.

Zurück zur Community-Seite