imported>Pavel: Nová stránka:

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

2009-06-18T12:21:43Z

Nová stránka: <pre> CREATE OR REPLACE FUNCTION "_global_"."getphonenumber" (pphonenum varchar) RETURNS varchar AS $body$ declare ltmp varchar; BEGIN ltmp := lower(pphonenum); -- otestovani, zd...

Nová stránka

<pre>
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;
</pre>
<pre>
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;
</pre>
<pre>
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;
</pre>
[[Uživatel:Raul|Raul]] 23. 4. 2009, 17:00 (UTC)

Standardisace telefonního čísla - Historie editací

imported>Pavel: Nová stránka:
CREATE OR REPLACE FUNCTION "_global_"."getphonenumber" (pphonenum varchar) RETURNS varchar AS $body$ declare ltmp varchar; BEGIN ltmp := lower(pphonenum); -- otestovani, zd...