# Convert Degrees Minutes Seconds to Decimal UDF

,

I wrote this function to work with my sp_EarthDistance stored procedure.
It converts the regular degrees/minutes/seconds representation of angles to a decimal number.

Remember to use '' instead of ' within a string to be treated a character and not an end quote.

Sydney location    151¦12'0 "E               33¦52'0 "S
and in decimal       151.2             -33.87

SELECT [master].[dbo].[udfDegMinSecToDecimal]('151¦12''0 "E'), [master].[dbo].[udfDegMinSecToDecimal]('33¦52''0 "S')

```SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfDegMinSecToDecimal]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfDegMinSecToDecimal]
GO

CREATE FUNCTION udfDegMinSecToDecimal  (@str varchar(255))
RETURNS Decimal(18,6)
AS
BEGIN
Declare @udfDegMinSecToDecimal Decimal(18,6)
,  @Degrees Decimal(18,6)
,  @convMinutes Decimal(18,6)
,  @ConvSeconds Decimal(18,6)
,  @vcDegrees varchar(10)
,  @vcMinutes varchar(10)
,  @vcSeconds varchar(10)
,  @dPos int
,  @mPos int
,  @sPos int
,  @Sgn varchar(1)
,  @cmd varchar(255)
Select @Str = [SWITCH].[dbo].[udfStripSpaces](@str)

SELECT @dPos = CharIndex(CHAR(166), @Str)
,@mPos = CharIndex(CHAR(39), @Str)
,@sPos = CharIndex(CHAR(34), @Str)
,@Sgn  = RIGHT(@Str, 1)
If @dPos = 0
Begin
Select @dPos = CharIndex(CHAR(176), @Str)
End
If @dPos = 0
Begin
Select @dPos = CharIndex(CHAR(186), @Str)
End
Select @Sign =
CASE
WHEN @Sgn = 'S' THEN -1
WHEN @Sgn = 'W' THEN -1
ELSE 1
END

SELECT  @vcDegrees = SUBSTRING(@Str, 1, @dPos -1)
,  @vcMinutes = SUBSTRING(@Str, @dPos + 1, @mPos - @dPos -1)
,  @vcSeconds = SUBSTRING(@Str, @mPos + 1, @sPos - @mPos -1)

Select @Degrees =
CASE
WHEN Len(@vcDegrees) = 0 THEN 0  -- Takes care of no value
ELSE Cast(@vcDegrees as Decimal(18,6))
END
Select @convMinutes =
CASE
WHEN Len(@vcMinutes) = 0 THEN 0  -- Takes care of no value
ELSE Cast(@vcMinutes as Decimal(18,6))
END
Select @ConvSeconds =
CASE
WHEN Len(@vcSeconds) = 0 THEN 0  -- Takes care of no value
ELSE Cast(@vcSeconds as Decimal(18,6))
END

SELECT @udfDegMinSecToDecimal = @Sign * (@Degrees + (@convMinutes/60.0) + (@ConvSeconds/3600.0))

RETURN(@udfDegMinSecToDecimal)
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[udfDegMinSecToDecimal]  TO [public]
GO```

## Rate

4 (1)

You rated this post out of 5. Change rating

## Rate

4 (1)

You rated this post out of 5. Change rating