SQL SERVER 计算经纬度函数

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