Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
CREATE OR REPLACE PACKAGE PKG_PHONETIK AS 
/*
|| $Header: $
||
|| Name   : PCK_PHONETIK
|| Aufgabe: Funktionen für die Umsetzung der Kölner Phonetik fuer
CRS BI
|| Autor  : 13.02.2017, Jan Schreiber
||
*/
  
  FUNCTION GET_KP_STRING (
      I_STRING                IN VARCHAR2)
    RETURN VARCHAR2;
END PKG_PHONETIK;
/

CREATE OR REPLACE PACKAGE BODY PKG_PHONETIK AS
/*------------------------------------------------------------------------------
||
|| 2017.02.13 Jan Schreiber: An implementation of "Koelner Phonetik".
||
*/------------------------------------------------------------------------------
/*
  This work is based on Andy Theilers github package: https://github.com/deezaster/germanphonetic
  and Carsten Czarskis APEX implementation: https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/1502/index.html
    
  Source code: https://wiki.loopback.org/confluence/x/HoDR
*/
  FUNCTION GET_KP_STRING (
      i_string                IN VARCHAR2)
    RETURN VARCHAR2 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(i_string); 
    l_lastpos := length(l_word);
    for i in 1..l_lastpos loop
      -- loop through input string
      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;
      -- translations are implemented according to: http://de.wikipedia.org/wiki/K%C3%B6lner_Phonetik
      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
        -- a code is not to appear multiple times concatenated
        -- "0" can only occur at the beginning
          l_code := l_code || l_codechar;
        end if;
      end if;
    end loop;
    RETURN l_code;
  END GET_KP_STRING;
END PKG_PHONETIK;
/

...