I answered this question from a person who had to convert coordinates stored as an integer to a float

I’m receiving data on coordinates as 115949833 and I need it to output as 115.949833 because I need to be able to calculate the mileage between the latitude and longitude coordinates. Both the longitude and latitude values are saved as integers with no decimal places

There are several things you can do to accomplish this:

SUBSTRING

STUFF

LEFT and RIGHT

Arithmetic

Let’s take a look at how each of them work

Arithmetic multiplication

This is pretty simple, just multiply the integer value by 0.000001

``````DECLARE @pos int = 115949833

SELECT CONVERT(decimal(10,6),@pos * 0.000001) --115.949833
``````

Arithmetic division

This is pretty simple as well, instead of multiplying the integer value by 0.000001, we are going to divide the value by 1000000.0

``````DECLARE @pos int = 115949833

SELECT CONVERT(decimal(10,6),@pos /1000000.0) --115.949833
``````

STUFF

The STUFF function is not a widely used function in the SQL Server world. Basically you can use it to inject a character (or series of characters) into a range of characters. If you are using 0 then you are inserting and expanding the string, if you use a value greater than 0 then you will replace some characters

``````DECLARE @s varchar(100) = '1111122222'
SELECT STUFF(@S,6,0,'.')
``````

11111.22222

As you can see, when you use 0, the dot gets inserted and everything else gets shifted to the right from position 6

``````DECLARE @s varchar(100) = '1111122222'
SELECT STUFF(@S,6,0,'.....')
``````

11111…..22222

One or more characters doesn’t make a difference, the rest still gets shifted to the right

``````DECLARE @s varchar(100) = '1111122222'
SELECT STUFF(@S,6,5,'.....')
``````

11111…..

Since we used 5, the 5 left most characters after position 6 get replaced by our dots

``````DECLARE @s varchar(100) = '1111122222'
SELECT STUFF(@S,6,2,'.....')
``````

11111…..222

Now we used 2 instead of 5, as you can see 3 (5-2) are still there from the original value

Here is then how you would use the STUFF function to inject a dot into the value

``````DECLARE @pos int = 115949833

SELECT CONVERT(decimal(10,6),(STUFF(@pos,4,0,'.'))) --115.949833
``````

LEFT + RIGHT

This is pretty easy, you grab the first 3 characters with left, add a dot and grab the last 6 characters with right

``````DECLARE @pos int = 115949833

SELECT LEFT(@pos,3) +'.' + RIGHT(@pos,6)--115.949833
``````

SUBSTRING

Substring is similar to using left and right but you need to give it a start position and end position. You need first to convert to char or vachar because substring can’t be used on an integer data type directly

``````DECLARE @pos int = 115949833

SELECT SUBSTRING(CONVERT(CHAR(9),@pos),1,3) +'.' + SUBSTRING(CONVERT(CHAR(9),@pos),4,6) --115.949833
``````

That is all for this post, any other way you would have used?