--先选择所有正常格式经纬度
select obstacle_id, obstacle_desc_id, no, name, type, bear, distance, elevation, has_light,
nvl2(latitude,‘N‘||replace(latitude,‘N‘),null) ,
nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),
control_obstacle
from siniswift.OBSTACLE_TABLE@trans_cad
where regexp_like(nvl2(latitude,‘N‘||replace(latitude,‘N‘),null),‘^[NS]((([0-8]\d)([0-5]\d){2}(\.\d+)?)|(900000(\.0+)?))$‘) and
regexp_like(nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),‘^[EW]((((1[0-7]\d)|(0\d{2}))([0-5]\d){2}(\.\d+)?)|(1800000(\.0+)?))$‘)
union --补充选择经度只有两位的,并在经度前补0
select obstacle_id, obstacle_desc_id, no, name, type, bear, distance, elevation, has_light,
nvl2(latitude,‘N‘||replace(latitude,‘N‘),null) ,
nvl2(longitude,‘E0‘||replace(longitude,‘E‘),null),
control_obstacle
from siniswift.OBSTACLE_TABLE@trans_cad
where regexp_like(nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),‘^[EW]\d{2}([0-5]\d){2}(\.\d+)?$‘)
union --再补充没有经纬度或者经纬度格式不正确的,将其经纬度设为null
select obstacle_id, obstacle_desc_id, no, name, type, bear, distance, elevation, has_light,null,null,control_obstacle
from siniswift.OBSTACLE_TABLE@trans_cad
where not regexp_like(nvl2(latitude,‘N‘||replace(latitude,‘N‘),null),‘^[NS]((([0-8]\d)([0-5]\d){2}(\.\d+)?)|(900000(\.0+)?))$‘) or
(
not regexp_like(nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),‘^[EW]((((1[0-7]\d)|(0\d{2}))([0-5]\d){2}(\.\d+)?)|(1800000(\.0+)?))$‘) and
not regexp_like(nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),‘^[EW]\d{2}([0-5]\d){2}(\.\d+)?$‘)
)
原文:http://www.cnblogs.com/mol1995/p/6292550.html