转换坐标为数字型的函数

create or replace function cord2number (cord varchar2)--返回小数坐标，只做坐标长度格式验证，未做彻底格式验证

return number is nm number(15,4);

tm varchar2(20);

res varchar2(20);

begin

 tm:=regexp_replace(cord, '[NSWE]',''); --去掉经纬标识

 res:=REGEXP_replace(tm,'^\d{6,7}(\.[0-9]+)?$','OK'); --简单格式验证

 if res <>'OK' then return null;

 elsif (instr(tm,'.')=0 and length(tm)=6) or instr(tm,'.')=7 then --纬度

   nm:=3600*to_number(substr(tm,1,2))+60*to_number(substr(tm,3,2))+ to_number(substr(tm,5));

 elsif (instr(tm,'.')=0 and length(tm)=7) or instr(tm,'.')=8 then --经度

   nm:=3600*to_number(substr(tm,1,3))+60*to_number(substr(tm,4,2))+ to_number(substr(tm,6));

 else

   nm:=null;

 end if;   

return nm; 

end;

转载于:https://www.cnblogs.com/mol1995/p/7833319.html