USE [SmartWater_MXL]
GO
/****** Object: UserDefinedFunction [dbo].[getLon] Script Date: 2022/1/11 17:27:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <祥子>
-- Create date: <2022-01-11>
-- Description: <数据导入所用经度>
-- 经纬度的算法问题,111°51′26 〃或111°51′26.4 〃格式
--标量值函数sql server 专用
-- =============================================
ALTER function [dbo].[getLon](@Longitude varchar(100))
returns decimal(10,6)
as
begin
DECLARE @retLon decimal(10,6)
DECLARE @nmb int
DECLARE @maths decimal(10,6)
DECLARE @wenben varchar(20)
DECLARE @leng int
DECLARE @str decimal(10,6);
select @nmb= CHARINDEX('.',REPLACE(REPLACE(REPLACE(@Longitude,'′','-'),'″',''),'°','-'),0)
if(@nmb=0)
select @wenben= REPLACE(REPLACE(REPLACE(REPLACE(@Longitude,'′','.'),'″',''),'°','.'),'〃',' ')
else
select @wenben= REPLACE(REPLACE(SUBSTRING(REPLACE(REPLACE(REPLACE(@Longitude,'′','-'),'″',''),'°','-'),0,@nmb),'-','.'),'〃',' ')
set @leng =len( SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(@Longitude,'′','.'),'″',''),'°','.'),'〃',' '),@nmb+1,len(@Longitude)))
set @str = cast (SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(@Longitude,'′','.'),'″',''),'°','.'),'〃',' '),@nmb+1,len(@Longitude)-1) as int)
if (@leng='1')
begin
set @str= @str*0.000028
end
else if (@leng='2')
begin
set @str = @str*0.000003
end
else
set @str=0
set @maths=
cast(
cast( PARSENAME(@wenben,3)as decimal(10,6) )+(
cast( PARSENAME(@wenben,2)as decimal(10,6) )+(
cast( PARSENAME(@wenben,1)as decimal(10,6) ))/60)/60 as decimal(10,6)) +@str
return @maths
end;
--业务逻辑直接调用即可.必须加dbo.名
select dbo.getLon(Longitude) as Longitudechange from temp11
SQL SERVER 计算经纬度函数
于 2022-01-11 17:37:23 首次发布