translit_oracle_plsql.sql

create or replace FUNCTION ostsci2.translit(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ret VARCHAR2(32000) := p_str;
    l_upper BOOLEAN := FALSE;
  BEGIN
    IF upper(p_str)=p_str THEN 
      l_upper := TRUE;
    END IF; 
    ret := translate(ret, 'ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÛÚÜÝ', 'ABVGDEZIYKLMNOPRSTUFHY''''E');
    ret := REPLACE(ret, 'Æ', 'Zh');
    ret := REPLACE(ret, '¨', 'Yo');
    ret := REPLACE(ret, 'Ö', 'Ts');
    ret := REPLACE(ret, '×', 'Ch');
    ret := REPLACE(ret, 'Ø', 'Sh');
    ret := REPLACE(ret, 'Ù', 'Sch');
    ret := REPLACE(ret, 'Þ', 'Yu');
    ret := REPLACE(ret, 'ß', 'Ya');
    IF l_upper THEN
       ret := upper(ret);
    END IF;
    -- lowercase 
    RET := TRANSLATE(RET, 'àáâãäåçèéêëìíîïðñòóôõûúüý', 'abvgdeziyklmnoprstufhy''''e');
    RET := REPLACE(RET, 'æ', 'zh');
    RET := REPLACE(RET, '¸', 'yo');
    RET := REPLACE(RET, 'ö', 'ts');
    RET := REPLACE(RET, '÷', 'ch');
    RET := REPLACE(RET, 'ø', 'sh');
    RET := REPLACE(RET, 'ù', 'sch');
    RET := REPLACE(RET, 'þ', 'yu');
    RET := REPLACE(RET, 'ÿ', 'ya');
    RETURN ret;
  END;
/