I’m querying a Microsoft SQL database with facilities locations from around the country. Problem is many facilities managers do not know north from south or east from west and have entered the location of their facilities in a wide variety of formats.

SELECT 
[LOCATION]
      ,[BEGIN_LATITUDE]
      ,[BEGIN_LONGITUDE]
       ,CASE WHEN LEFT([BEGIN_LATITUDE],1)='+'  THEN RIGHT([BEGIN_LATITUDE], LEN([BEGIN_LATITUDE])-1) ELSE [BEGIN_LATITUDE]  END AS LAT 
      ,CASE 
      --WHEN LEFT([BEGIN_LONGITUDE],2)='-0' THEN RIGHT([BEGIN_LONGITUDE], LEN([BEGIN_LONGITUDE])-2) 
      --WHEN LEFT([BEGIN_LONGITUDE],1)='0' THEN RIGHT([BEGIN_LONGITUDE], LEN([BEGIN_LONGITUDE])-1)
      WHEN [BEGIN_LONGITUDE] LIKE '%0%' THEN 
      SUBSTRING([BEGIN_LONGITUDE], PATINDEX('%[0-9]%',[BEGIN_LONGITUDE]),
      LEN([BEGIN_LONGITUDE])-(PATINDEX('%[0-9]%',[BEGIN_LONGITUDE])-2)
      -(PATINDEX('%[0-9]%',REVERSE([BEGIN_LONGITUDE]))-2))
      ELSE [BEGIN_LONGITUDE]
      END AS LON
    FROM [FMSS_SDE].[dbo].[FMSS_LOAD_TABLE]
    order by LAT

Gets some of what I’m trying to fix, but not all. I’m struggling with the following:

1. If [BEGIN_LONGITUDE] contains +144.652149 remove the “+” and replace with “-“
2. If [BEGIN_LONGITUDE] contains 064.761570 remove the leading “0” and add replace with “-“
3. If [BEGIN_LONGITUDE] contains -074.933330 remove the second “0”