http://postgres.cz/index.php?title=Standardisace_telefonn%C3%ADho_%C4%8D%C3%ADsla&feed=atom&action=history
Standardisace telefonního čísla - Historie editací
2024-03-29T01:48:57Z
Historie editací této stránky
MediaWiki 1.36.0
http://postgres.cz/index.php?title=Standardisace_telefonn%C3%ADho_%C4%8D%C3%ADsla&diff=458&oldid=prev
imported>Pavel: 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...
2009-06-18T12:21:43Z
<p>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...</p>
<p><b>Nová stránka</b></p><div><pre><br />
CREATE OR REPLACE FUNCTION "_global_"."getphonenumber" (pphonenum varchar) RETURNS varchar AS<br />
$body$<br />
declare ltmp varchar;<br />
BEGIN<br />
ltmp := lower(pphonenum);<br />
<br />
-- otestovani, zda-li neni telefonnich cisel vic, oddelenych carkou<br />
If position(',' in ltmp) > 0 then<br />
-- pokud ano, vezmeme jen prvni<br />
ltmp := substr(ltmp,1,position(',' in ltmp)-1);<br />
END if;<br />
<br />
-- zrusime mezery<br />
ltmp := replace(ltmp, ' ', '');<br />
--vyfiltrujeme pouze cifry a +<br />
ltmp := regexp_replace(ltmp, E'([^[:digit:]\+])', '','gi');<br />
--pokud telefon zacina na 00420 prepiseme na +420<br />
ltmp := regexp_replace(ltmp, '^00420', '+420');<br />
--totez pro slovensko<br />
ltmp := regexp_replace(ltmp, '^00421', '+421');<br />
--pokud zacina jen 420 pridame +<br />
ltmp := regexp_replace(ltmp, '^420', '+420');<br />
--totez pro slovensko<br />
ltmp := regexp_replace(ltmp, '^421', '+421');<br />
--pokud nejaky slovak pridal rpedvolbu mesta za predvolbu zeme, odstranime prebyvajici 0<br />
ltmp := regexp_replace(ltmp, E'^\\+4210', '+421');<br />
<br />
--pokud zacina cislo 00 predelame na +<br />
ltmp := regexp_replace(ltmp, '^00', '+');<br />
--pokud nyni zacina na 0 odstranime ji (pozustatek predvoleb s 0 z minulosti<br />
ltmp := regexp_replace(ltmp, '^0', '');<br />
<br />
-- pokud nyni cislo nezacina + a ma jen 9 znaku, pridame +420 pro cechy<br />
if (substr(ltmp, 1, 1) != '+') and (length(ltmp) = 9) then<br />
ltmp := '+420' || ltmp;<br />
end if;<br />
<br />
-- pokud cislo zacina na +420 a nema 13 znaku, nepovedlo se standarizovat a vysledek smazeme<br />
if (substr(ltmp, 1, 4) = '+420') and (length(ltmp) != 13) then<br />
ltmp := '';<br />
end if;<br />
<br />
--pokud je delka mensi nez 9 nebo naopak vetsi nez 15, radeji vysledek smazeme<br />
if (length(ltmp) < 9) or (length(ltmp) > 15) then<br />
ltmp := '';<br />
end if;<br />
return ltmp; <br />
END;<br />
$body$<br />
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER;<br />
</pre><br />
<pre><br />
CREATE OR REPLACE FUNCTION "_global_"."is_czechmobilephone" (pphonenum varchar) RETURNS boolean AS<br />
$body$<br />
declare polepredvoleb varchar[];<br />
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';<br />
res boolean;<br />
BEGIN<br />
select <br />
into res<br />
count(pv) > 0<br />
<br />
from<br />
_global_.unnest(string_to_array(predvolby,',')) pv<br />
WHERE<br />
substr(pphonenum,1,7) = '+420'||pv;<br />
return res;<br />
END;<br />
$body$<br />
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY DEFINER;<br />
</pre><br />
<pre><br />
CREATE OR REPLACE FUNCTION "_global_"."getmobilephone" (pphonenum varchar) RETURNS varchar AS<br />
$body$<br />
BEGIN<br />
if _global_.is_czechmobilephone(pphonenum) then<br />
return pphonenum;<br />
else<br />
return '';<br />
end if;<br />
END;<br />
$body$<br />
LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY DEFINER;<br />
</pre><br />
[[Uživatel:Raul|Raul]] 23. 4. 2009, 17:00 (UTC)</div>
imported>Pavel