Standardisace telefonního čísla

CREATE OR REPLACE FUNCTION "_global_"."getphonenumber" (pphonenum varchar) RETURNS varchar AS
$body$
declare ltmp varchar;
BEGIN
   ltmp := lower(pphonenum);

-- otestovani, zda-li neni telefonnich cisel vic, oddelenych carkou
   If position(',' in ltmp) > 0 then
   -- pokud ano, vezmeme jen prvni
      ltmp := substr(ltmp,1,position(',' in ltmp)-1);
   END if;
   
   -- zrusime mezery
   ltmp := replace(ltmp, ' ', '');
   --vyfiltrujeme pouze cifry a +
   ltmp := regexp_replace(ltmp, E'([^[:digit:]\+])', '','gi');
   --pokud telefon zacina na 00420 prepiseme na +420
   ltmp := regexp_replace(ltmp, '^00420', '+420');
   --totez pro slovensko
   ltmp := regexp_replace(ltmp, '^00421', '+421');
   --pokud zacina jen 420 pridame +
   ltmp := regexp_replace(ltmp, '^420', '+420');
   --totez pro slovensko
   ltmp := regexp_replace(ltmp, '^421', '+421');
   --pokud nejaky slovak pridal rpedvolbu mesta za predvolbu zeme, odstranime prebyvajici 0
   ltmp := regexp_replace(ltmp, E'^\\+4210', '+421');

   --pokud zacina cislo 00 predelame na +
   ltmp := regexp_replace(ltmp, '^00', '+');
   --pokud nyni zacina na 0 odstranime ji (pozustatek predvoleb s 0 z minulosti
   ltmp := regexp_replace(ltmp, '^0', '');

   -- pokud nyni cislo nezacina + a ma jen 9 znaku, pridame +420 pro cechy
   if (substr(ltmp, 1, 1) != '+') and (length(ltmp) = 9) then
     ltmp := '+420' || ltmp;
   end if;
   
   -- pokud cislo zacina na +420 a nema 13 znaku, nepovedlo se standarizovat a vysledek smazeme
   if (substr(ltmp, 1, 4) = '+420') and (length(ltmp) != 13) then
     ltmp := '';
   end if;
   
   --pokud je delka mensi nez 9 nebo naopak vetsi nez 15, radeji vysledek smazeme
   if (length(ltmp) < 9) or (length(ltmp) > 15) then
     ltmp := '';
   end if;
   return ltmp; 
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER;

CREATE OR REPLACE FUNCTION "_global_"."is_czechmobilephone" (pphonenum varchar) RETURNS boolean AS
$body$
declare polepredvoleb varchar[];
        predvolby varchar = '601,602,603,604,605,606,607,608,720,721,722,723,724,725,726,727,728,729,730,7311,732,733,734,735,736,737,738,739,773,774,775,776,777';
        res boolean;
BEGIN
   select 
   into res
   count(pv) > 0
   
   from
   _global_.unnest(string_to_array(predvolby,',')) pv
   WHERE
   substr(pphonenum,1,7) = '+420'||pv;
   return res;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY DEFINER;

CREATE OR REPLACE FUNCTION "_global_"."getmobilephone" (pphonenum varchar) RETURNS varchar AS
$body$
BEGIN
   if _global_.is_czechmobilephone(pphonenum) then
     return pphonenum;
   else
     return '';
   end if;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY DEFINER;

Raul 23. 4. 2009, 17:00 (UTC)